PostgreSQL: dump and restore one table

1	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

1	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

1 2 3 4 5 6 7 8

-- 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

1 2 3 4

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$'