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:
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:
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:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4