postgres common queries

pg_common_queries.sh

#list column names of a table
select column_name
from information_schema.columns
where table_name = 'questionnaire'
  and table_schema = 'fi';
  
## list the sizes of all schemas in a database
SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;

here are some run through a remote connection

# ##listthe schemas in the database
dbGetQuery(conn,
           "SELECT schema_name
           FROM information_schema.schemata")
# #
# #
# # # ##list tables in a schema
dbGetQuery(conn,
           "SELECT table_name
           FROM information_schema.tables
           WHERE table_schema='whse_basemapping'")

##list column names in a table
dbGetQuery(conn,
           "SELECT column_name,data_type
           FROM information_schema.columns
           WHERE table_name='gba_railway_structure_lines_sp'")

# list distinct params from a column in a table
dbGetQuery(conn,
           "SELECT DISTINCT modelled_crossing_type_source FROM bcfishpass.modelled_stream_crossings")

# find-tables-with-specific-column-name
dbGetQuery(conn,
           "select t.table_schema,
           t.table_name
           from information_schema.tables t
           inner join information_schema.columns c on c.table_name = t.table_name
           and c.table_schema = t.table_schema
           where c.column_name = 'file_type_description'
           and t.table_schema not in ('information_schema', 'pg_catalog')
           and t.table_type = 'BASE TABLE'
           order by t.table_schema;")