Reference: Tables

CREATE TABLE

CREATE TABLE creates a new relation with the specified columns.

Usage example:

CREATE TABLE species (
    id              int PRIMARY KEY,
    common_name     text,
    botanical_name  text NOT NULL,
    genus_id        int REFERENCES genus(id)
);

After executing this statement, you can find the created table in the pg_tables system view.

Columns

Column definitions are specified as: name type constraint. Column names can be any identifier; however, for arbitrary character sequences, you need to enclose them in double quotes: "complex name". You can find a list of supported types in the data types reference.

CedarDB supports the following column-level constraints:

• DEFAULT expression: default value used when no value is supplied on INSERT.
• GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY: sequence / serial column.
• NOT NULL: rejects null values.
• UNIQUE: enforces uniqueness for this column.
• PRIMARY KEY: equivalent to UNIQUE and NOT NULL. Only one is allowed per table.
• REFERENCES other_table(col): foreign key into another table.
• CHECK (condition): rejects rows that do not satisfy the condition.

Constraints

The UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints can be specified per-column or at the table level (after all column definitions), and can optionally be given a name using the CONSTRAINT keyword:

CREATE TABLE observations (
    id          int,
    species     int,
    site        int,
    observed_at date,
    CONSTRAINT observations_pk PRIMARY KEY (id),
    CONSTRAINT observations_species_fk FOREIGN KEY (species) REFERENCES species (id),
    CONSTRAINT observations_site_fk FOREIGN KEY (site) REFERENCES sites (id),
    CONSTRAINT observations_unique UNIQUE (species, site, observed_at),
    CONSTRAINT observations_id_positive CHECK (id > 0)
);

The CONSTRAINT <name> part can be omitted entirely, in which case CedarDB automatically assigns a default name using the same conventions as PostgreSQL:

• Primary key: tablename_pkey
• Unique: tablename_colname_key
• Foreign key: tablename_colname_fkey
• Check: tablename_colname_check

These default names can be used just like explicit names, e.g., to drop a constraint with ALTER TABLE ... DROP CONSTRAINT <name>. Naming is not supported for NOT NULL.

Foreign Key Actions

CedarDB supports the following referential actions on foreign keys:

• ON DELETE CASCADE: deletes child rows when the referenced row is deleted.
• ON DELETE RESTRICT: prevents deletion if child rows exist.
• ON DELETE NO ACTION: same as RESTRICT, and the default.
• ON UPDATE CASCADE: updates child rows when the referenced key value changes.

ON DELETE SET NULL and ON DELETE SET DEFAULT are not yet implemented.

Options

Create a temporary table that exists only for the current session:

CREATE TEMP TABLE temp_results (id int, score numeric);

Do not throw an error if a table with the same name already exists:

CREATE TABLE IF NOT EXISTS species (id int PRIMARY KEY, common_name text);

Create a table using the column layout of an existing table. This copies the column names and types, but not constraints or defaults:

CREATE TABLE species_archive (LIKE species);

Create a table partitioned by a hash of a column:

CREATE TABLE observations (
    id      int,
    species int,
    site    text
) PARTITION BY HASH (id);

Create a table that stores all compressed data on a remote server previously created with name remote_storage (see CREATE SERVER for more information):

CREATE TABLE remote_species (...) WITH (server = remote_storage);

Identity Columns

Identity columns automatically generate unique integer values:

CREATE TABLE specimens (
    id          int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    accession   text NOT NULL,
    collected   date
);

-- id is assigned automatically
INSERT INTO specimens (accession, collected) VALUES ('HRB-00142', '2024-03-15');

With GENERATED BY DEFAULT AS IDENTITY, you can supply a value explicitly on INSERT. With GENERATED ALWAYS AS IDENTITY, you need to use OVERRIDING SYSTEM VALUE to override the generated value.

Permissions

To create a table, you need the CREATE privilege on the target schema. By default, every role has the CREATE privilege on the public schema.

CREATE TABLE AS

CREATE TABLE AS creates a table with a schema inferred from the output of a query, and optionally populates it with the query result.

Usage example:

CREATE TABLE recent_observations AS
    SELECT *
    FROM observations
    WHERE observed_at >= now()::date - INTERVAL '1' year;

Create the table structure without copying any rows:

CREATE TABLE observations_archive AS
    SELECT * FROM observations
    WITH NO DATA;

Override the output column names:

CREATE TABLE taxon_counts (taxon, cnt) AS
    SELECT family, count(*) FROM species GROUP BY family;

Create a temporary table that is automatically dropped at the end of the transaction:

BEGIN;
CREATE TEMP TABLE session_results ON COMMIT DROP AS
    SELECT id, score FROM compute_scores();
COMMIT;

SELECT INTO

CedarDB also supports the alternative SELECT INTO syntax for compatibility with PostgreSQL:

SELECT *
INTO recent_observations
FROM observations
WHERE observed_at >= now()::date - INTERVAL '1' year;

Caveats

The data types and names of the created columns can be surprising for queries with multiple expressions. E.g., CedarDB promotes the precision of numeric types to avoid overflows, or infers that columns are guaranteed not-null from query predicates. For more control over the schema, consider using the regular CREATE TABLE statement.

DROP TABLE

DROP TABLE removes a table and all its data.

DROP TABLE observations;

Do not throw an error if the table does not exist:

DROP TABLE IF EXISTS staging_data;

Drop multiple tables in one statement:

DROP TABLE staging_data, temp_results;

If another table has a foreign key referencing the table being dropped, the DROP fails with an error. Drop the dependent table first. DROP TABLE CASCADE is not yet supported.

Permissions

To drop a table you must own it, own its schema, or be a database superuser.

ALTER TABLE

ALTER TABLE modifies the definition of an existing table.

Column statements

ADD COLUMN

ALTER TABLE species ADD COLUMN iucn_status text;

Add a column only if it does not already exist:

ALTER TABLE species ADD COLUMN IF NOT EXISTS iucn_status text;

DROP COLUMN

ALTER TABLE species DROP COLUMN iucn_status;

Drop a column, silently ignoring if it does not exist:

ALTER TABLE species DROP COLUMN IF EXISTS iucn_status;

Drop a column and cascade to any dependent objects, like other columns that reference it:

ALTER TABLE observations DROP COLUMN raw_notes CASCADE;

RENAME COLUMN

ALTER TABLE species RENAME COLUMN botanical_name TO scientific_name;

Renaming a column does not rename any constraints whose default name was derived from the old column name. For example, a unique constraint with the default name species_botanical_name_key keeps that name after the column is renamed.

RENAME TABLE

ALTER TABLE species RENAME TO plant_species;

Constraint statements

ADD CONSTRAINT

Adds a constraint to an existing table. CedarDB supports adding named PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints:

ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY (id);
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer) REFERENCES customers (id);
ALTER TABLE orders ADD CONSTRAINT orders_unique UNIQUE (customer, item);

Foreign key actions like ON DELETE CASCADE are also supported when adding a constraint:

ALTER TABLE observations ADD CONSTRAINT obs_species_fk
    FOREIGN KEY (species_id) REFERENCES species (id) ON DELETE CASCADE;

The CONSTRAINT <name> part can be omitted, in which case CedarDB automatically assigns a default name:

• Primary key: tablename_pkey
• Unique: tablename_colname_key
• Foreign key: tablename_colname_fkey

When adding a FOREIGN KEY constraint to an existing table, CedarDB validates all existing rows. If any row would violate the constraint, the statement fails.

Adding a CHECK constraint to an existing table is not yet supported. CHECK constraints can only be specified at CREATE TABLE time.

DROP CONSTRAINT

Removes a constraint by name:

ALTER TABLE orders DROP CONSTRAINT orders_unique;
ALTER TABLE orders DROP CONSTRAINT orders_pkey;

Silently ignore if the constraint does not exist:

ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_unique;

Drop a constraint and cascade to dependent constraints:

ALTER TABLE child_table DROP CONSTRAINT fk_constraint CASCADE;

Ownership

ALTER TABLE species OWNER TO botanist_role;

Row Level Security

Enable row-level security on a table. Policies must be created separately to actually filter rows:

ALTER TABLE observations ENABLE ROW LEVEL SECURITY;
ALTER TABLE observations DISABLE ROW LEVEL SECURITY;

Force row-level security to apply even to the table owner:

ALTER TABLE observations FORCE ROW LEVEL SECURITY;
ALTER TABLE observations NO FORCE ROW LEVEL SECURITY;

Row-level security policies are created with CREATE POLICY. This feature requires an enterprise license.

Permissions

To alter a table you must be its owner. Superusers can alter any table.