How to get the PostgreSQL table structure | PostgreSQL Tutorials and Solutions

In this article, we are going to see How to get the PostgreSQL table structure. Table structure is nothing but to get the list of columns, datatypes and constrains information.

PostgreSQL table structure using meta command:

\d+ table_name;

Example:

\d+ dept

Output:

                                                   Table "public.dept"
 Column |         Type          | Collation | Nullable |         Default         | Storage  
--------+-----------------------+-----------+----------+-------------------------+----------
 deptno | numeric(2,0)          |           |          | NULL::numeric           | main     
 dname  | character varying(14) |           |          | NULL::character varying | extended 
 loc    | character varying(13) |           |          | NULL::character varying | extended

PostgreSQL table structure using SQL Statement:
1. We can the PostgreSQL table structure by using information_schema. In the below query replace your_schema and your_table with actual table name and schema name.

SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'your_schema'
   AND table_name   = 'your_table';

Example: Run the above query for the table ‘dept’ in the schema ‘public’.

SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name   = 'dept';

2. We can get only PostgreSQL table columns name by using below query.

select * from table_name where false;

Examples:

r2schools=# select * from dept where 1=2;
 deptno | dname | loc
--------+-------+-----
(0 rows)

So in this article, we have covered different methods to get PostgreSQL table structure.

Related Posts: