Equivalent of “describe table” in PgAdmin3

问题:

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.

回答1:

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 = '????';

Check here for PostgreSQL specific details on the information schema.

回答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.

回答3:

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'"

回答4:

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.