Equivalent of "describe table" in PgAdmin3

Question

Question asked and answered:

As many of us know, PostgreSQL does not support describe table or describe view. As one might find from google, PostgreSQL uses \d+ instead.

However, if one accesses PostgreSQL using PgAdmin (I am actually using PgAdmin3) then \d+ does not work. What does one do instead?

I thought about this question when playing with the query tool in PgAdmin3. I had a "well, duh!" moment when I thought to look at the home window of PgAdmin3, and at the tree on the left side of that window. Under

<servername>
-> <databasename>
-> Schemas
-> <schemaname>
-> Tables

was a list of my tables, and clicking on the table name showed me text very much like what \d+ would have showed me.

So for the benefit of anyone else who did not discover this right away, here is an answer.

Strictly, `psql` uses `\dt`; PostgreSQL the server backend doesn't, though it does offer the `information_schema` views. — Craig Ringer, Jan 29 '14 at 23:27

score 50 · Answer 1 · edited Jan 21 '21 at 21:31

50

PostgreSQL also supports the standard SQL information schema to retrieve details of objects in the database.

i.e. to get column information you can query the information_schema.columns view:

SELECT *
FROM information_schema.columns
WHERE table_name = '<YourTableName>';

Be sure to use single quotations, double quotes won't work

Check here for PostgreSQL specific details on the information schema.

edited Jan 21 '21 at 21:31

answered Jan 29 '14 at 21:38

Gareth Flowers

1,513
12
23

information_schemas was all I was looking for. Needed an equivalent of sys(in MS SQL Server) in pgadmin 3. Thanks. – Praveen Jul 29 '19 at 07:28

score 2 · Answer 2 · answered Jan 29 '14 at 23:41

psql's \d command sends a set of queries to the database to interrogate the schema, then prints the result.

You can use the '-E' psql option to get it to display these queries, if you want to be able to extract similar information directly via SQL.

Having said that, psql uses the internal Postgresql catalog tables, instead of the standardized 'information_schema' schema (see answer from garethflowers). So if you care about portability, or even guaranteeing that it will continue to work from one release to the next, you probably should use information_schema.

Yordan Georgiev · Answer 3 · 2017-07-28T15:38:03.087

2

and the straight from the bash shell:

    psql -d "$db_name" -c '
    SELECT 
    ordinal_position , table_name , column_name , data_type , is_nullable
    FROM information_schema.columns
    WHERE 1=1
    AND table_name = '\''my_table'\''
    ;'

    # or just the col names
    psql -d "$my_db" -t -c \
    "SELECT column_name FROM information_schema.columns 
    WHERE 1=1 AND table_name='my_table'"

edited Jul 28 '17 at 15:38

answered May 30 '17 at 12:33

Yordan Georgiev

5,114
1
56
53

score 2 · Answer 4 · answered Dec 11 '18 at 22:42

To get the full view that the describe query would return right click on the relation/table of interest and select Properties... then use the Columns tab in the window provided.

The only difference is that the window does not give information about foreign key relation.

score -3 · Answer 5 · answered Jun 02 '20 at 03:35

-3

you can use the following command: \d Table_Name

answered Jun 02 '20 at 03:35

samir

1

1

Try to add some explanation to your answer, it will make it more clear and understandable. Please read stackoverflow.com/help/how-to-answer – 32cupo Jun 02 '20 at 05:41
I think a better explanation is necessary because for many versions this command doesn't work. – IFTEKHAR I ASIF Apr 12 '21 at 07:52

Equivalent of "describe table" in PgAdmin3

5 Answers5

Linked

Related