Product Documentation

Appearance

All platforms

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.) The primary key declaration results in an implicit foreign-key constraint.

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 a two-column primary key and an explicit foreign-key constraint ​

Create a table with a primary key that spans two columns, and an explicit foreign-key reference to another table. (This example requires that the referenced column season.seasonid is declared as UNIQUE.)

premdb=# create table player(playerid bigint, teamid smallint, seasonid smallint, firstname varchar(30), lastname varchar(30), position char(1), dob date, cob varchar(30), 
primary key (playerid, teamid), 
foreign key (seasonid) references season(seasonid));
CREATE TABLE
premdb=# \d player
            Table "public.player"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 playerid  | bigint                | not null
 teamid    | smallint              | not null
 seasonid  | smallint              | 
 firstname | character varying(30) | 
 lastname  | character varying(30) | 
 position  | character(1)          | 
 dob       | date                  | 
 cob       | character varying(30) | 

Distribution: Hash (playerid)
Columns:
   playerid, teamid PRIMARY KEY
Foreign-key constraints:
   "player_seasonid_fkey" FOREIGN KEY (seasonid) REFERENCES season(seasonid)
Primary-key constraints:
   "player_pkey" PRIMARY KEY (playerid, teamid)

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.