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.

In addition to the command line \d+ <table_name> you already found, you could also use the Information Schema to look up the column data, using information_schema.columns:

SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'your_schema'
   AND table_name   = 'your_table'
     ;

Note: As per the example above, make sure the values are enclosed within quotes.

I don't know why you would ever need to do this, but it is possible using some of the JSON functions introduced in 9.3.

SELECT json_object_keys(row_to_json(t)) FROM
 (SELECT * FROM table1
  LEFT JOIN table2 ON table1.id = table2.tbl1_id LIMIT 1) t;

This will give you the name of every column returned for a single row. Without the LIMIT you would get the columns repeated for every row returned. If you wanted to see the values returned as well you can get more complex:

WITH t as
  (SELECT * FROM table1
   LEFT JOIN table2 ON table1.id = table2.tbl1_id LIMIT 1)
SELECT json_data.key, json_data.value
FROM t, json_each_text(row_to_json(t)) AS json_data;

Both these queries will return all the columns even if they are named the same. If all you want is a list of unique column names, you can utilize hstore:

CREATE EXTENSION hstore; --Create the extension if you need it.

SELECT akeys(hstore(t)) as array_of_columns
FROM
(SELECT * FROM table1
 LEFT JOIN table2 ON table1.id = table2.tbl1id LIMIT 1) t;

Whenever you need to convert user input to identifiers or code in an SQL statement, you need dynamic SQL. Either concatenate the statement in your client an send it to the DB engine or (more efficiently) do it in PL/pgSQL (or some procedural server-side language) dynamically with EXECUTE. More details:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

Solution

CREATE OR REPLACE FUNCTION f_get_columns(_cols text[])
  RETURNS TABLE (col text, val text) AS
$func$
DECLARE
   _col text;
BEGIN
   FOREACH _col IN ARRAY _cols LOOP
      RETURN QUERY EXECUTE
      format('SELECT %1$L::text, %1$I::text FROM tbl t', _col);
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_array_of_columns('{column1,column2}');

Returns (random values):

col     | val
--------+-----
column1 | 001
column1 | 002
column1 | 003
column2 | {foo,boo,foo}
column2 | {"",for,test}
column2 | {test,foo,boo}

Key elements:

• A matching RETURNS declaration.
• A way to ruturn your results, RETURN QUERY EXECUTE here.
• A FOREACH loop in this particular case.
• format() to simplify string concatenation and escape names and values properly.
  Note the specifiers:
  %1$L ... first parameter as quoted literal.
  %1$I ... first parameter as properly escaped Identifier.

Note how I cast both columns to text (::text) to match the return type and make it work for any data type. Depending on exact requirements, this could be more specific.

With Postgres (and its JDBC driver) you can do the following:

PreparedStatement pstmt = con.prepareStatement("select ... ");
ResultSetMetaData meta = pstmt.getMetaData();
for (int i=1; i <= meta.getColumnCount(); i++)
{
  System.out.println("Column name: " + meta.getColumnName(i) + ", data type: " + meta.getColumnTypeName(i));
}

Note that you do not need to add a where false or limit 0 to the statement. The call to prepareStatement() does not actually execute the query.

Information schema vs. system catalogs

The information schema serves certain purposes. System catalogs are the actual source of truth in Postgres.

The information schema provides standardized views which help with portability - mostly across major Postgres versions as portability across different RDBMS platforms typically is an illusion once your queries are sophisticated enough to look up system catalogs. Notably, major RDBMS like Oracle, SQLite, IBM Db2, SAP ASE, or IBM Informix still don't support the information schema (as of June 2026).

Views in the information schema must jump through many hoops to achieve a format complying to the standard. This makes them slow, sometimes very slow. Compare plans and performance for these basic objects:

EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;

The difference is remarkable.

Your example

For your example SELECT * from tbl compare the two queries below for this simple table:

CREATE TEMP TABLE foo(
   a numeric(12,3)
 , b timestamp(0)
);

Using pg_attribute:

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'foo'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

format_type() returns the complete type with all modifiers:

attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone

Also note that the cast to regclass resolves the table name according to the current search_path. It raises an exception if the name is not valid. See:

• ERROR: could not find array type for datatype information_schema.sql_identifier

Using information_schema.columns:

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'foo'
ORDER  BY ordinal_position;

The information is standardized, but incomplete:

column_name | data_type
------------+----------------------------
a           | numeric
b           | timestamp without time zone

To get full information for the data type you need to consider all of these columns additionally:

character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision

Related answers:

• How to check if a table exists in a given schema
• List all columns for a specified table

List of pros & cons

The biggest pros (IMO) in bold.

Information schema views

• often simpler (depends)
• slow
• preprocessed, which may or may not suit your needs
• selective (users only see objects they have privileges for)
• conforming to an SQL standard (that's implemented by some of the major RDBMS)
• mostly portable across major Postgres versions
• do not require much specific knowledge about Postgres
• identifiers are descriptive, long and sometimes awkward

System catalogs

• often more complex (depends), closer to the source
• fast
• complete (system columns like oid included)
• not complying to an SQL standard
• less portable across major Postgres versions (but basics aren't going to change)
• require more specific knowledge about Postgres
• identifiers are terse, less descriptive but conveniently short

Arbitrary query

To get the same list of column names and types from a query, you could use a simple trick: CREATE a temporary table from the query output, then use the same techniques as above.

You can append LIMIT 0, since you do not need actual data:

CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT  0;

To get the data type of individual columns, you can also use the function pg_typeof():

SELECT pg_typeof(1);