\d [ PATTERN ]
The \d
meta-command lists all tables in the database and returns their schema, table name, kind (e.g., table), and owner.
If you use \d [ PATTERN ]
and provide the schema name or table name (or wildcard or ? characters) as the pattern argument, the result shows more detailed information about the tables:
- Schema name
- Table name
- Column name
- Column data type
- Data type size
- Default column value
- Whether the column accepts null values or has a
NOT NULL
constraint - Whether there is a primary key or foreign key constraint
To view information about system tables, you must include the V_MONITOR
or V_CATALOG
as the pattern argument; for example:
\d v_catalog.types -- information on the types table in v_catalog schema \d v_catalog.* -- information on all table columns in v_catalog schema
The following output is the result of all tables in the vmart schema, which is in the PUBLIC schema.
VMart=> \d
List of tables
Schema | Name | Kind | Owner
--------------+-----------------------+-------+---------
online_sales | call_center_dimension | table | dbadmin
online_sales | online_page_dimension | table | dbadmin
online_sales | online_sales_fact | table | dbadmin
public | customer_dimension | table | dbadmin
public | date_dimension | table | dbadmin
public | employee_dimension | table | dbadmin
public | inventory_fact | table | dbadmin
public | product_dimension | table | dbadmin
public | promotion_dimension | table | dbadmin
public | shipping_dimension | table | dbadmin
public | vendor_dimension | table | dbadmin
public | warehouse_dimension | table | dbadmin
store | store_dimension | table | dbadmin
store | store_orders_fact | table | dbadmin
store | store_sales_fact | table | dbadmin
(15 rows)
This example returns information on the inventory_fact
table in the VMart database:
VMart=> \x
Expanded display is on.
VMart=> \d inventory_fact
List of Fields by Tables
-[ RECORD 1 ]------------------------------------------
Schema | public
Table | inventory_fact
Column | date_key
Type | int
Size | 8
Default |
Not Null | t
Primary Key | f
Foreign Key | public.date_dimension(date_key)
-[ RECORD 2 ]------------------------------------------
Schema | public
Table | inventory_fact
Column | product_key
Type | int
Size | 8
Default |
Not Null | t
Primary Key | f
Foreign Key | public.product_dimension(product_key)
-[ RECORD 3 ]------------------------------------------
Schema | public
Table | inventory_fact
Column | product_version
Type | int
Size | 8
Default |
Not Null | t
Primary Key | f
Foreign Key | public.product_dimension(product_version)
-[ RECORD 4 ]------------------------------------------
Schema | public
Table | inventory_fact
Column | warehouse_key
Type | int
Size | 8
Default |
Not Null | t
Primary Key | f
Foreign Key | public.warehouse_dimension(warehouse_key)
-[ RECORD 5 ]------------------------------------------
Schema | public
Table | inventory_fact
Column | qty_in_stock
Type | int
Size | 8
Default |
Not Null | f
Primary Key | f
Foreign Key |
Use the question mark [ ?
] argument to replace a single character. For example, the ?
argument replaces the last character in the user-created SubQ1
and SubQ2
tables, so the command returns information about both:
=> \d SubQ?
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
public | SubQ1 | a | int | 8 | | f | f |
public | SubQ1 | b | int | 8 | | f | f |
public | SubQ1 | c | int | 8 | | f | f |
public | SubQ2 | x | int | 8 | | f | f |
public | SubQ2 | y | int | 8 | | f | f |
public | SubQ2 | z | int | 8 | | f | f |
(6 rows)
If you run the \d
command and provide both the schema and table name, output includes columns for tables that match the pattern
VMart=> \x Expanded display is on. VMart=> \d v_catalog.types List of Fields by Tables -[ RECORD 1 ]-------------------- Schema | v_catalog Table | types Column | column_size Type | int Size | 8 Default | Not Null | f Primary Key | f Foreign Key | -[ RECORD 2 ]-------------------- Schema | v_catalog Table | types Column | creation_parameters Type | varchar(128) Size | 128 Default | Not Null | f Primary Key | f Foreign Key | -[ RECORD 3 ]-------------------- Schema | v_catalog Table | types Column | epoch Type | int Size | 8 Default | Not Null | f Primary Key | f Foreign Key | -[ RECORD 4 ]-------------------- Schema | v_catalog Table | types Column | interval_mask Type | int Size | 8 Default | Not Null | f Primary Key | f Foreign Key | -[ RECORD 5 ]-------------------- Schema | v_catalog Table | types Column | max_scale Type | int Size | 8 Default | Not Null | f Primary Key | f Foreign Key | -[ RECORD 6 ]-------------------- Schema | v_catalog Table | types Column | min_scale Type | int Size | 8 Default | Not Null | f Primary Key | f Foreign Key | -[ RECORD 7 ]-------------------- Schema | v_catalog Table | types Column | odbc_subtype Type | int Size | 8 Default | Not Null | f Primary Key | f Foreign Key | -[ RECORD 8 ]-------------------- Schema | v_catalog Table | types Column | odbc_type Type | int Size | 8 Default | Not Null | f Primary Key | f Foreign Key | -[ RECORD 9 ]-------------------- Schema | v_catalog Table | types Column | type_id Type | int Size | 8 Default | Not Null | f Primary Key | f Foreign Key | -[ RECORD 10 ]------------------- Schema | v_catalog Table | types Column | type_name Type | varchar(128) Size | 128 Default | Not Null | f Primary Key | f Foreign Key |
To view all tables in a schema, use the wildcard character. The following command would return all system tables in the V_CATALOG
schema:
=> \d v_catalog.*