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

How can I get a list of column names and datatypes of a table in PostgreSQL using a query?

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

with the above query you can columns and its datatype

Open psql command line and type :

\d+ table_name

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

More info on it : http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

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.

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

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:

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