How to get list column names and table data type in PostgreSQL?

Question

With the following query, we can get a list of column names and a table data type in PostgreSQL.

Answer 1

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 = ( SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(hello world)$' AND pg_catalog.pg_table_is_visible(c.oid) ); 

Additional information about him: http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

Answer 2

Open the psql commande command and type:

 \d+ table_name 

Answer 3

 select column_name,data_type from information_schema.columns where table_name = 'table_name'; 

with the above query you can use columns and its data type

Answer 4

Remember to add the schema name in case you have multiple schemas with the same table names.

 SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'your_table_name' AND table_schema = 'your_schema_name'; 

or using PSQL:

 \d+ your_schema_name.your_table_name 

Answer 5

Pratik's updated answer to support more schemas and null values.

 SELECT "pg_attribute".attname as "Column", pg_catalog.format_type("pg_attribute".atttypid, "pg_attribute".atttypmod) as "Datatype", not("pg_attribute".attnotnull) AS "Nullable" FROM pg_catalog.pg_attribute "pg_attribute" WHERE "pg_attribute".attnum > 0 AND NOT "pg_attribute".attisdropped AND "pg_attribute".attrelid = ( SELECT "pg_class".oid FROM pg_catalog.pg_class "pg_class" LEFT JOIN pg_catalog.pg_namespace "pg_namespace" ON "pg_namespace".oid = "pg_class".relnamespace WHERE "pg_namespace".nspname = 'schema' AND "pg_class".relname = 'table' ); 

Answer 6

To get information about a table column, you can use:

 \dt+ [tablename] 

To get information about the data type in a table, you can use:

 \dT+ [datatype] 

Answer 7

I use the psql to interact with my database, here is a cheat sheet that helped me a lot. Hope this helps someone else too. psql playlist

Answer 8

  SELECT DISTINCT ROW_NUMBER () OVER (ORDER BY pgc.relname , a.attnum) as rowid , pgc.relname as table_name , a.attnum as attr, a.attname as name, format_type(a.atttypid, a.atttypmod) as typ, a.attnotnull as notnull, com.description as comment, coalesce(i.indisprimary,false) as primary_key, def.adsrc as default FROM pg_attribute a JOIN pg_class pgc ON pgc.oid = a.attrelid LEFT JOIN pg_index i ON (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum) LEFT JOIN pg_description com on (pgc.oid = com.objoid AND a.attnum = com.objsubid) LEFT JOIN pg_attrdef def ON (a.attrelid = def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_namespace n ON n.oid = pgc.relnamespace WHERE 1=1 AND pgc.relkind IN ('r','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND a.attnum > 0 AND pgc.oid = a.attrelid AND pg_table_is_visible(pgc.oid) AND NOT a.attisdropped ORDER BY rowid ; 

Answer 9

select column_name, data_type from information_schema.columns, where table_name = 'your_table_name' and table_catalog = 'your_database_name' and table_schema = 'your_schema_name';