Describe Table Structure in PostgreSQL
How to Describe Table Structure in PostgreSQL?
In PostgreSQL, there’s no direct equivalent to Oracle’s DESCRIBE TABLE command, which provides detailed information about a table's columns and data types. However, you can achieve similar results using the \d command in psql or by querying the information_schema.columns table. Here’s how to view table structure in PostgreSQL.
1. Using \d Command in psql
The \d command in PostgreSQL’s interactive terminal, psql, displays information about tables, including column names, data types, and other details.
Syntax:
\d table_name
Example Code:
-- Connect to the database in the psql command line
\c database_name
-- Describe the structure of a specific table
\d table_name
Explanation:
- \c database_name: Connect to the target database (replace database_name with the name of your database).
- \d table_name: Displays detailed information about table_name, including column names, data types, and constraints.
2. Using SQL Query on information_schema.columns
Another way to get information about a table’s columns is by querying information_schema.columns, which provides metadata for columns across tables in the database.
Syntax:
SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'table_name';
Example Code:
-- Select details of columns in a specific table
SELECT column_name, -- Column name
data_type, -- Data type of the column
is_nullable, -- Whether the column allows NULLs
column_default -- Default value for the column
FROM information_schema.columns -- From the 'columns' schema
WHERE table_name = 'table_name'; -- Specify the table name here
Explanation:
- column_name: Retrieves each column’s name.
- data_type: Shows the data type of each column.
- is_nullable: Indicates whether the column allows NULL values.
- column_default: Displays the default value, if any, for the column.
- information_schema.columns: Stores metadata for columns in all tables.
- WHERE table_name = 'table_name': Filters results to only show information for the specified table.
3. Using pg_catalog.pg_attribute and pg_catalog.pg_class
You can also query PostgreSQL system catalogs to retrieve similar table information. This method involves using pg_catalog.pg_attribute and pg_catalog.pg_class to get column details.
Syntax:
SELECT a.attname AS column_name, pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull AS is_nullable FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON a.attrelid = c.oid WHERE c.relname = 'table_name' AND a.attnum > 0 AND NOT a.attisdropped;
Example Code:
-- Query columns from system catalogs
SELECT a.attname AS column_name, -- Column name
pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type, -- Data type
a.attnotnull AS is_nullable -- NULLability of column
FROM pg_catalog.pg_attribute a -- 'pg_attribute' catalog for columns
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid -- Join 'pg_class' to get table details
WHERE c.relname = 'table_name' -- Specify the table name
AND a.attnum > 0 -- Filter to real columns only
AND NOT a.attisdropped; -- Exclude dropped columns
Explanation:
- attname: Column name in the table.
- pg_catalog.format_type(a.atttypid, a.atttypmod): Retrieves data type information.
- attnotnull: Indicates if the column is set to NOT NULL.
- pg_catalog.pg_attribute: Stores information about table columns.
- pg_catalog.pg_class: Contains metadata about tables and other objects.
- The WHERE clause filters to get details only for the specified table’s active columns.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/how-to-describe-table-structure-in-postgresql.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics