PostgreSQL: How to List Column Names and Types for Table

Often in PostgreSQL you may want to get the column names and datatypes for each column in a particular table.

You can use the following syntax to do so:

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

This particular example will return each column name and datatype for each column in the table named products.

To get the column names and datatypes for a different table, simply change the table name specified in the WHERE clause.

The following example shows how to use this syntax in practice.

Example: How to List Column Names and Types for Table in PostgreSQL

Suppose that we create the following table named products that contains information about products sold on various dates by some company:

-- create table named 'products'
CREATE TABLE products (
  productID INTEGER,
  name TEXT,
  date_sold DATE
);

-- add rows to the table 
INSERT INTO products VALUES (0001, 'A', '2024-09-11');
INSERT INTO products VALUES (0002, 'A', '2024-09-12');
INSERT INTO products VALUES (0003, 'B', '2024-09-10');
INSERT INTO products VALUES (0004, 'B', '2024-09-15');
INSERT INTO products VALUES (0005, 'C', '2024-09-18');
INSERT INTO products VALUES (0006, 'C', '2024-09-22');

-- view all rows in table
SELECT * FROM products;

 productid | name | date_sold  
-----------+------+------------
         1 | A    | 2024-09-11
         2 | A    | 2024-09-12
         3 | B    | 2024-09-10
         4 | B    | 2024-09-15
         5 | C    | 2024-09-18
         6 | C    | 2024-09-22

Suppose that we would like to get the column names and datatypes for each column in the table.

We can use the following syntax to do so:

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

 column_name | data_type 
-------------+-----------
 productid   | integer
 date_sold   | date
 name        | text

The resulting table shows the column name and datatype for each column in the table.

Feel free to use similar syntax to get the column names and datatypes for each column in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Round Numbers to 2 Decimal Places
PostgreSQL: Count Occurrences of Each Value in Column