WikiGalaxy

Personalize

PostgreSQL Describe Table

Using psql Command:

To describe a table in PostgreSQL, the psql command-line utility can be used. The command \d table_name provides detailed information about the table structure.


\d employees

Alternative psql Command:

Another way to describe a table is using the \dt+ table_name command, which provides additional details such as size and description.


\dt+ employees

PostgreSQL Describe Table Using SQL

Using Information Schema:

The information_schema provides a standardized way to access metadata information about database objects. Use the following SQL query to describe a table:


SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'employees';

Using pg_catalog Schema:

The pg_catalog schema contains PostgreSQL-specific system catalogs. Here is a query to get table details:


SELECT a.attname AS "Column",
  pg_catalog.format_type(a.atttypid, a.atttypmod) AS "Datatype"
FROM pg_catalog.pg_attribute a
WHERE a.attnum > 0 AND NOT a.attisdropped
AND a.attrelid = 'employees'::regclass;

Understanding Table Details

Viewing Indexes:

Indexes are crucial for performance. To view indexes on a table, use the \di table_name command in psql.


\di employees

Checking Constraints:

Constraints ensure data integrity. Use the following query to list constraints on a table:


SELECT conname AS constraint_name, contype AS constraint_type
FROM pg_constraint
WHERE conrelid = 'employees'::regclass;

Advanced Table Description Techniques

Using pgAdmin:

pgAdmin is a popular GUI tool for PostgreSQL. To describe a table, navigate to the table in the browser pane and view its properties.

Using DBeaver:

DBeaver is another GUI tool supporting PostgreSQL. Right-click the table and select "View Diagram" to see its structure.

Exploring Table Relationships

Foreign Keys:

Foreign keys define relationships between tables. Use this query to find foreign keys related to a table:


SELECT
  tc.constraint_name, kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name
FROM
  information_schema.table_constraints AS tc
  JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='employees';

Analyzing Table Performance

Using EXPLAIN:

The EXPLAIN statement helps analyze how queries interact with a table. It provides insights into query execution plans.


EXPLAIN SELECT * FROM employees WHERE department_id = 10;

Describing Table Security

Viewing Table Permissions:

Permissions control access to tables. Use the following query to check permissions on a table:


SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='employees';

Describing Table with Constraints and Defaults

Listing Default Values:

Default values are pre-set values for columns. Use this query to list default values of a table:


SELECT column_name, column_default
FROM information_schema.columns
WHERE table_name = 'employees';

Describing Table with Views

Using Views for Description:

Views can be used to simplify complex queries. To describe a view, use the same commands as for tables.


\d+ employee_view