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 TALE statement to find the information on the columns of a particular table. PostgreSQL does not provide the DESCRIBE TABLE 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'; |
In this tutorial, we have shown you how to query information on columns of a particular table using the psql tool and information_schema.