List all tables in postgresql information_schema
You should be able to just run select * from information_schema.tables
to get a listing of every table being managed by Postgres for a particular database.
You can also add a where table_schema = 'information_schema'
to see just the tables in the information schema.
How to list all tables in postgres without partitions
You won't find that information in the information_schema
; you will have to query the catalogs directly:
SELECT c.relname
FROM pg_class AS c
WHERE NOT EXISTS (SELECT 1 FROM pg_inherits AS i
WHERE i.inhrelid = c.oid)
AND c.relkind IN ('r', 'p');
PostgreSQL - query all tables' all table columns
You can do this in a single query by using array_agg()
and a join on the information_schema.tables
and information_schema.columns
tables.
This would return something similar to your expected output:
select
t.table_name,
array_agg(c.column_name::text) as columns
from
information_schema.tables t
inner join information_schema.columns c on
t.table_name = c.table_name
where
t.table_schema = 'public'
and t.table_type= 'BASE TABLE'
and c.table_schema = 'public'
group by t.table_name;
Here I'm taking all the tables first, then I join it with the columns tables, and finally use array_agg()
to aggregate them all to an array, grouped by the table name.
Hope it helps :) Feel free to ask if you have any doubts.
List tables in a PostgreSQL schema
In all schemas:
=> \dt *.*
In a particular schema:
=> \dt public.*
It is possible to use regular expressions with some restrictions
\dt (public|s).(s|t)
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | s | table | cpn
public | t | table | cpn
s | t | table | cpn
Advanced users can use regular-expression notations such as character classes, for example [0-9] to match any digit. All regular expression special characters work as specified in Section 9.7.3, except for
.
which is taken as a separator as mentioned above,*
which is translated to the regular-expression notation.*
,?
which is translated to.
, and$
which is matched literally. You can emulate these pattern characters at need by writing?
for.
,(R+|)
forR*
, or(R|)
forR?
.$
is not needed as a regular-expression character since the pattern must match the whole name, unlike the usual interpretation of regular expressions (in other words,$
is automatically appended to your pattern). Write*
at the beginning and/or end if you don't wish the pattern to be anchored. Note that within double quotes, all regular expression special characters lose their special meanings and are matched literally. Also, the regular expression special characters are matched literally in operator name patterns (i.e., the argument of\do
).
postgresql list and order tables by size
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2
This shows you the size of all tables in the schema public
if you have multiple schemas, you might want to use:
select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3
SQLFiddle example: http://sqlfiddle.com/#!15/13157/3
List of all object size functions in the manual.
Psql list all tables
If you wish to list all tables, you must use:
\dt *.*
to indicate that you want all tables in all schemas. This will include tables in pg_catalog
, the system tables, and those in information_schema
. There's no built-in way to say "all tables in all user-defined schemas"; you can, however, set your search_path
to a list of all schemas of interest before running \dt
.
You may want to do this programmatically, in which case psql
backslash-commands won't do the job. This is where the INFORMATION_SCHEMA
comes to the rescue. To list tables:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
BTW, if you ever want to see what psql
is doing in response to a backslash command, run psql
with the -E
flag. eg:
$ psql -E regress
regress=# \list
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
so you can see that psql
is searching pg_catalog.pg_database
when it gets a list of databases. Similarly, for tables within a given database:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
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_table_is_visible(c.oid)
ORDER BY 1,2;
It's preferable to use the SQL-standard, portable INFORMATION_SCHEMA
instead of the Pg system catalogs where possible, but sometimes you need Pg-specific information. In those cases it's fine to query the system catalogs directly, and psql -E
can be a helpful guide for how to do so.
PostgreSQL: Show tables in PostgreSQL
From the psql
command line interface,
First, choose your database
\c database_name
Then, this shows all tables in the current schema:
\dt
Programmatically (or from the psql
interface too, of course):
SELECT * FROM pg_catalog.pg_tables;
The system tables live in the pg_catalog
database.
list table sizes for all tables in a postgresql database
Combine your query using a CTE
and join it with the one posted on this answer
, as @a_horse_with_no_name suggested, e.g.
WITH j AS (
SELECT table_schema, table_name, count(*) AS count_columns
FROM information_schema.columns
GROUP BY table_schema, table_name
)
SELECT
nspname AS schemaname,relname,reltuples,count_columns
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
JOIN j ON j.table_name = relname AND j.table_schema = nspname
WHERE
nspname NOT IN ('pg_catalog', 'information_schema') AND
relkind='r'
ORDER BY reltuples DESC;
Related Topics
Changing SQL Server Database Sorting
How to Get Max(Date) from Given Set of Data Grouped by Some Fields Using Pyspark
SQL - Remove the Duplicate Results
How to Alter a Table for Identity Specification Is Identity SQL Server
SQL Use Case Statement in Where in Clause
Find All Rows with Null Value(S) in Any Column
Why SQL Server Go Slow When Using Variables
Multiple Tables Need One to Many Relationship
Create Computed Column Using Data from Another Table
Intersection of Multiple Arrays in Postgresql
Split Date Range into One Row Per Month in SQL Server
Convert Number to Words - First, Second, Third and So On
Using Group by and Having Clause
Workaround for Ora-00997: Illegal Use of Long Datatype
Insert Xml File in SQL via Ssis
How to Avoid Nulls in My Database, While Also Representing Missing Data