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 information on 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 a function to get the columns list of a particular table.
Using the 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 a 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:
