Is there a psql command to show table definitions?

Question

The sqlite3 CLI has a command .schema that will display the columns of all tables in the database.

The psql CLI for PostgreSQL has a meta-commands \d that shows columns for all "relations" (table, view, index, sequence, or foreign table) and a meta-command \dt that lists the relations that are tables but does not show the columns of those tables.

Is there a way to get psql to show output like sqlite3's .schema - show the output of \d on just relations that are tables? \d * shows columns for all relations, which in my database of 32 tables contains 63 tables and sequences. The pattern (* in this example) seems able to match on relation name but not relation type. Is there a pattern for "match all tables"?

Answer 1

If you want this like output:

sqlite> .schema t24
CREATE TABLE t24 (
    i integer,
    t text
);

You should use pg_dump -s, not psql:

bash>pg_dump -t t24 -s
CREATE TABLE t24 (
    i integer,
    t text
);

It created DDL just like .schema...

Now regarding the queries in comments, if you modify them a little:

t=# \d t24
      Table "public.t24"
 Column |  Type   | Modifiers
--------+---------+-----------
 i      | integer |
 t      | text    |

t=# SELECT a.attname as "Column",pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as "Modifiers"
FROM pg_catalog.pg_attribute a
join pg_catalog.pg_class  c  on a.attrelid = c.oid
WHERE true
  AND relname like 't24'
  AND c.relkind = 'r'::"char"
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY relname, a.attnum;
 Column |  Type   | Modifiers
--------+---------+-----------
 i      | integer |
 t      | text    |
(2 rows)

This query will show only tables in same manner as \d meta-command.

BTW I took the query from this metacommand. If you launch psql -E, and run '\d table_name` you will see that all meta commands are just wraps for select queries...