CODEWITHSUNDEEP
postgresqlviewpgadmindescribe
user3112568
user3112568

Reputation: 291

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.

Upvotes: 28

Views: 77797

Answers (8)

chris costa
chris costa

Reputation: 71

Or you can create a macro and bind it to something like 'CTRL F1' using the following line in the SQL part:

SELECT * FROM public.f_get_table_column_info('$SELECTION$');

The function can reside on the public schema as to not interfere with any other schemas you may have:

CREATE OR REPLACE FUNCTION public.f_get_table_column_info
(
    in_full_table_name TEXT
)
RETURNS TABLE 
(
    c_name text
    ,data_type text
    ,column_default text
    ,numeric_precision INTEGER
    ,numeric_scale INTEGER
) LANGUAGE plpgsql AS $$
DECLARE
    in_table_schema text;
    in_table_name text;
BEGIN
    -- Split the input into schema and table name
    SELECT split_part(in_full_table_name, '.', 1) INTO in_table_schema;
    SELECT split_part(in_full_table_name, '.', 2) INTO in_table_name;

    if in_table_name = ''
    then
        in_table_name := in_table_schema;
        in_table_schema := null;
    end if;

    -- Return the column information
    RETURN QUERY
    SELECT 
        sc.column_name::text
        ,sc.data_type::text
        ,sc.column_default::text
        ,sc.numeric_precision::integer
        ,sc.numeric_scale::integer
    FROM information_schema.columns AS sc
    WHERE sc.table_schema = coalesce(in_table_schema, sc.table_schema)
    AND sc.table_name = in_table_name;
END;
$$;

now you can just select any 'schema.table' or even 'table' and use your Macro to see the structure.

Be aware though that if you use the 'table' format only and the table exists in multiple schemas, you will return information for those also. You can either modify the function to also return the schema, or for such tables, always provide the schema.

Upvotes: 0

Blocked
Blocked

Reputation: 351

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;

Upvotes: 0

Thirdman
Thirdman

Reputation: 709

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

Upvotes: 1

user1322772
user1322772

Reputation:

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.

Upvotes: 52

samir
samir

Reputation: 1

you can use the following command: \d Table_Name

Upvotes: -3

Nate
Nate

Reputation: 65

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.

Upvotes: 2

Yordan Georgiev
Yordan Georgiev

Reputation: 5430

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

Upvotes: 2

harmic
harmic

Reputation: 30587

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.

Upvotes: 2

Related Questions