Postgresql – Convert query for count and updated date

aggregatepostgresqlquerysnowflake

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.

Best Answer

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

Related Solutions

PostgreSQL – JSONB SELECT Against Multiple Values

From docs (http://www.postgresql.org/docs/9.4/static/datatype-json.html) try to use expression index:

CREATE INDEX idx_jsonthings_names ON jsonthings USING gin ((d -> 'name'));
SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';

PostgreSQL – Double Count Query

This is a simple group by query.

select status, 
       rank,
       count(*) as cnt
from the_table
group by status, rank
order by status, rank;

Best Answer

Related Solutions

PostgreSQL – JSONB SELECT Against Multiple Values

PostgreSQL – Double Count Query

Related Question