YogeshChauhan . com

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

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

Attributes and Properties in JavaScript and HTMLHTMLIntroduction to Angular modules Part 2: NgModules and componentsAngularUse eq() method in jQueryjQueryHow to hide a DIV on clicks outside of it using jQuery?jQueryHow to Make a Simple Module with a Form and Menu Link in Drupal 7.x?DrupalWordPress: How to get ACF field values from another post?WordPress