Convert query for count and updated date

Question

I currently run the below query in Snowflake for some of my reconciliations and have never worked in PostgreSQL, which we just adopted.

Select count(1), min (LAST_UPDATED_DATE), max(LAST_UPDATED_DATE)
from "SOURCE"."SCHEMA"."TABLE"

I'm looking to do the same thing in PostgreSQL. It's one that we can run against a table that will give us the last updated date and a count when checking that movements completed.

I know Postgres can be time heavy on counts and some of these tables are massive. That aside I'm not sure how to rewrite this to begin with.

Answer 1

Starting point, mostly unchanged:

SELECT count(*), min(last_updated_date), max(last_updated_date)
FROM   schema.table;

• Be aware of (not) case sensitive behavior of identifiers in Postgres. If in doubt use legal, unquoted, lower-case names exclusively. See:

  • Are PostgreSQL column names case-sensitive?

• A table can optionally be schema-qualified (schema.table), but not database-qualified. A name like from "SOURCE"."SCHEMA"."TABLE" would raise:

ERROR:  cross-database references are not implemented: "SOURCE.SCHEMA.TABLE"

• Table names without schema-qualification are resolved according to the current search_path. See:

  • How does the search_path influence identifier resolution and the “current schema”

• count(*) is a bit faster than count(1).

• Min and max can be read from a matching index in next-to-no time. Only the count can be slow. If your count does not have to be exact, consider:

  • Fast way to discover the row count of a table in PostgreSQL