CREATE TABLE Examples

The following examples show different options for CREATE TABLE statements.

Table with replicated distribution and a primary key

Create a replicated table with a column defined as the primary key.
premdb=# create table season
(seasonid smallint primary key, season_name character(9), numteams smallint, winners varchar(30)) 
distribute replicate;
CREATE TABLE
premdb=# \d season
              Table "brumsby.season"
   Column    |         Type          | Modifiers  
-------------+-----------------------+------------
 seasonid    | smallint              | not null
 season_name | character(9)          | 
 numteams    | smallint              |
 winners     | character varying(30) | 

Distribution: Replicated

Table with distribution and sort keys

Create a table with the same column defined for data distribution and sorting:
premdb=# create table match
(seasonid smallint, matchday timestamp, htid smallint, atid smallint, ftscore char(3), htscore char(3)) 
distribute on(seasonid) sort on(seasonid);
CREATE TABLE
premdb=# \d match
               Table "brumsby.match"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | 
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 ftscore  | character(3)                | 
 htscore  | character(3)                | 

Distribution: Hash (seasonid)
Sort Column: (seasonid)

Temporary table with ON COMMIT DELETE ROWS

Create a temporary table that is truncated when a transaction commits:
premdb=# create temp table team
(teamid int, team_name varchar(30)) 
on commit delete rows;
CREATE TABLE
premdb=# \d team
            Table "pg_temp_5.team"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 teamid    | integer               | 
 team_name | character varying(30) | 

Distribution: Hash (teamid)

Table that uses a sequence to generate column values

Create a table with a column named MATCHKEY that uses the NEXTVAL function as its default value.
premdb=# create table matchstats(matchkey bigint default nextval('matchid'), 
seasonid smallint, matchday date, htid smallint, atid smallint, monent varchar(5));
CREATE TABLE
premdb=# \d matchstats
                       Table "public.matchstats"
  Column  |         Type         |              Modifiers               
----------+----------------------+--------------------------------------
 matchkey | bigint               | default nextval('matchid'::regclass)
 seasonid | smallint             | 
 matchday | date                 | 
 htid     | smallint             | 
 atid     | smallint             | 
 monent   | character varying(5) | 

Distribution: Hash (matchkey)

Table with primary and foreign keys

Create a table with a primary key that references another table. (This example requires that the referenced column season.seasonid is declared as UNIQUE.)
premdb=# create table match
(seasonid smallint primary key references season(seasonid), 
matchday timestamp, htid smallint, atid smallint, ftscore char(3), htscore char(3)) 
distribute on(seasonid) 
sort on(seasonid);
CREATE TABLE
premdb=# \d match
                Table "public.match"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | not null
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 ftscore  | character(3)                | 
 htscore  | character(3)                | 

Distribution: Hash (seasonid)
Sort Column: (seasonid)
Columns:
    seasonid PRIMARY KEY
Foreign-key constraints:
    "match_seasonid_fkey" FOREIGN KEY (seasonid) REFERENCES season(seasonid)
You can drop the foreign-key constraint if you want to break the relationship between the two tables. For example:
premdb=# alter table match drop constraint match_seasonid_fkey;
ALTER TABLE

Table with encrypted columns

Create a table with three encrypted columns. When the table is loaded, the source strings in these columns will be encrypted.
premdb=# create table player(
playerid bigint not null,
teamid smallint not null,
seasonid smallint not null,
firstname varchar(30),
lastname varchar(30),
position char(1),
dob varchar(100) encrypted with(COLUMN_ENCRYPTION_KEY=playerkey,ENCRYPTION_TYPE=RANDOMIZED,ALGORITHM = 'AES_256_OFB'),
weekly_wages varchar(100) encrypted with(COLUMN_ENCRYPTION_KEY=playerkey,ENCRYPTION_TYPE=DETERMINISTIC,ALGORITHM = 'AES_128_OFB'),
avg_mins_per_match double precision,
matches_played real,
cob varchar(100) encrypted with(COLUMN_ENCRYPTION_KEY=playerkey,ENCRYPTION_TYPE=RANDOMIZED,ALGORITHM = 'AES_192_OFB'));
CREATE TABLE
Load some rows into this table, then grant user yb100 SELECT on the player table, but do not grant DECRYPT on the key playerkey. When yb100 selects from player, the encrypted values are returned.
premdb=# create user yb100 password 'yb100';
CREATE ROLE
premdb=# grant select on player to yb100;
GRANT
premdb=# \c premdb yb100
You are now connected to database "premdb" as user "yb100".
premdb=> select * from player;
-[ RECORD 1 ]------+-------------------------------------------------------------
playerid           | 2
teamid             | 41
seasonid           | 27
firstname          | Harry
lastname           | Kane
position           | F
dob                | CTc5ZT+iDT7Z80aYy5OTtV1Qk2k0zFci7MgDWUAJzTrMnq0a5iLVm5uEcA==
weekly_wages       | BP9GFdcy5ND0OKXgLeHiMlfMMd0j06Y=
avg_mins_per_match | 84.1567
matches_played     | 36.1
cob                | CDv/nAiOq+vvQzLMRvJQmfD501x6DLXsI6unLmFZ/Ds=
-[ RECORD 2 ]------+-------------------------------------------------------------
playerid           | 3
teamid             | 41
seasonid           | 27
firstname          | Harry
lastname           | Winks
position           | M
dob                | CfvZTq+DCl/ahcZW0gpXL2puITwPhiBbX/0WByBvVVxP/MpSTlalxX97Zg==
weekly_wages       | BLwKffvCFHQhuneexpn6pG+1NdtdLA==
avg_mins_per_match | 72.3412
matches_played     | 31.2
cob                | CDq0xiP8GZkYwCYPwMJULx/5EmXVfy5INpB/H+J/quk=
...
Now grant DECRYPT to yb100. When yb100 queries the table again, the decrypted values are returned.
premdb=# grant decrypt on key playerkey to yb100;
GRANT
premdb=# \c premdb yb100
You are now connected to database "premdb" as user "yb100".
premdb=> select * from player;
 playerid | teamid | seasonid | firstname | lastname  | position |    dob     | weekly_wages | avg_mins_per_match | matches_played |     cob     
----------+--------+----------+-----------+-----------+----------+------------+--------------+--------------------+----------------+-------------
        2 |     41 |       27 | Harry     | Kane      | F        | 07-28-1993 | 250000       |            84.1567 |           36.1 | England
        3 |     41 |       27 | Harry     | Winks     | M        | 02-02-1996 | 40000        |            72.3412 |           31.2 | England
...

If you want users to see clear text for some encrypted columns but not others, use different keys in the CREATE TABLE statement, then grant privileges on the keys accordingly.

Two tables joined on encrypted columns

Regardless of permissions on encryption keys, you can use encrypted columns in equality joins. In this example, a user without DECRYPT privileges runs a query that joins on the weekly_wages column, which is encrypted with the same attributes in both tables:
premdb=> select p.firstname, p.lastname, p.weekly_wages 
from player p, newplayer np where p.weekly_wages=np.weekly_wages and p.playerid=np.playerid;
 firstname | lastname  |           weekly_wages           
-----------+-----------+----------------------------------
 Paul      | Pogba     | BD3kn8ThZr6fFfyR09jKDrHswd7HQZA=
 Alexis    | Sanchez   | BGLAs08top1lIoTRupU3hXTtCgi4r/g=
 Mesut     | Ozil      | BMyXnoYtbTT/+7XBZXGmAAwyNBkoatA=
 Romelu    | Lukaku    | BPGCDY4vTeIcj01zghWRYsIcnneUC2c=
 David     | Silva     | BPGCDY4vTeIcj01zghWRYsIcnneUC2c=
 Jamie     | Vardy     | BDgVHSxmbPe4/OOU+4Q7bfiobM0mVw==
 Marcus    | Rashford  | BPGCDY4vTeIcj01zghWRYsIcnneUC2c=
 Harry     | Kane      | BPGCDY4vTeIcj01zghWRYsIcnneUC2c=
 Harry     | Winks     | BOAXiWS24OEegpbVtFHhsoamN3fsoQ==
 Kevin     | De Bruyne | BB4wPtcyHyViIsXZKLHFCWYn6VVJM0w=
 Gonzalo   | Higuain   | BAhlSXor3krmxTdy9T1DpF9Ds3bw2Po=
 Eden      | Hazard    | BFUsmeTmHAClz25OLCxle7Zjz6PA3Lk=
 Sergio    | Aguero    | BBZMIb5nJ15KinMuUpRstRtpwrng4wA=
 David     | de Gea    | BFUsmeTmHAClz25OLCxle7Zjz6PA3Lk=
 Mo        | Salah     | BFUsmeTmHAClz25OLCxle7Zjz6PA3Lk=
 Riyad     | Mahrez    | BFUsmeTmHAClz25OLCxle7Zjz6PA3Lk=
 Virgil    | Van Dijk  | BA4du6oGxpJ6y29t2DgMI4ax0r/+6Ro=
 Dele      | Alli      | BDYd5WqZEK6YDXC53sC/iYh0TPo3LxQ=
(18 rows)

Note that because the encryption values in this example are deterministic, the user without DECRYPT privilege can still see which players receive the same weekly wages, so there is some of loss of protection in these query results.