# Databases : PostgreSQL : Schema \[ [ALTER DATABASE](https://www.postgresql.org/docs/current/sql-alterdatabase.html) | [ALTER INDEX](https://www.postgresql.org/docs/current/sql-alterindex.html) | [ALTER SCHEMA](https://www.postgresql.org/docs/current/sql-alterschema.html) | [ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html) | [ALTER VIEW](https://www.postgresql.org/docs/current/sql-alterview.html) | [CREATE DATABASE](https://www.postgresql.org/docs/current/sql-createdatabase.html) | [CREATE SCHEMA](https://www.postgresql.org/docs/current/sql-createschema.html) | [CREATE TABLE](https://www.postgresql.org/docs/current/sql-createtable.html) | [CREATE VIEW](https://www.postgresql.org/docs/current/sql-createview.html) | [DROP DATABASE](https://www.postgresql.org/docs/current/sql-dropdatabase.html) | [DROP INDEX](https://www.postgresql.org/docs/current/sql-dropindex.html) | [DROP SCHEMA](https://www.postgresql.org/docs/current/sql-dropschema.html) | [DROP TABLE](https://www.postgresql.org/docs/current/sql-droptable.html) | [DROP VIEW](https://www.postgresql.org/docs/current/sql-dropview.html) ] ## CREATE ```sql CREATE DATABASE <database> [TEMPLATE <template>] # template defaults to template1 ``` ```sql CREATE TABLE [ IF NOT EXISTS ] <table> ( -- column definition <type1> <column1> [DEFAULT <expr>] -- column constraints NOT NULL [CONSTRAINT <constraint_name>] CHECK ( <exp> ) [CONSTRAINT <constraint_name>] PRIMARY KEY [CONSTRAINT <constraint_name>] UNIQUE [NULLS NOT DISTINCT] [CONSTRAINT <constraint_name>] REFERENCES <target_table> ( <target_columnA>, ... ) -- if not specified, defaults to table's PK [ON DELETE CASCADE -- delete referencing rows NO ACTION RESTRICT -- prevent deletion of referenced row SET DEFAULT SET NULL ] -- or... -- table constraint [CONSTRAINT <constraint_name>] CHECK ( <exp> ) [CONSTRAINT <constraint_name>] PRIMARY KEY ( <columnA>, ... ) [CONSTRAINT <constraint_name>] UNIQUE ( <columnA>, ... ) [NULLS NOT DISTINCT] [CONSTRAINT <constraint_name>] FOREIGN KEY ( <column1>, ... ) REFERENCES <table> ( <column1>, ... ) -- multi-column FK [MATCH FULL] [ON DELETE ...] [, ...] ) -- copy table CREATE TABLE <table2> AS SELECT * FROM <table1> ``` #### Constraints **Not Null** - can't be written as a table constraint - can't assign explicit name **Check** - can only refer to data in the same row Exclusion - evaluates a comparison of two rows in the table - you can say things like "no two rows overlap", or even "no two rows can be different" **Primary Key** - defaults to `"<table>_pkey"` if not named - is equivalent to UNIQUE + NOT NULL - multiple columns -> "composite primary key" - every table has 0 or 1 PKs **Unique** - allows multiple NULLs - a uniqueness restriction covering only some rows cannot be written as a unique constraint, but it's possible to enforce such a restriction by creating a unique partial index **Foreign Keys — Referential Integrity** - value must exist in target table before using in foreign key column - cannot delete row in target table as long as foreign keys reference it unless `ON DELETE CASCADE` - normally, a composite key is exempt from this constraint if any columns are `NULL` - add `MATCH FULL` to tighten that up so a row is only exempt if *all* columns are `NULL` > [!NOTE] UUIDs as Primary Key > - "Exercise caution when considering UUIDs for a surrogate key. Because of their size, they are inefficient compared with options such as bigint." > - "If you have a B-tree index you'll see that a random value interacts really badly with how the index gets structured." > - UUIDv7 will improve the situation — coming in PG v17. Also not yet in Python stdlib, but can use package [uuid7](https://pypi.org/project/uuid7/). > - https://pganalyze.com/blog/5mins-postgres-uuid-vs-serial-primary-keys ## ALTER ```sql ALTER TABLE <table> RENAME TO <new_name> ADD COLUMN <column> <type> ...constraints... -- same options as column definitions in CREATE ALTER COLUMN <column> SET DATA TYPE <type> ALTER COLUMN <column> SET DEFAULT <exp> ALTER COLUMN <column> DROP DEFAULT ALTER COLUMN <column> SET NOT NULL ALTER COLUMN <column> DROP NOT NULL RENAME COLUMN <column> TO <new_name> DROP COLUMN <column> [CASCADE] -- CASCADE drops FKs referencing column ADD CONSTRAINT <constraint> CHECK ( <exp> ) ADD CONSTRAINT <constraint> PRIMARY KEY ( <columnA>, ... ) ADD CONSTRAINT <constraint> UNIQUE ( <columnA>, ... ) RENAME CONSTRAINT <constraint> TO <new_name> DROP CONSTRAINT <constraint> [CASCADE] -- CASCADE neededto drop constraints with dependencies ``` > [!NOTE] Adding a Column > Since PostgreSQL 11, adding a column with a constant default value no longer means that each row of the table needs to be updated when the `ALTER TABLE` statement is executed. Instead, the default value will be returned the next time the row is accessed and applied when the table is rewritten, making the `ALTER TABLE` very fast even on large tables. > > However, if the default value is volatile (e.g., `clock_timestamp()`) each row will need to be updated with the value calculated at the time `ALTER TABLE` is executed. > >To avoid a potentially lengthy update operation, particularly if you intend to fill the column with mostly non-default values anyway, it may be preferable to add the column with no default, insert the correct values using `UPDATE`, and then add any desired default as described below. ## DROP - Almost all `DROP` commands in PostgreSQL support specifying `CASCADE`. - A `DROP` command can list multiple objects. ```sql DROP DATABASE [IF EXISTS] <database1>, ... DROP TABLE [IF EXISTS] <table1>, ... ``` ## Indexes PRIMARY KEY and UNIQUE create indexes on their columns. **Types** - B-tree (default) — useful for ordering, searching using comparison and range operators, and LIKE (except when starting with wildcard) - GIN — Generalized Inverted Index - GiST — Generalized Search Tree — good for geometry ```sql CREATE [UNIQUE] INDEX [<index>] ON <table> [USING btree|hash|gist|spgist|gin|brin] ( <column1> [ASC|DESC] [NULLS {FIRST|LAST}] -- default is FIRST if DESC specified, else LAST [, ...] ) NULLS NOT DISTINCT -- for unique indexes; default is DISTINCT ALTER INDEX <index> RENAME TO <new_name> DROP INDEX <index> ``` **Suggestions** - consider indexes for columns used in JOINs, WHERE, and ORDER BY - always use EXPLAIN to check your assumptions and measure results ## Constraints > [!NOTE] Name Uniqueness > Constraint names are unique only within the object on which they are defined. However, the index that implements the constraint has to have the same name as the constraint, and indexes share a namespace with tables, views, sequences and composite types. ## Generated Columns A column generated from other columns, like a view is generated from other tables. > [!NOTE] Stored vs Virtual > PostgreSQL only supports "stored" generated columns, which are computed when the source column vales are inserted or updated. (As opposed to "virtual" columns which are computed when queried.) ```sql CREATE TABLE people ( height_cm numeric, height_in numeric GENERATED ALWAYS AS ( height_cm / 2.54 ) STORED ) ``` **Restrictions** - can't use volatile functions, like random() or now(); only immutable functions - cannot use subqueries or reference anything other than the current row - cannot reference another generated column - cannot reference a system column (except tableoid) - cannot have a default value or identity definition (autoincrement) - cannot be part of a partition key - cannot be referenced in BEFORE triggers (generated columns are updated after BEFORE triggers are run)