31 Aug 2017

psql \d now supports DISTKEY / SORTKEY / ENCODING (in Redshift)

This is in continuation of my work for (my forked version of) psql to better support Redshift (read more here).

Now \d table provides some additional Redshift specific table properties such as:
  • DISTKEY
  • SORTKEY
  • COMPRESSION (ENCODING)
  • ENCRYPTION
Sample:

t3=# CREATE TABLE customer(
  custkey   SMALLINT                ENCODE delta NOT NULL,
  custname  INTEGER DEFAULT 10      ENCODE raw NULL,
  gender    BOOLEAN                 ENCODE RAW,
  address   CHAR(5)                 ENCODE LZO,
  city      BIGINT identity(0, 1)   ENCODE DELTA,
  state     DOUBLE PRECISION        ENCODE Runlength,
  zipcode   REAL,
  tempdel1  DECIMAL                 ENCODE Mostly16,
  tempdel2  BIGINT                  ENCODE Mostly32,
  tempdel3  DATE                    ENCODE DELTA32k,
  tempdel4  TIMESTAMP               ENCODE Runlength,
  tempdel5  TIMESTAMPTZ             ENCODE DELTA,
  tempdel6  VARCHAR(MAX)            ENCODE text32k,
  start_date VARCHAR(10)            ENCODE TEXT255
)
DISTSTYLE KEY
DISTKEY (custname)
INTERLEAVED SORTKEY (custkey, custname);
CREATE TABLE
t3=# \d customer
                                                                   TABLE "public.customer"
   Column   |            Type             | Encoding  | DistKey | SortKey | Preload | Encryption | Collation | Nullable |              Default
------------+-----------------------------+-----------+---------+---------+---------+------------+-----------+----------+------------------------------------
 custkey    | smallint                    | delta     | f       | 1       | f       | none       |           | not null |
 custname   | integer                     | none      | t       | 2       | f       | none       |           |          | 10
 gender     | boolean                     | none      | f       | 0       | f       | none       |           |          |
 address    | character(5)                | lzo       | f       | 0       | f       | none       |           |          |
 city       | bigint                      | delta     | f       | 0       | f       | none       |           |          | "identity"(493983, 4, '0,1'::text)
 state      | double precision            | runlength | f       | 0       | f       | none       |           |          |
 zipcode    | real                        | none      | f       | 0       | f       | none       |           |          |
 tempdel1   | numeric(18,0)               | mostly16  | f       | 0       | f       | none       |           |          |
 tempdel2   | bigint                      | mostly32  | f       | 0       | f       | none       |           |          |
 tempdel3   | date                        | delta32k  | f       | 0       | f       | none       |           |          |
 tempdel4   | timestamp without time zone | runlength | f       | 0       | f       | none       |           |          |
 tempdel5   | timestamp with time zone    | delta     | f       | 0       | f       | none       |           |          |
 tempdel6   | character varying(65535)    | text32k   | f       | 0       | f       | none       |           |          |
 start_date | character varying(10)       | text255   | f       | 0       | f       | none       |           |          |

Now that a few 'ToDos' are listed on Github Issues, next would probably involve working on this ticket, which aims at elaborate SORTKEY details (such as INTERLEAVED / COMPOUND) etc. when using Describe Table.

Update (15th Sep 2017):
This project has now been named PsqlForks!

No comments:

Find Database DNS / Endpoint via SQL

How to get Database identifier using SQL Often there is a need for client programs to find "where am I logged into?". This blog po...