Postgres queries

pg.md

Redshift specific

Models

Show status of a model

show model <name>;

List all models

show model all;

Importing data

When using a client like DBeaver to import CSV data, Redshift requires you to be the owner of the table

GRANT ALL PRIVILEGES ON TABLE <name> TO <user>;
ALTER TABLE <name> owner to <user>;

Postgres in general

List of recent inserts

drop table curated_data.public.jz_test_20230423;
SELECT sti.schema, sti.table, sq.endtime, sq.querytxt
FROM 
    (SELECT MAX(query) as query, tbl, MAX(i.endtime) as last_insert
    FROM stl_insert i
    GROUP BY tbl
    ORDER BY tbl) inserts
JOIN stl_query sq ON sq.query = inserts.query
JOIN svv_table_info sti ON sti.table_id = inserts.tbl
ORDER BY inserts.last_insert DESC;

STL tables only retain approximately two to five days of log history (at least on Redshift).

Does not capture inserts from ALTER TABLE abc APPEND FROM xyz

Describe a table

SELECT * FROM information_schema.columns
WHERE table_schema = 'public'
   AND table_name   = '<name>';
   AND column_name = '<column>';

Select a specific range of hours from a timestamp

select * from <name> where extract(hour from timestamp_col) in (1, 2) order by timestamp_col limit 100;

Combine a date and hour of day into one timestamp, truncate to the hour

date_trunc('hour', TO_TIMESTAMP(DATE(transaction_date) || ' ' || transaction_time || ':00', 'YYYY-MM-DD HH24:MI:SS')) AS timestamp,