SQL - create and alter tables

Close Up Of Hand Over White Background 316465

In this blog post, we introduce the creation and alteration of tables along with auxiliary information like data types and constraints.

Table of content:
Data types
$\mapsto$ Numeric
$\mapsto$ $\mapsto$ Integer
$\mapsto$ $\mapsto$ Floating-point
$\mapsto$ Character
$\mapsto$ Boolean
$\mapsto$ Date/Time
$\mapsto$ Array
Constraints
$\mapsto$ Check
$\mapsto$ Unique
$\mapsto$ Not-Null
$\mapsto$ Primary Key
$\mapsto$ Foreign Key
$\mapsto$ Exclusion
Create a table
$\mapsto$ Insert data into a table
Alter a table
$\mapsto$ Rename
$\mapsto$ Column properties
$\mapsto$ Constraints
Drop a table

Data types

Numeric

Integer types

SMALLINT: (2 bytes) can store integer values in the range [ $-2^{15}$ , $2^{15}-1$ ].

INT / INTEGER: (4 bytes) can store integer values in the range [ $-2^{31}$ , $2^{31}-1$ ].

BIGINT: (8 bytes) can store integer values in the range [ $-2^{63}$ , $2^{63}-1$ ].

SMALLSERIAL / SERIAL / BIGSERIAL: (2 / 4 / 8 bytes) autoincrementing integer types. The system automatically populates values for the columns of these types. Users shouldn’t specify the values of these columns by themselves when adding data since that act can result in duplicated values.

Floating-point types

REAL: (4 bytes) basic floating-point type.

NUMERIC(p, s) / DECIMAL(p, s): p – precision – is the total number of digits and s – scale – is the number of digits on the right of the decimal point. These types are recommended when exact calculations are required for a column.

Character

CHAR(n): fixed-length string type. The system pads spaces if the input string is shorter then n characters.

VARCHAR(n): string type of length at most n.

TEXT: string type of any length.

Boolean

BOOL / BOOLEAN: a true-false type. We can input any of (‘1’, ‘t’, ‘y’, ‘true’, ‘yes’) as a True, and any of (‘0’, ‘f’, ‘n’, ‘false’, ‘no’) as a False.

Date/Time

DATE: store date.

TIME: store time.

TIMESTAMP: store both date and time.

TIMESTAMPZ: store date, time and the time-zone.

INTERVAL: store a period of time.

Array

We can also store an array of any other data types. For examples, a variable-size integer array: INT[], a fixed 3×3 date array: DATE[3][3]. When inputting array values, use '{}' to indicate an array, for example, '{{1, 2, 3}, {1, 3, 5}, {1, 5, 9}}'.

Other less common data types

These include network types, XML, JSON, geometric type, composite, etc.

Constraints

Making constraints is a way of ensuring the data inputted to our database follows our expectations. For example, when storing information about products, we need their prices to be non-negative. However, there are no data types that only accept non-negative values, thus, we have to make a constraint, stating that $price \geq 0$ . Whenever new data is inputted, or an existing row is modified, the constraints we made are checked, then the system will throw an error if any of them is violated.

A constraint may be stated together with the declaration of the column (this is called a column constraint) or separately (called a table constraint).

Check

This constraint is fairly frequently used. It helps us ensure a boolean expression is true. For example, we want $price \geq 0$ is true.

A column check-constraint:

CREATE TABLE Product (
	id SERIAL,
	price DECIMAL(12, 2) CHECK (price >= 0)
);

A table check-constraint:

CREATE TABLE Product (
	id SERIAL,
	price DECIMAL(12, 2),
	CHECK (price >= 0)
);

We are also allowed to give a name to a constraint, so that in the future when the constraint is violated and an error notification is shown, we can better recognize which constraint caused the problem.

A named column constraint:

CREATE TABLE Product (
	id SERIAL,
	price DECIMAL(12, 2) CONSTRAINT reasonable_price CHECK (price >= 0)
);

A named table constraint:

CREATE TABLE Product (
	id SERIAL,
	price DECIMAL(12, 2),
	CONSTRAINT reasonable_price CHECK (price >= 0)
);

Unique

This constraint makes sure that no 2 rows have the same data value in the specified column.

CREATE TABLE Product (
	id INT UNIQUE,
	price DECIMAL(12, 2)
);

You may also ensure a set of columns is unique.

CREATE TABLE Product (
	id SERIAL,
	model_id VARCHAR(20),
	manufacturer VARCHAR(30),
	price DECIMAL(12, 2),
	CONSTRAINT unique_model UNIQUE (model_id, manufacturer)
);

Not-Null

By default, all columns permit values to be NULL. We can change this option by stating that some columns are not NULL-tolerant.

CREATE TABLE Product (
	id INT NOT NULL,
	price DECIMAL(12, 2)
);

If we write id INT NULL, this means the column id is NULL -tolerant, which is equivalent to the default id INT.

Primary key

The primary key is a column (or a set of columns) that can be used as a unique identifier for every row in the table. The primary key must be both unique and not-null. For a table, there is at most 1 primary key.

The primary key as a column:

CREATE TABLE Product (
	id SERIAL PRIMARY KEY,
	model_id VARCHAR(20),
	manufacturer VARCHAR(30),
	price DECIMAL(12, 2)
);

The primary key as a set of columns:

CREATE TABLE Product (
	id SERIAL,
	model_id VARCHAR(20),
	manufacturer VARCHAR(30),
	price DECIMAL(12, 2),
	PRIMARY KEY (model_id, manufacturer)
);

Foreign key

A Foreign key constraint restricts the values in a column that these values must exist in a column of another table. For example, suppose we have 2 tables: the Product table and the Purchase table. For a purchase, the product being bought must exist in the Product table.

CREATE TABLE Product (
	id INT PRIMARY KEY,
	price DECIMAL(12, 2)
);

CREATE TABLE Purchase (
	purchase_id INT PRIMARY KEY,
	product_id INT REFERENCES Product (id),
	quantity INT
);

The REFERENCES indicates a foreign key, which says that the values of Purchase.product_id must exist in Product.id. If you insert or modify a row that does not satisfy this restriction, an error will be thrown.

The Foreign key constraint also works for a set of columns. Moreover, a table may have more than one foreign key that refers to more than one table. However, each referenced column must be either the primary key or being marked as unique in their tables.

By default, we are not allowed to delete the referenced value if there are some references that point to them. Instead, we have to delete all the referencing values before being allowed to delete the referenced values.

Exclusion

The Exclusion constraint is kind of a generalization of the Unique constraint. While Unique means we can not afford 2 rows with equal value, Exclusion constraint allows us to modify the operator, instead of just equal, it can be anything, from unequal, a mix of equal and unequal to non-overlapping, etc.

-- Install the btree_gist extension
CREATE EXTENSION btree_gist;

CREATE TABLE Purchase (
	purchase_id INT PRIMARY KEY,
	product_id INT REFERENCES Product (id),
	quantity INT,
	customer_id INT,
	EXCLUDE USING gist (customer_id WITH =, product_id WITH <>)
);

In the code above, we set up a constraint that no two rows have the same customer_id and different product_id, i.e. a customer is eligible to buy only 1 type of product. When we add or modify a row, the system compares it with all other existing rows, if for any row all the expressions held TRUE, it will throw an error saying that the Exclusion constraint is violated (in this example, we have 2 expressions, customer_id WITH = and product_id WITH <>).

Create a table

A detailed explanation of all possible options for creating tables in Postgresql is given in this doc. Here, we introduce a simplified version, which shows the essential and popular syntax.

CREATE [ {TEMP | TEMPORARY} | UNLOGGED ] TABLE [ IF NOT EXISTS] [schema_name.]table_name ( [
{
column_name data_type [ COLLATE collation ] [ column_constraint ] [DEFAULT default_value]
| table_constraint
| LIKE source_table [ like_option … ]
}
[, … ]
] )
[ INHERITS (parent_table [, … ]) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

This command creates a table. If schema_name is specified, the table will be created in that schema, if not, it is created in the currently-active schema.

TEMP or TEMPORARY: If specified, the table being created is temporary, i.e. it will be deleted at the end of a session (by default) or at the end of the current transaction (using ON COMMIT).

UNLOGGED: an unlogged table does not write data to the write-ahead log, which makes it perform faster than normal tables. However, it is vulnerable to an unexpected crash or shutdown.

IF NOT EXISTS: If a table with the same table_name already existed, a notice is issued instead of an error.

COLLATE: it is optional to assign a collation to the column.

column_constraint and table_constraint: see Constraints.

DEFAULT: set the default value for a column.

LIKE source_table: copy all column names, data types, and Not-Null constraints from source_table. Note that this is deep-copying, which means the source and the destination are not linked to each other after this creation is completed.

like_option:

INCLUDING DEFAULTS for copying default expressions.
INCLUDING CONSTRAINTS for copying CHECK constraints.
INCLUDING INDEXES for copying indexes, Primary Key, Unique, and Exclude constraints. Note that names for these constraints are chosen according to the default rules, regardless of the original names.
INCLUDING STORAGE for copying storage settings.
INCLUDING COMMENT for copying comments for columns, constraints, and indexes.
INCLUDE ALL for copying all the above.

INHERITS: this specifies a list of tables from that the new table inherits all columns, Not-Null and Check constraints. Note that, unlike LIKE, the parent tables and the child table are bound with each other. Any modifications to the parents will be transferred to the child, furthermore, by default, the data in the child table also appear in scans of the parents.

ON COMMIT: control the behavior of temporary tables.

PRESERVE ROWS: (is the default) no special actions at the end of the transaction.
DELETE ROWS: when the transaction ends, all rows in the table are deleted.
DROP: when the transaction ends, the table is dropped.

Example 1:

CREATE TABLE Product (
	id INT PRIMARY KEY,
	description TEXT UNIQUE,
	price DECIMAL(12, 2) NOT NULL
);

CREATE TABLE Purchase (
	purchase_id INT PRIMARY KEY,
	product_id INT REFERENCES Product (id),
	quantity INT DEFAULT 1,
	customer_id INT NOT NULL,
	EXCLUDE USING gist (customer_id WITH =, product_id WITH <>)
);

We created 2 tables.

The first is Product, which has 3 columns with Primary key, Unique, and Not-Null constraints.

The second is Purchase containing 4 columns with Primary key, Foreign key, Not-Null, and Exclusion constraints. The quantity is 1 by default if no information is provided. The product_id is a foreign key that references Product.id.

Example 2:

CREATE TABLE Worker (
	id SERIAL PRIMARY KEY,
	name TEXT NOT NULL,
	address TEXT NOT NULL,
	date_of_birth DATE NOT NULL,
	role_id INT,
	CHECK (date_of_birth < CURRENT_DATE)
);


CREATE TEMP TABLE Manager (
	manager_id SERIAL PRIMARY KEY,
	responsibility TEXT
)
INHERITS (Worker);

We create 2 tables, Worker and Manager. The Manager inherits all columns and Not-Null and Check constraints from the Worker.

Insert data into a table

To insert a row into a table, we follow the below syntax:

INSERT INTO table_name (column_1, column_2, ...) VALUES (value_column_1, value_column_2, ...);

For inserting multiple rows at once:

INSERT INTO table_name (column_1, column_2, ...) 
       VALUES (value_column_1, value_column_2, ...)
            , (value_column_1, value_column_2, ...) 
            ...
;

If we want to insert row(s) by specifying all values of the columns, we may remove the part that enumerates column names. For example, if the table has exactly 3 columns, we can do:

INSERT INTO table_name 
       VALUES (value_column_1, value_column_2, value_column_3)
            , (value_column_1, value_column_2, value_column_3) 
            ...
;

Alter a table

While the full information can be found here, below is the simple version.

Rename

Rename a table:

ALTER TABLE table_name RENAME TO new_name

Rename a column:

ALTER TABLE table_name
RENAME [ COLUMN ] column_name TO new_name

Alter column properties

ALTER TABLE table_name
ADD [ COLUMN ] column_name data_type [ column_constraint ];

ALTER TABLE table_name
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ];

ALTER TABLE table_name
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type;

ALTER TABLE table_name
ALTER [ COLUMN ] column_name SET DEFAULT expression;

ALTER TABLE table_name
ALTER [ COLUMN ] column_name DROP DEFAULT;

Alter/Set/Drop constraints

ALTER TABLE table_name
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL;

ALTER TABLE table_name
ADD table_constraint;

ALTER TABLE table_name
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ];

(All the commands above are self-explanatory.)

Drop a table

DROP TABLE [ IF EXISTS ] table_name_1, table_name_2, … [ CASCADE | RESTRICT ];

IF EXISTS: show notification instead of an error in case the table does not exist.

CASCADE: Also automatically drop all objects that depend on this table.

RESTRICT: (is the default) throw an error if there are still object(s) that depend on it.

References:

Postgresql’s CREATE TABLE: link
Postgresql’s data types: link
Postgresql’s constraints: link
Postgresql’s alter: link
Q&A about exclusion constraints: link
Session vs Transaction: link
Postgresqltutorial’s create table: link

Tung M Phung's Blog

SQL – create and alter tables