- PostgreSQL Tutorial
- Advanced PostgreSQL
As this is a new application, I would like to ask for your advice regarding using serial vs. Bigserial as the id for the meter records. Is there any noticable performance downgrade on current x64 CPUs or such? Storage for the id field should increase by factor 2, right? Any obvious reason why not to use bigserial?
- PostgreSQL Interfaces
Currently I have a primary key column 'id' which is set to serial but I want to change it to bigserial to map to Long in Java layer. What is the best way to achieve this considering this is a existing table? I think my Postgres version is 10.5. Also I am aware that both serial and bigserial are not a data type. Here's the snippet from the PostgreSQL docs: The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column. Bigserial should be used if you anticipate the use of more than 2 31 identifiers over the lifetime of the table.
- PostgreSQL Useful Resources
- Selected Reading
PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases.
If you wish a serial column to have a unique constraint or be a primary key, it must now be specified, just like any other data type.
The type name serial creates an integer columns. The type name bigserial creates a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The type name smallserial creates a smallint column.
Syntax
The basic usage of SERIAL dataype is as follows −
Example
Consider the COMPANY table to be created as follows −
Now, insert the following records into table COMPANY −
This will insert seven tuples into the table COMPANY and COMPANY will have the following records −
Active10 months ago
I did some research but can't find the exact answer that I look for. Currently I have a primary key column 'id' which is set to serial but I want to change it to bigserial to map to Long in Java layer. What is the best way to achieve this considering this is a existing table? I think my Postgres version is 10.5. Also I am aware that both serial and bigserial are not a data type.
user3123690user312369042522 gold badges88 silver badges1919 bronze badges
1 Answer
In Postgres 9.6 or earlier the sequence created by a
serial
column already returns bigint
. You can check this using psql:So you should only alter the type of the serial column:
The behavior has changed in Postgres 10:
Also, sequences created for SERIAL columns now generate positive 32-bit wide values, whereas previous versions generated 64-bit wide values. This has no visible effect if the values are only stored in a column.
Postgresql Serial Column
Hence in Postgres 10+:
Postgresql Serial Vs Bigserial 4
klinklin66.5k77 gold badges7171 silver badges111111 bronze badges