CREATE TABLE Examples in PostgreSQL

PostgreSQL CREATE TABLE syntax is very complex and really made for large database query. We are not going into that in this post but I am just going to list examples to help you understand the concept a bit.

Create table films and table distributors

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

CREATE TABLE distributors (
     did    integer PRIMARY KEY DEFAULT nextval('serial'),
     name   varchar(40) NOT NULL CHECK (name <> '')
);

Create a table with a 2-dimensional array

CREATE TABLE array_int (
    vector  int[][]
);

Define a unique table constraint

Let’s define a unique table constraint for the table films.

Unique table constraints can be defined on one or more columns of the table:

CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);

Define a check column constraint

CREATE TABLE distributors (
    did     integer CHECK (did > 100),
    name    varchar(40)
);

Define a check table constraint

CREATE TABLE distributors (
    did     integer,
    name    varchar(40)
    CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);

Define a primary key table constraint

Let’s do that for the table films

CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);

Define a primary key constraint

Let’s define it for table distributors.

The following two examples are equivalent.

this first one using the table constraint syntax:

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);

this second one using the column constraint syntax:

CREATE TABLE distributors (
    did     integer PRIMARY KEY,
    name    varchar(40)
);

In this following example, there are 3 parts 

CREATE TABLE distributors (
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);

First, we assign a literal constant default value for the column name,

Second, we arrange for the default value of column did to be generated by selecting the next value of a sequence object, 

and, Third we make the default value of modtime be the time at which the row is inserted.

Define two NOT NULL column constraints

Let’s define it on the table distributors, one of which is explicitly given a name:

CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);

Define a unique constraint for the name column

CREATE TABLE distributors (
    did     integer,
    name    varchar(40) UNIQUE
);

The same, specified as a table constraint

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name)
);

Create the same table, specifying 70% fill factor for both the table and its unique index

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);

Create table circles with an exclusion constraint that prevents any two circles from overlapping

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

Create table cinemas in tablespace diskvol1

CREATE TABLE cinemas (
        id serial,
        name text,
        location text
) TABLESPACE diskvol1;

Create a composite type and a typed table

CREATE TYPE employee_type AS (name text, salary numeric);

CREATE TABLE employees OF employee_type (
    PRIMARY KEY (name),
    salary WITH OPTIONS DEFAULT 1000
);

Share this post