Skip to content

Instantly share code, notes, and snippets.

@Asjas
Created February 28, 2021 19:09
Show Gist options
  • Save Asjas/423145f8e4d71610ac17a377392e3033 to your computer and use it in GitHub Desktop.
Save Asjas/423145f8e4d71610ac17a377392e3033 to your computer and use it in GitHub Desktop.
PostgreSQL System Table Queries
// This query returns a list of tables, in alphabetical order, with a count of the columns.
SELECT table_name
,COUNT(column_name)
FROM information_schema.columns
WHERE table_schema = 'myschema' -- put your schema here
GROUP BY table_name
ORDER BY table_name;
// This query returns a list of tables, in alphabetical order, with a count of the rows.
SELECT schemaname
,relname
,n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = 'myschema'
ORDER BY relname;
// This query returns a list of tables, in alphabetical order, with their column names, data types and lengths.
SELECT table_schema
,table_name
,column_name
,data_type
FROM information_schema.columns
WHERE table_schema = 'myschema';
// This query returns a list of column names that match the search criteria in the WHERE clause.
SELECT column_name
,table_name
FROM information_schema.columns
WHERE column_name = 'mycolumn' --put your column name here
AND table_schema = 'myschema'; -- put your schema here
// This query returns a list of tables, in alphabetical order, from the schema or database requested.
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'myschema' -- put your schema here
ORDER BY table_name;
// This query returns a list of tables, in alphabetical order, with their last modified and created date.
SELECT schemaname
,COUNT(tablename)
FROM pg_tables
GROUP BY schemaname
ORDER BY schemaname;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment