POSTGRESQL NUMERIC DATA TYPES PART 1: INTEGER TYPES AND SERIAL TYPES

By: Saurav

2020-07-30 23:01:00 UTC

PostgreSQL has very rich set of data types available for different use cases. Subtypes are grouped and each group needs to be explained thoroughly and practiced again and again. In this blog post, I will try and explain two of these groups in the numeric type.

The numeric data type has the following groups:
1. Integer Types
2. Variable Precision Types
3. Floating Types
4. Serial Types

In this blog, we will take a look into integer and serial types.

Integer Types


These are whole numbers without fractions of various ranges and use cases.
If we try to store a number outside the range, it throws an error.

1. Small int, also aliased as int2


This is small range integer with range -32768 to +32767. It has 2 byte storage size which is a total of 16 bit. To understand the storage, let's convert 32767 in to binary:

32767 = 111111111111111
1 bit is dedicated to storing negative 2's complement totaling a total of 16 bit. 
Thus the range -32768 to +32767

To understand it more, let's create a table and add the data types below:

-- create table employees
create table employees (
  id smallint
}

insert into employees
values (32767);

If we try storing out of range values like the following, it will throw an error.

insert into employees
values (32769);

ERROR: smallint out of range


insert into employees
values (-32780);

ERROR: smallint out of range

If we try storing decimal points, it will only store the whole number part. For example

insert into employees
values (32767.1);

This will store 32767 and ignore the decimal

Use Case: The smallint type is used only if disk space is scarce.

2. Integer, also aliased as int4 and int


This is a common choice with range -2147483648 to +2147483647. It has 4 byte storage size which is a total of 32 bit. It offers the best of range, storage size, and performance to store a whole number

To understand the storage, let's convert 2147483647 into binary:

2147483647 = 1111111111111111111111111111111
1 bit is dedicated to storing negative 2's complement totaling a total of 32 bit. 
Thus the range -2147483648 to +2147483647

-- create table employees
create table employees (
  id integer
}

insert into employees
values (2147483649);

throws ERROR: integer out of range

Reason being 2147483649 = 10000000000000000000000000000001 in binary which is 32 bit but one bit is dedicated to 2's complement. Therefore this is out of range.

3. Big Integer, also aliased as int8


This is used when the range of the integer type is insufficient. The range is -9223372036854775808 to +9223372036854775807. It has 8 byte storage size which is a total of 64 bit. Although it offers more range of storage, it comparatively takes more resources on computation.

To understand the storage, we can convert 9223372036854775807 into binary:

9223372036854775807 = 111111111111111111111111111111111111111111111111111111111111111
The same logic applies here for storage. 1 bit is for 2s complement and hence 63 bit positive number is the max it can support. This number is 9223372036854775807

-- create table employees
create table employees (
  id bigint
}

The second group I would like to talk about is

Serial Types

These are not exactly types per se but more of a PSQL keyword for convenience while creating unique identifier columns. You can think them of integer columns which are on AUTO_INCREMENT

There are thee types in this group: smallserial, serial and bigserial

create table employees (
  ss_id smallserial PRIMARY KEY,
  s_id serial,
  bs_id bigserial
);

This creates an integer column and arranges for default values to be assigned from a sequence generator.
Normally, we use PRIMARY KEY constraints as shown above so that the same value doesn't get duplicated.

We normally use this data type for a column that we want to do auto-increment on. A lot of frameworks apply a constraint on integer for us but knowing PSQL provides this feature saves you from doing a mistake while building an application on top of it. Let's look at these in a little bit more detail

1. Small Serial or serial2


The range here is 1 to 32767 since the storage space available is 2 bytes (16 bits). Creates smallint column

2. Serial or serial4


The range here is 1 to 2147483647 since the storage space available is 4 bytes (32 bits). Creates integer column

3. Big Serial or serial8


The range here is 1 to 1 to 9223372036854775807 since the storage space available is 8 bytes (64 bits). Creates bigint column

Note: There are few important points to remember when using serial data types


  • While inserting values in the table, we exclude this column and auto increment takes care of incrementing it

  • A value allocated from the sequence is "used up" even if a row containing that value is never successfully gets inserted. For example when an error happens

Let's see the following examples to understand

create table employees (
  name varchar,
  bs_id serial
);

-- When inserting, no need to insert the bs_id column since its auto incremented from 1
insert into employees values ('sasha');

let's see what it returns:
select * from employees;  gives -> sasha,1





Reference is available here: PostgreSQL Reference

Try them and let me know how it works out for you :)
Twitter

Owned & Maintained by Saurav Prakash

If you like what you see, you can help me cover server costs or buy me a cup of coffee though donation :)