How do I get table and columns information from Redshift?

Question

pg_tables provides a list of tables. Is there a pg_columns or its equivalent to provide the list of columns?

In DB2, I would query sysibm.systables/columns to get such information. What is the equivalent in redshift?

Answer 1

Use PG_TABLE_DEF table for getting that information:

It looks like this:

select * from pg_table_def where tablename = 't2';

schemaname|tablename|column|  type   | encoding | distkey |sortkey| notnull 
----------+---------+------+---------+----------+---------+-------+---------
public    | t2      | c1   | bigint  | none     | t       |     0 | f
public    | t2      | c2   | integer | mostly16 | f       |     0 | f
public    | t2      | c3   | integer | none     | f       |     1 | t
public    | t2      | c4   | integer | none     | f       |     2 | f
(4 rows)

Answer 2

From http://www.postgresonline.com/journal/archives/20-The-Anatomy-of-PostgreSQL-Part-2-Database-Objects.html :

The information_schema is a very important schema and is part of the ANSI standard, but is not quite so standard. It would be nice if all relational databases supported it, but they don't all do - MySQL 5, SQL Server (2000+), and PostgreSQL (7.4+) support them. Oracle and DB2 evidentally still don't, but there is hope. For the DBMS that support the information_schema, there are varying levels, but in all you can be pretty much assured to find tables, views, columns with same named fields that contain the full listings of all the tables in a database, listings of views and view definition DDL and all the columns, sizes of columns and datatypes.

The pg_catalog schema is the standard PostgreSQL meta data and core schema. You will find pre-defined global postgres functions in here as well as useful meta data about your database that is very specific to postgres. This is the schema used by postgres to manage things internally. A lot of this information overlaps with information found in the information_schema, but for data present in the information_schema, the information_schema is much easier to query and requires fewer or no joins to arrive at basic information.

So for basic queries, you're better off with information_schema.

This page (http://www.alberton.info/postgresql_meta_info.html) shows a beautiful list of queries to get information about your schema. Here is what is relevant to this question:

SELECT a.attname
  FROM pg_class c, pg_attribute a, pg_type t
 WHERE c.relname = 'test2'
   AND a.attnum > 0
   AND a.attrelid = c.oid
  AND a.atttypid = t.oid

-- with INFORMATION_SCHEMA:

SELECT column_name
  FROM information_schema.columns
 WHERE table_name = 'test2';

For detailed infos, there is:

SELECT a.attnum AS ordinal_position,
         a.attname AS column_name,
         t.typname AS data_type,
         a.attlen AS character_maximum_length,
         a.atttypmod AS modifier,
         a.attnotnull AS notnull,
         a.atthasdef AS hasdefault
    FROM pg_class c,
         pg_attribute a,
         pg_type t
   WHERE c.relname = 'test2'
     AND a.attnum > 0
     AND a.attrelid = c.oid
     AND a.atttypid = t.oid
ORDER BY a.attnum;

-- with INFORMATION_SCHEMA:

  SELECT ordinal_position,
         column_name,
         data_type,
         column_default,
         is_nullable,
         character_maximum_length,
         numeric_precision
    FROM information_schema.columns
   WHERE table_name = 'test2'
ORDER BY ordinal_position;

Answer 3

In case @Tomasz Tybulewicz's query returns an empty result is very likely that the table you are querying is not in public schema.

Hence run first SET search_path TO my_schema and the query

select * 
from pg_table_def 
where tablename = 't2';

will show the desired result.