Equivalent of "describe table" in PgAdmin3
Asked Answered
R

7

27

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.

Rasmussen answered 29/1, 2014 at 20:57 Comment(1)
Strictly, psql uses \dt; PostgreSQL the server backend doesn't, though it does offer the information_schema views.Hypotrachelium
L
51

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.

Longwood answered 29/1, 2014 at 21:38 Comment(1)
information_schemas was all I was looking for. Needed an equivalent of sys(in MS SQL Server) in pgadmin 3. Thanks.Y
P
2

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.

Prophets answered 29/1, 2014 at 23:41 Comment(0)
C
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'"
Croaky answered 30/5, 2017 at 12:33 Comment(0)
T
2

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.

Tempting answered 11/12, 2018 at 22:42 Comment(0)
C
1

See Answer of Gareth Flowers below:

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

and also be sure to use lower case name of your table...

Chromatograph answered 2/10, 2023 at 12:0 Comment(0)
E
0

For PgAdmin 4 version 8.5 we have:

1) Press Alt+Shift+S. A dialog will be opened. With this dialog, you can search for almost any kind of objects in a database, including tables. https://www.pgadmin.org/docs/pgadmin4/development/search_objects.html Search Objects Dialog

2)

select *
from schema.Table_Name
where FALSE;

It will return only the header (columns name and type) but no data rows.

3)

SELECT *
FROM information_schema.columns
WHERE table_schema = 'schema'
AND table_name = 'Table_Name';

It will return all the columns of the specified table in data rows.

You can improve 2) and 3) by adding a Macro with the placeholder '$SELECTION$' or "$SELECTION$" See https://www.pgadmin.org/docs/pgadmin4/8.8/query_tool.html#macros

If you have acces to psql tool(SQL Shell) you can use the following command:

\d Table_Name

or

\d+ Table_Name;
Eduino answered 11/7 at 11:29 Comment(0)
F
-3

you can use the following command: \d Table_Name

Frasco answered 2/6, 2020 at 3:35 Comment(2)
Try to add some explanation to your answer, it will make it more clear and understandable. Please read stackoverflow.com/help/how-to-answerZoellick
I think a better explanation is necessary because for many versions this command doesn't work.Antiperspirant

© 2022 - 2024 — McMap. All rights reserved.