Wednesday, August 30, 2017

PostgreSQL: dump and restore one table

pg_dump --host db2.prod.vty.mbqt --port 5432 --username postgres --no-owner --no-password --format custom --encoding UTF8 --verbose --file /tmp/jerome/jerome_test.custom --table public.jerome_test --data-only dwhmbqt
Then
pg_restore --host db2.prod.vty.mbqt --port 5432 --username postgres --no-owner --no-password --dbname dwhmbqt --data-only --verbose /tmp/jerome/jerome_test.custom

PostgreSQL: tables without PK

Source: http://petereisentraut.blogspot.fr/2009/07/how-to-find-all-tables-without-primary.html
-- Tables of db2 without PK
SELECT table_catalog, table_schema, table_name
    FROM information_schema.tables
    WHERE (table_catalog, table_schema, table_name) NOT IN
          (SELECT table_catalog, table_schema, table_name
               FROM information_schema.table_constraints
               WHERE constraint_type = 'PRIMARY KEY')
      AND table_schema NOT IN ('information_schema', 'pg_catalog') and table_schema ~ '_db$' and table_schema not in ('prov_db', 'queue_db', 'ota_db', 'sla_db');

PostgreSQL: find non-empty tables

Source: https://stackoverflow.com/questions/24734489/show-all-not-empty-tables-in-postgres
SELECT n.nspname, c.relname                                                                                                                                                                           
FROM pg_class c
INNER JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.reltuples != 0 AND c.relkind = 'r' and nspname ~ '_db$'