Columns

What is a Column Data Type?

A column data type is a limitation of manner in which a data piece can be stored in a given column.

For example a column with integer data type can store whole numbers (e.g. 42) but not floating point numbers (e.g. 42.42) nor words (e.g. forty two).

Built-In Data Types

There are tens of built-in data types in Postgres.

The most widely used data types are:

• integer - a 16-bit signed integer,

• double precision float,

• varchar (aka text) - variable-length strings,

• boolean,

• date,

• time,

• timestamp,

• uuid.

Other commonly used data types are

• smallint - a 8-bit signed integer,

• bigint - a 64-bit signed integer,

• numeric - a selectable precision decimal,

• real - a single precision float,

• char - a fixed-length string,

• json,

• jsonb - decomposed JSON

• xml.

There are also datatypes dedicated to storing byte data.

Custom Data Types

Postgres allows for creating new custom data types.

serial & bigserial

serial and bigserial are not actual Postgres data types but a shorthand for creating a column with auto-incrementing integer (serial) or bigint (bigserial) data types with NOT NULL constraint applied.

serial & bigserial do not apply UNIQUE, nor PRIMARY KEY constraints automatically.

Using serial & bigserial is not the only way of applying auto-incrementation. Auto-incrementation can also be applied manually using DEFAULT nextval('tablename_colname_seq').

What is a Column Constraint?

A column constraint is a limitation of acceptable data values that can be stored in the column cells.

A column can be constrained with more than one constraint at the same time.

Built-in Column Constrains

PostgreSQL features the following built-in column constraints:

• check,

• primary key,

• foreign key,

• not null, and

• unique.

Check Constraint

The check constraint is a limitation of acceptable data values that can be stored in the column cells through verifying whether a value - before it is stored - satisfies a provided boolean expression.

For example, to ensure that a user age is always positive the CHECK (age > 0) constraint can be used.

CREATE TABLE users (
  age integer CHECK (age > 0)
);

Primary Key Constraint

The primary key constraint is a limitation of acceptable data values that can be stored in column cells through allowing:

• only unique values, and

• only not null values.

A given table can have only one primary key.

CREATE TABLE users (
  id serial PRIMARY KEY
);

A primary key constraint can constrain multiple columns at the same time creating a multi‑column primary key.

Foreign Key Constraint

The foreign key constraint is a limitation of acceptable data values that can be stored in column cells through allowing only values that have their counterparts as primary keys of other tables to which they reference. This ensures a so called referential integrity.

CREATE TABLE orders (
  user_id integer REFERENCES users (id)
);

The orders table is the referencing table and the users table is the referenced table.

The referenced column name (in the above case (id)) can be omitted if the id column is the primary key of the referenced table.

A given table can have many foreign keys.

A foreign key constraint can constrain multiple columns at the same time if it references a multi‑column primary key.

It is allowed for a table to reference itself.

Not Null Constraint

The not null constraint is a limitation of acceptable data values that can be stored in column cells through allowing only not null values.

CREATE TABLE users (
  email VARCHAR (256) NOT NULL
);

Unique Constraint

The unique constraint is a limitation of acceptable data values that can be stored in column cells through allowing only unique values.

CREATE TABLE users (
  token VARCHAR (256) UNIQUE
);

A unique constraint automatically applies a B-tree index on the relevant column or columns.

Table Constraints

It is possible to apply a constraint to more than one column using a table constraint.

A table constraint is a constraint applied on a table as a whole instead of a specific one column.

For example, it is possible apply a unique constraint to two columns to ensure that no two same combinations of values are present in the table. However, such a two-column unique constraint does not disallow from duplicated values in each column separately as only the combinations have to be unique. Further, combinations with null values in any of the columns count as unique.

CREATE TABLE users (
  first_name VARCHAR (256),
  last_name VARCHAR (256),
  UNIQUE (first_name, last_name)
);

A table constraint can compare values from two columns before allowing them to be saved.

CREATE TABLE users (
  price numeric,
  discount numeric,
  CHECK (price > discount)
);

A multi-column check constraint is satisfied if its boolean expression evaluates to true or null. Therefore, caution is advised as some expressions evaluate to null when any of their operands are null.

Naming Constraints

For easier referencing it is possible to name constraints.

Both, column and table, constraints can be named.

If no constraint name is provided Postgres chooses one automatically.

Adding Constraints to Existing Tables & Columns

It is possible add both table and column constraints associated with already existing columns using the ALTER TABLE and ALTER COLUMN commands.

ALTER TABLE users ADD UNIQUE (first_name, last_name);
ALTER TABLE users ALTER COLUMN first_name SET NOT NULL;

Dropping Constraints

To drop a constraint - other than a not null constraint which does not have a name - it is required to know its name. The constraint name could have been granted to it explicitly or automatically.

ALTER TABLE users DROP CONSTRAINT users_email_key;
ALTER TABLE users ALTER COLUMN first_name DROP NOT NULL;

To get constraint names for a given table use the \d internal command appended with the table name.

\d users

Listing Columns with psql

To list columns in a given table with psql use the \d+ internal command appended with the table name.

=> \d+ users

Listing Columns with SQL

To list columns in a given table with SQL select all rows from information_schema.columns where table_name equals the contemplated table name.

SELECT *
FROM information_schema.columns
WHERE table_name = 'users';