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

If you are a DBA and you are working on a data-dictionary for a project so that you can keep track of schema changes and so on, then this article will help you achieve your right things. Using schema information_schema and table columns we can get the all infornation of the particular column.

Get the list of columns and its details using information_schema.columns


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


Note:
schema_name = public
table_name = product
Also, using the following query you can get the list of column name only without other information.

SELECT *
  FROM table_name
 WHERE false;

Using pg_catalog.pg_attribute, get the list of columns

List the columns and their descriptions such as data type, column name, etc .. Use the following query for the table.


SELECT
        a.attname as "Column",
        pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
                                --more attributes
    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 ~ '^(table_name)$'
                AND pg_catalog.pg_table_is_visible(c.oid)
        );



And see the following query which may help you to list the column 


SELECT
    "pg_attribute".attname as "Column",
    pg_catalog.format_type("pg_attribute".atttypid, "pg_attribute".atttypmod) as "Datatype",
    not("pg_attribute".attnotnull) AS "Nullable"
                --,more attributes
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_name'
            AND "pg_class".relname = 'table_name'
    );


Creating function to get column list of particular table.

Using above query we can create a function with schema and table name parameter, one thing also I want to improve like I want to top one value of a column for the data illustration, basically we use it in data-dictionary.

CREATE OR REPLACE FUNCTION get_column_list (my_schema_name text, my_table_name text)
   RETURNS TABLE (
                table_schema text,
                table_name text,
                column_name text,
                data_type text,
                is_nullable text, 
                value text
)
AS $$
DECLARE cnt integer;
mytable text;
val text;
col text;
BEGIN

CREATE temporary TABLE Tmp
(
                id INT GENERATED ALWAYS AS IDENTITY,
                table_schema text,
                table_name text,
                column_name text,
                data_type text,
                is_nullable text, 
                value text,
    PRIMARY KEY(id)
);
 
INSERT INTO Tmp(  table_schema,table_name,column_name, data_type, is_nullable )
SELECT c.table_schema, c.table_name, c.column_name, c.data_type, c.is_nullable
  FROM information_schema.columns c
WHERE c.table_schema = my_schema_name
  AND c.table_name   = my_table_name;

SELECT  COUNT(*) INTO cnt FROM Tmp ;

LOOP
EXIT WHEN  cnt=0;
 col := (select  t.column_name  from Tmp t where t.id = cnt);
 mytable := (select t.table_name  from Tmp t where t.id = cnt);  
EXECUTE format('update Tmp t set value= (SELECT %s from %s limit 1) where t.id=%s;',col,mytable,cnt );
cnt := cnt-1;
END LOOP; 
                RETURN query
                SELECT t.table_schema,t.table_name,t.column_name, t.data_type, t.is_nullable, t.value from Tmp t ;
DROP TABLE  if exists Tmp;
END; $$ 
LANGUAGE 'plpgsql';


Suppose we have the Products table as below, and want to the list of the columns and its information.

CREATE TABLE products
(
    product_id serial NOT NULL,
    product_name text,
    category text,
    quantity numeric
);

-- Inserted some records into PRODUCTS table

INSERT INTO products(product_name, category, quantity)
VALUES('prod_1','category_1',100);

INSERT INTO products(product_name, category, quantity)
VALUES('prod_2','category_2',200);

INSERT INTO products(product_name, category, quantity)
VALUES('prod_3','category_3',400);

INSERT INTO products(product_name, category, quantity)
VALUES('prod_4','category_4',500);


  
Get the column list using get_column_list() funtion.

SELECT * FROM   get_column_list('public','products');


Result:

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...