Display Amazon Redshift Database Table Columns List using SQL

Frequently SQL developers require to display the column list of a specific database table by SQL query. In this Amazon Redshift Data Warehouse tutorial, I want to share with SQL programmers how to query database table columns list using system table and views. In addition to displaying and querying system table and system views for columns list, I want to show useful details like default values of columns, or comments/remarks on columns or to concatenate column names of target Redshift database table.

I have an Amazon Redshift cluster running on my AWS account.
As seen below, using SQL Workbench/J I connected to Redshift Data Warehouse sampledb database
Under Table node, I can see the list of tables in "public" schema.
When I extend the "Columns" node for "category" sample table, I can see the columns forming the category database table.

Database Management tools enable administrators and SQL developers to display the table columns easily.
But if the requirement is to get the columns list using SQL code, then system tables and system views can be used.

Redshift PG_TABLE_DEF Catalog Table for Table Columns

One of the system catalog tables for PostgreSQL and Amazon Redshift databases where table columns can be fetched is PG_TABLE_DEF

Here is a basic SQL query that can be used to find a specific tables columns list

SELECT * FROM PG_TABLE_DEF WHERE tablename IN ('category');

SQL database developer can add multiple table names using the IN clause and separating table names using a comma. Please note that the tablename value is case sensitive, so the table name should be written exactly the same as it is created.

If the upper/lower case can cause a problem for the SQL user, UPPER() or LOWER() functions can be used as seen in below SQL query.

SELECT * FROM PG_TABLE_DEF WHERE lower(tablename) = lower('Category');
SELECT * FROM PG_TABLE_DEF WHERE upper(tablename) = upper('Category');

The returned data for table columns contains following columns from PG_TABLE_DEF catalog table

schemaname is the name of the schema that target table is created under.

tablename is the table name

column shows the column name in a specific table

type shows the data type name like integer, date, timestamp, etc. For the Amazon Redshift database data types, Amazon Redshift's Data types page can be referred.

encoding indicates the compression encoding settings for that column. For a complete list of Amazon Redshift Compression Encodings, you can refer to Database Developer Guide

distkey is true if the column is used as a Distributio Key for that database table

sortkey is an integer value. If it is 0, then this column is not a sort key for the table. Otherwise, the column is either part of a compound sort key or interleaved sort key

notnull column value is true if the column is created by using a NOT NULL constraint

Information_Schema.Columns for Redshift Table Columns Details

Another useful system view for querying table columns on a Redshift database is Information_Schema.Columns view. Here is a sample SQL query that can be executed to get all columns details of a Redshift table.

SELECT * FROM information_schema.columns WHERE table_name = 'category';

Redshift Database SVV_COLUMNS System View

SVV_COLUMNS is an Amazon Redshift database SVV system view containing detailed data about database table columns.

Here is the SQL statement that can be executed on Redshift database to list columns with detailed information

SELECT * FROM SVV_COLUMNS WHERE upper("table_name") = upper('Category');

The SVV_COLUMNS system view has more details about each table column then the catalog view PG_TABLE_DEF

table_catalog is the database name where the table is created

table_schema is the schema name where the table is created under

table_name is the name of the table where we filter for columns of it

column_name is the name of the table column we queried for

ordinal_position shows the position of the column in the table definition. Considering the columnar storage of Amazon Redshift architecture, this does not provide a useful information. On the other hand, using the ordinal position the automatic code generation processes can be built.

column_default shows the default value if the table DDL is created with a default value for the column definition. The column default text includes the data type identifiers for character data types. So it cannot be used directly as the default value. For example, if the table is created using SQL code "create table redshifttable (code varchar(5) default 'AAAAA');" the column_default value will be "'AAAAA'::character varying"

is_nullable identifies if column accepts NULL values or not by YES/NO value

data_type shows the table column's data type

character_maximum_length shows the maximum number of character values allowed for the column

numeric_precision for columns with numberic data types, shows the numeric precision. (SmallInt:16, Int:32, BigInt:64, for decimals it is the first argument in definition)

numeric_precision_radix shows the numeric precision radix. For integer values this is 2 and for decimal values it is 10.

numeric_scale shows the numeric scale. It is the second argument used while creating a decimal data type.

datetime_precision indicates the datetime precision

interval_type shows the interval type

interval_precision shows the interval precision. It is interesting that inverval data type is not supported within Amazon Redshift tables, but it is listed in SVV_COLUMNS system view.

character_set_catalog

character_set_schema

character_set_name

collation_catalog

collation_schema

collation_name

domain_name

remarks is the comment text created for the column. For example, to create a remark for a specific table column following SQL statement can be used: "comment on column RedshiftTable.DateColumn is 'Record entry date';"

Create Table Column with Default Value on Amazon Redshift Database

To create a table column with default value following sample SQL code can be used:

Create Table DefaultValuesTable (
 Id smallint default 1 not null,
 Code varchar(3) default 'ABC',
 InsertDate date default current_date
);

If we query for the table columns, we can see how the column_default is populated

SELECT * FROM SVV_COLUMNS WHERE upper("table_name") = upper('DefaultValuesTable');

How to Comment on Table Column on Amazon Redshift Database

To create remarks about a table column, Amazon Redshift SQL developers can use COMMENT ON statement as follows:

Comment On Column category.catid is 'Category unique Id integer value';
Comment On Column category.catgroup is 'Category group';
Comment On Column category.catname is 'Category name';
Comment On Column category.catdesc is 'Category description';

We can now observer the comments about table columns in SVV_Columns system view

SELECT table_name, column_name, remarks FROM SVV_COLUMNS WHERE table_name = 'category';

Below screenshot shows the comments provided for each database table column in Remarks column of the system view SVV_COLUMNS

Concatenate Table Column Names for Redshift Database Table

If the requirement is to concatenate column names of a specific Amazon Redshift database table, SQL programmers can use ListAgg Window function. For example, if the SQL developer wants to concatenate and get the list of all columns of sample database table "category", the LISTAGG function can be used as seen in following SQL query.

SELECT DISTINCT
 table_name,
 listagg(column_name,',')
WITHIN GROUP (ORDER BY ordinal_position)
OVER (PARTITION BY table_name) AS columnlist
FROM SVV_COLUMNS
WHERE table_name = 'category'
ORDER BY table_name;

The output of the above SQL query execution is as seen in following screenshot of SQL Workbench /J database management tool.

If developers check the sample SQL query, they will realize omitting the WHERE clause will result with aggregated or concatenated columns list for each table in current database.

I hope Amazon Redshift SQL programmers will find this information useful for listing all the columns of a specific database table.