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.
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.
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.
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
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
Note: There are few important points to remember when using serial data types
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
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 :)