CREATE TABLE Queries

Builds CREATE TABLE statements using SqlCreateTable.

CREATE TABLE is a DDL statement and produces no parameterized arguments.

These classes are primarily useful for temporary tables whose columns depend on program logic — the structure of the table may vary based on runtime conditions, making string manipulation awkward.

Basic Table

qb = (SQL.create_table('films')
      .column('code', 'char(5) CONSTRAINT firstkey PRIMARY KEY')
      .column('title', 'varchar(40) NOT NULL')
      .column('did', 'integer NOT NULL')
      .column('date_prod', 'date')
      .column('kind', 'varchar(10)'))

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

IF NOT EXISTS

qb = (SQL.create_table('films')
      .if_not_exists()
      .column('id', 'serial PRIMARY KEY')
      .column('title', 'text'))

CREATE TABLE IF NOT EXISTS films (
    id serial PRIMARY KEY,
    title text
)

UNLOGGED

PostgreSQL only.

qb = (SQL.create_table('fast_log')
      .unlogged()
      .column('id', 'bigserial')
      .column('msg', 'text'))

CREATE UNLOGGED TABLE fast_log (
    id bigserial,
    msg text
)

TEMPORARY

qb = (SQL.create_table('temp_results')
      .temporary()
      .on_commit_drop()
      .column('id', 'integer')
      .column('value', 'text'))

CREATE TEMPORARY TABLE temp_results (
    id integer,
    value text
) ON COMMIT DROP

Table Constraints

qb = (SQL.create_table('orders')
      .column('id', 'integer')
      .column('product_id', 'integer')
      .constraint('PRIMARY KEY (id)')
      .constraint('FOREIGN KEY (product_id) REFERENCES products(id)'))

CREATE TABLE orders (
    id integer,
    product_id integer,
    PRIMARY KEY (id),
    FOREIGN KEY (product_id) REFERENCES products(id)
)

Exclusion constraint:

qb = (SQL.create_table('circles')
      .column('c', 'circle')
      .constraint('EXCLUDE USING gist (c WITH &&)'))

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

LIKE

qb = (SQL.create_table('new_films')
      .like('films', 'INCLUDING ALL'))

CREATE TABLE new_films (
    LIKE films INCLUDING ALL
)

INHERITS

PostgreSQL only.

qb = (SQL.create_table('cities_west')
      .column('population', 'integer')
      .inherits('cities'))

CREATE TABLE cities_west (
    population integer
) INHERITS (cities)

PARTITION BY

PostgreSQL only.

qb = (SQL.create_table('measurement')
      .column('logdate', 'date NOT NULL')
      .column('peaktemp', 'int')
      .column('unitsales', 'int')
      .partition_by('RANGE (logdate)'))

CREATE TABLE measurement (
    logdate date NOT NULL,
    peaktemp int,
    unitsales int
) PARTITION BY RANGE (logdate)

TABLESPACE

PostgreSQL only.

qb = (SQL.create_table('cinemas')
      .column('id', 'serial')
      .column('name', 'text')
      .tablespace('diskvol1'))

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