Yogesh Chauhan's Blog

CREATE TABLE Examples in PostgreSQL

in Postgres on December 13, 2020

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
);

Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
How to Skip or Exclude a Specific URL or File From .htaccess Rewrite Rule?PHPHow to create a Recent Posts function in WordPress?WordPressHow to float an image around texts?CSSHow to Recognize an Array in JavaScript?JavaScriptDebugging in WordPress Part 2: WP_DEBUG_LOG and WP_DEBUG_DISPLAYWordPressHow to get query string values in JavaScript? (URL Manipulation using URLSearchParams)JavaScript