PostgreSQL Tutorial

  • Home
  • Stored Procedures
  • Triggers
  • Views
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
  • Functions
Home / PostgreSQL Administration / PostgreSQL Describe Table

PostgreSQL Describe Table

This tutorial shows you how to use the psql tool and information_schema to describe table in PostgreSQL.

If you are using MySQL, you use the DESCRIBE statement to find the information on the columns of a particular table. PostgreSQL does not provide the DESCRIBE statement. However, you can query the information on columns of a table in a couple of ways.

PostgreSQL DESCRIBE TABLE using psql

First, connect to PostgreSQL server,  the database dvdrental.

Second, issue the command \d table_name or \d+ table_name to find the information on columns of a table. The following example queries information on columns of the city table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Server [localhost]:
Database [postgres]: dvdrental
Port [5432]:
Username [postgres]:
psql (9.4.2)
 
dvdrental=# \d city
                                        Table "public.city"
   Column    |            Type             |                       Modifiers
-------------+-----------------------------+--------------------------------------------------------
city_id     | integer                     | not null default nextval('city_city_id_seq'::regclass)
city        | character varying(50)       | not null
country_id  | smallint                    | not null
last_update | timestamp without time zone | not null default now()
Indexes:
    "city_pkey" PRIMARY KEY, btree (city_id)
    "idx_fk_country_id" btree (country_id)
Foreign-key constraints:
    "fk_city" FOREIGN KEY (country_id) REFERENCES country(country_id)
Referenced by:
    TABLE "address" CONSTRAINT "fk_address_city" FOREIGN KEY (city_id) REFERENCES city(city_id)
Triggers:
    last_updated BEFORE UPDATE ON city FOR EACH ROW EXECUTE PROCEDURE last_updated()
 
 
dvdrental=#

The command issued a lot of information on the columns of the city table. In addition, it also returned indexes, foreign key constraints, and triggers.

If you just want to know a list of columns of a table, you can use the second way.

PostgreSQL DESCRIBE TABLE using information_schema

In this way, you just use the SELECT statement to query the column_names of the columns table in the information_schema database.

For example, following query returns all column names of the city table:

1
2
3
4
5
6
SELECT
COLUMN_NAME
FROM
information_schema.COLUMNS
WHERE
TABLE_NAME = 'city';

postgresql describe table example

In this tutorial, we have shown you how to query information on columns of a particular table using the psql tool and information_schema.

Related Tutorials

  • PostgreSQL Show Tables
  • PostgreSQL Show Databases
  • 17 Practical psql Commands That You Don't Want To Miss
Previous Tutorial: PostgreSQL Show Tables
Next Tutorial: 17 Practical psql Commands That You Don’t Want To Miss

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete
  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • How To Change The Password of a PostgreSQL User
  • PostgreSQL AGE Function
  • PostgreSQL DATE_PART Function
  • PostgreSQL List Users
  • PostgreSQL NOW Function
  • PostgreSQL DATE_TRUNC Function
  • PostgreSQL TO_DATE Function: Convert String to Date
  • A Look at PostgreSQL User-defined Data Types
  • PostgreSQL Copy Database Made Easy
  • How to Get Table, Database, Indexes, Tablespace, and Value Size in PostgreSQL

More Tutorials

  • PostgreSQL Cheat Sheet
  • PostgreSQL Administration
  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2017 by PostgreSQL Tutorial Website. All Rights Reserved.