PostgreSQL Queries

gistfile1.sql

-- list all tables
SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname <> 'pg_catalog'AND n.nspname <> 'information_schema'AND n.nspname !~ '^pg_toast'AND pg_catalog.pg_get_userbyid(c.relowner) != 'postgres' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1;

-- list all columns for a table
SELECT attname FROM pg_attribute WHERE attrelid = 'public.sensor_tags'::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum;

-- get all column types for a table
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '___TABLE_NAME___';