SqlCreateTable

class SqlCreateTable(name)

Builds a CREATE TABLE statement. Created via SQL.create_table(name). Inherits from SqlStatement.

Parameters:

name – Table name string (optionally schema-qualified).

In the examples below, qb refers to a SqlCreateTable instance:

qb = SQL.create_table('films')

column(name, spec)

Add a column definition. Column constraints (NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, CHECK, REFERENCES) can be included in the spec string.

Parameters:

• name – Column name string.

• spec – Data type and optional inline constraints as a string.

qb.column('id', 'serial PRIMARY KEY')
qb.column('name', 'varchar(40) NOT NULL')
qb.column('price', 'numeric CHECK (price > 0)')

constraint(spec)

Add a table-level constraint (rendered after columns inside the parentheses).

Parameters:

spec – Constraint definition string.

qb.constraint('PRIMARY KEY (id)')
qb.constraint('FOREIGN KEY (dept_id) REFERENCES departments(id)')
qb.constraint('EXCLUDE USING gist (c WITH &&)')
qb.constraint("CONSTRAINT con1 CHECK (did > 100 AND name <> '')")

like(source_table, *options)

Add a LIKE clause to copy structure from another table.

Parameters:

• source_table – Source table name string.

• options – Zero or more option strings (e.g. 'INCLUDING ALL', 'INCLUDING DEFAULTS', 'EXCLUDING INDEXES').

qb.like('films', 'INCLUDING ALL')
# LIKE films INCLUDING ALL

temporary()

Create a TEMPORARY table.

qb.temporary()
# CREATE TEMPORARY TABLE ...

unlogged()

Create an UNLOGGED table.

qb.unlogged()
# CREATE UNLOGGED TABLE ...

if_not_exists()

Add IF NOT EXISTS to avoid errors when the table already exists.

qb.if_not_exists()
# CREATE TABLE IF NOT EXISTS ...

inherits(*tables)

Set the INHERITS clause.

Parameters:

tables – One or more parent table name strings.

qb.inherits('cities')
# ) INHERITS (cities)

partition_by(spec)

Set the PARTITION BY clause.

Parameters:

spec – Partition specification string.

qb.partition_by('RANGE (logdate)')
# ) PARTITION BY RANGE (logdate)

qb.partition_by('LIST (left(lower(name), 1))')
# ) PARTITION BY LIST (left(lower(name), 1))

on_commit(action)

Set the ON COMMIT behaviour for temporary tables.

Parameters:

action – One of 'DROP', 'DELETE ROWS', or 'PRESERVE ROWS'.

qb.on_commit('DELETE ROWS')
# ) ON COMMIT DELETE ROWS

on_commit_drop()

Shorthand for on_commit('DROP').

qb.on_commit_drop()
# ) ON COMMIT DROP

tablespace(name)

Set the TABLESPACE clause.

Parameters:

name – Tablespace name string.

qb.tablespace('diskvol1')
# ) TABLESPACE diskvol1