YogeshChauhan.com
CREATE TABLE Examples in PostgreSQL
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
);
dreamhost

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?



Recently Posted

Feb 27 WordPress: How to display slider repeater fields in ACF? Feb 26 Simple Page Hit Counter in PHP Feb 26 Here’s what we can do with PHP date() function Feb 25 How to set default timezone using PHP? Feb 24 WordPress: How to loop through a repeater field in ACF? Feb 24 How to get ACF values from custom post type?



You might also like these

Crypto.getRandomValues() method in JavaScriptJavaScriptWhat is PostgreSQL? How similar or different it is from SQL?PostgresHow to create a sticky menu pin using CSS?CSSHow to change CSS with jQuery?CSSHow to get Current Hour, Minute and Second in JavaScript?JavaScriptHow to set up the local environment and workspace for Angular development?Angular