Describe a table
Show a table's columns, indexes and foreign keys with the psql meta-command backslash d, and add a plus sign for storage and comments.
You need the columns, keys and indexes of a table, and you would rather not query the system catalogs.
psql -c 'CREATE TABLE users (id serial PRIMARY KEY, email text NOT NULL UNIQUE)'
psql -c 'CREATE TABLE orders (id serial PRIMARY KEY, user_id int REFERENCES users(id), total numeric(10,2) DEFAULT 0)'
psql -c '\d orders'
psql -c '\dt'
psql -c 'DROP TABLE orders, users'CREATE TABLE
CREATE TABLE
Table "public.orders"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('orders_id_seq'::regclass)
user_id | integer | | |
total | numeric(10,2) | | | 0
Indexes:
"orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | orders | table | postgres
public | users | table | postgres
(2 rows)
DROP TABLEThe backslash commands of psql read the catalogs and format the result: the describe command lists the columns with their types, nullability and defaults, then the indexes, and the foreign keys the table has and those that point at it. A second command lists all tables, a plus sign adds storage and descriptions, and sibling commands do the same for indexes and views. They are listed in the psql reference. A name is matched like a pattern, so a star lists several tables at once.
These commands exist only in psql, so another client needs queries against information_schema.columns or pg_catalog. Start psql with -E to see the queries behind each command, which is a way to learn the catalogs. The listing hides tables in schemas that are not on the search_path; give the schema as a prefix.