PostgreSQL “DESCRIBE TABLE”

I'm new to psql, but just discovered you may do a \d *search pattern * with asterisks to find tables that match the search pattern you're interested in.

If you want to obtain it from query instead of psql, you can query the catalog schema. Here's a complex query that does that:

SELECT  
    f.attnum AS number,  
    f.attname AS name,  
    f.attnum,  
    f.attnotnull AS notnull,  
    pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,  
    CASE  
        WHEN p.contype = 'p' THEN 't'  
        ELSE 'f'  
    END AS primarykey,  
    CASE  
        WHEN p.contype = 'u' THEN 't'  
        ELSE 'f'
    END AS uniquekey,
    CASE
        WHEN p.contype = 'f' THEN g.relname
    END AS foreignkey,
    CASE
        WHEN p.contype = 'f' THEN p.confkey
    END AS foreignkey_fieldnum,
    CASE
        WHEN p.contype = 'f' THEN g.relname
    END AS foreignkey,
    CASE
        WHEN p.contype = 'f' THEN p.conkey
    END AS foreignkey_connnum,
    CASE
        WHEN f.atthasdef = 't' THEN d.adsrc
    END AS default
FROM pg_attribute f  
    JOIN pg_class c ON c.oid = f.attrelid  
    JOIN pg_type t ON t.oid = f.atttypid  
    LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum  
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
    LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)  
    LEFT JOIN pg_class AS g ON p.confrelid = g.oid  
WHERE c.relkind = 'r'::char  
    AND n.nspname = '%s'  -- Replace with Schema name  
    AND c.relname = '%s'  -- Replace with table name  
    AND f.attnum > 0 ORDER BY number
;

It's pretty complex but it does show you the power and flexibility of the PostgreSQL system catalog and should get you on your way to pg_catalog mastery ;-). Be sure to change out the %s's in the query. The first is Schema and the second is the table name.

Is it possible to do: \dt LIKE '%some_table_name%'. I know all these combination will work if you know the full string or want to list all tables, sequences or indexes. - \dt - List all tables - \d - List tables, sequences, indexes, views - \d+ - same as above - \ds - List all sequences - \di - List all indexes

In addition to the PostgreSQL way (\d 'something' or \dt 'table' or \ds 'sequence' and so on)

The SQL standard way, as shown here:

select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = 'table';

It's supported by many db engines.

It looks like you can do that with a psql slash command:

 \d table     describe table (or view, index, sequence)

From http://www.faqs.org/docs/ppbook/c4890.htm

The psql equivalent of "DESCRIBE TABLE" is "\d table".

See the psql portion of the PostgreSQL manual for more details.