How to get a list column names and datatypes of a table in PostgreSQL?

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

with the above query you can retrieve columns and its datatype.

Open psql command line and type :

\d+ table_name 

Don't forget 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 

A version that supports finding the column names and types of a table in a specific schema, and uses JOINs without any subqueries

SELECT pg_attribute.attname AS column_name, pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type FROM pg_catalog.pg_attribute INNER JOIN pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid INNER JOIN pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE pg_attribute.attnum > 0 AND NOT pg_attribute.attisdropped AND pg_namespace.nspname = 'my_schema' AND pg_class.relname = 'my_table' ORDER BY attnum ASC; 

Updated Pratik answer to support more schemas and nullables:

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' ); 

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'; 

 --how to get a list column names and datatypes of a table in PostgreSQL? 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 ; 

To make this topic 'more complete'.

I required the column names and data types on a SELECT statement (not a table).

If you want to do this on a SELECT statement instead of an actual existing table, you can do the following:

DROP TABLE IF EXISTS abc; CREATE TEMPORARY TABLE abc AS -- your select statement here! SELECT * FROM foo -- end your select statement ; select column_name, data_type from information_schema.columns where table_name = 'abc'; DROP IF EXISTS abc; 

Short explanation, it makes a (temp) table of your select statement, which you can 'call' upon via the query provided by (among others) @a_horse_with_no_name and @selva.

Hope this helps.

I was looking for a way to get column names with data types without PSQL cl, directly from pgAdmin 4, and found a workaround. Adding one more option:

right-click desired database > generate ERD(Beta) > Generate SQL(or Alt+Ctrl+S) and pgAdmin 4 will open Query Editor where you can find all tables with column names and data types:

without mentioning schema also you can get the required details Try this query->

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

query = "SELECT column_name FROM information_schema.columns WHERE table_schema = 'schema_name' AND table_name = 'table_name'" qry = execute(query) print(qry) 

Below will list all the distinct data types of all the table in the provided schema name.

\copy (select distinct data_type, column_name from information_schema.columns where table_name in (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' and schemaname = '<Your schema name>')) to 'datatypes.csv' delimiter as ',' CSV header 

create a new function to get the table info

CREATE FUNCTION xdesc(in t varchar) RETURNS table(column_name varchar, data_type varchar) AS $$ SELECT column_name, data_type FROM information_schema.columns WHERE table_name = $1 $$ LANGUAGE SQL; select xdesc('rs_rail_job_index') 

I found @Pratik's answer to be the thing, but it didn't work for me for some reason. I've rewritten it in a more consise, if I may, manner. It's easier to understand, it supports schemas and actually works for me (postgres 15). No joins.

SELECT attname AS colname, pg_catalog.format_type(atttypid, atttypmod) AS coltype FROM pg_catalog.pg_attribute WHERE attnum > 0 AND NOT attisdropped AND attrelid = 'your table name'::regclass ORDER BY attnum ASC ; 

P.S. It also supports composite types (which I actually needed very much). It would return a string like this: schemaname.tablename.

Here's a version that just queries one table by leveraging the slightly magical ::regclass cast (which I have found is often more performant that joining on pg_class and pg_namespace), has no subqueries, and avoids information_schema (which I have found is often slower, so just as rule, I would avoid if you can)

SELECT pg_attribute.attname AS column_name, pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type FROM pg_catalog.pg_attribute WHERE pg_attribute.attrelid = 'my_schema.my_table'::regclass AND attnum > 0 AND NOT pg_attribute.attisdropped ORDER BY attnum ASC;