How do I get table and column information from Redshift?

Amazon Redshift allows its users to store their data on the cloud, it is fast, cost-effective, and simple for investigating large datasets. It is important to understand the structure of the database, tables, columns and how we can manage our data on Amazon Redshift. There are numerous ways through which we can get table and column information from Amazon Redshift.

In this article, we will learn how to get table and column information from Redshift and which method gives us most of it: filtering results and querying metadata.

Table and column information using pg_table_def.

In Amazon Redshift, to get table and column information, use pg_table_def. pg_table_def gives all the information about the table (tables that are visible to users) and column. It returns all the information you would like to know.

The below query selects all the fields from the pg_table_def after evaluating the condition. The condition says to display all the fields from pg_table_def where the schema name is pg_catalog. If you don’t want to specify the schema just execute SELECT *FROM pg_table_def. This will return information about all the tables and schemas that are created in the Amazon Redshift database. You can filter the fields and records according to your needs.

Code:

# getting table and column info.
SELECT *FROM
  pg_table_def
WHERE
  schemaname = 'pg_catalog';

Output:

Table and column information using information_schema.columns

We can get table and column information from the information_schema.column. it gives a detailed output containing all the columns, all the tables, and views. Information_schema.columns have all the information a user possibly could ask for.

It holds all the information from table name to column name, schema, ordinal position, data type, character maximum length, and many more. It returns thirty-four fields if you select all without any condition. If you want to get detailed table and column information, then this method is the best match.

Code:

# getting table and column info using schema.columns
SELECT
  table_schema,
  table_name,
  column_name,
  ordinal_position,
  data_type,
  character_maximum_length,
  numeric_precision,
  numeric_scale,
  dtd_identifier,
  is_self_referencing,
  is_nullable
FROM
  information_schema.columns;

Output:

Table and column information using SVV_ALL_COLUMNS

SVV_ALL_COLUMNS returns all the information about columns joining together column from Amazon Redshift and a list of external tables. If you select all without specifying any condition it will return a total of fourteen fields containing information of tables and columns.

Code:

# getting table and column info using SVV_ALL_COLUMNS
SELECT *
FROM svv_all_columns;

Output:

Column information from the database using show columns

Show columns display the specific table information here it shows information of students table displaying database name, schema name, table name, ordinal position, and all the basic information we want to retrieve. To execute this query database_name.schema_name_table_name are required.

Code:

# getting Column information from the database using show columns
SHOW COLUMNS FROM TABLE dev.public.students;

Output:

Filter by table name

In the end, we are filtering the table from pg_table_def where the table name is students. It will return all the fields containing information on the students table in the Amazon Redshift database. You can filter the required data based on the condition and you can also select which field to display in the result.

Code:

# filtering by table name
SELECT *FROM
  pg_table_def
WHERE
  tablename = 'students';

Output:

Conclusion:

In conclusion, we need to get table and column information to know the database, number of fields, primary keys, schema, and other table and column information. This information is required when you want to select, update, or delete the data from the database.

Amazon Redshift provides us with to solution to get all the information about a specific table and column or all the tables and columns present in the database. Several methods have been discussed in the article to get the table and column information redshift. Now it’s your choice to select which method best suits your requirements. In the end, we filtered the pg_table_def to get students table information.