PostgreSQL Integer Types

Integer types store integers, and PostgreSQL provides three integer data types: SMALLINT, INTEGER, and BIGINT.

The following table shows the number of bytes occupied and value ranges by different integer types:

Note that BIGINT types take up a lot of space and can degrade database performance.

Usages

To define an integer type column, use the following syntax:

column_name {SMALLINT | INTEGER | BIGINT} column_constrait

Note that INT is a synonym for INTEGER, you can use INT instead INTEGER.

To define a primary key column of integer type, use the following syntax:

column_name {SMALLINT | INTEGER | BIGINT} GENERATED ALWAYS AS IDENTITY PRIMARY KEY

To define an identity column of type integer, use the following syntax:

column_name {SMALLINT | INTEGER | BIGINT} GENERATED ALWAYS AS IDENTITY

To define a SERIAL column, use the following syntax:

column_name SERIAL PRIMARY KEY

Note that the internal type of SMALLSERIAL is SMALLINT, the internal type of SERIAL is INTEGER, and the internal type of BIGSERIAL is BIGINT.

Examples

SMALLINT Type Examples

First, create a test table with the following statement:

CREATE TABLE test_smallint(
  name TEXT NOT NULL,
  age SMALLINT NOT NULL
);

Insert two rows of data using the following statement:

INSERT INTO test_smallint (name, age)
VALUES ('Tom', '23'), ('Lucy', 20)
RETURNING *;

 name | age
------+-----
 Tom  |  23
 Lucy |  20
(2 rows)

Note that although we pass in a character value '23' for the integer column, PostgreSQL automatically converts it to a number and stores it in the database.

Use INTEGER column as primary key

In general, the identity columns of the INTEGER type can be used as the primary key column:

CREATE TABLE test_int_pk(
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  age SMALLINT NOT NULL
);

Here, the id column is the primary key column. Its type is INTEGER and uses the GENERATED ALWAYS AS IDENTITY property.

Use the following statement to insert the two rows into the table:

INSERT INTO test_int_pk (name, age)
VALUES ('Tom', '23'), ('Lucy', 20)
RETURNING *;

 id | name | age
----+------+-----
  1 | Tom  |  23
  2 | Lucy |  20
(2 rows)

Conclusion

PostgreSQL provides three integer data types: SMALLINT, INTEGER, and BIGINT. You can choose different types according to your different storage requirements to improve the efficiency of access.