SQL: Postgres Dialect
Get database size:
SELECT pg_database_size('FOO');
SELECT pg_size_pretty(pg_database_size('FOO'));
Get table sizes in current database and public schema:
SELECT table_name AS table, pg_relation_size(quote_ident(table_name)) AS size FROM information_schema.tables WHERE table_schema = 'public' ORDER BY size DESC;
Delete duplicates without unique column:
DELETE FROM table a WHERE a.ctid <> (SELECT min(b.ctid) FROM table b WHERE a.key = b.key);
List column indices (? == table name):
SELECT index_class.relname AS name, attribute.attname AS column
FROM pg_class table_class
INNER JOIN pg_index index ON table_class.oid=index.indrelid
INNER JOIN pg_class index_class ON index.indexrelid=index_class.oid
INNER JOIN pg_attribute attribute ON attribute.attrelid=table_class.oid AND attribute.attnum=ANY(index.indkey)
WHERE table_class.relname=? AND table_class.relkind='r'
Update primary key with foreign references:
BEGIN;
SET session_replication_role='replica';
UPDATE primary SET id=1 WHERE id=99;
UPDATE secondary SET primary_id=1 WHERE primary_id=99;
SET session_replication_role='origin';
COMMIT;