What is a serial data type?
The SERIAL data type stores a sequential integer, of the INT data type, that is automatically assigned by the database server when a new row is inserted. The default serial starting number is 1, but you can assign an initial value, n, when you create or alter the table.
What is a serial column?
SERIAL is an auto-incremented integer column that takes 4 bytes while BIGSERIAL is an auto-incremented bigint column taking 8 bytes. Behind the scenes, PostgreSQL will use a sequence generator to generate the SERIAL column values upon inserting a new ROW.
Is Bigint a long?
The equivalent of Java long in the context of MySQL variables is BigInt. In Java, the long datatype takes 8 bytes while BigInt also takes the same number of bytes.
How to make an integer column work as a serial column?
If you want to make an existing (integer) column to work as a “serial”, just create the sequence by hand (the name is arbitrary), set its current value to the maximum (or bigger) of your current address.new_id value, at set it as default value for your address.new_id column. To set the value of your sequence see here.
Which is data type to reference serial data type?
The first one should have an auto-incrementing ID field that the second one references: CREATE TABLE tableA (id SERIAL NOT NULL PRIMARY KEY.) CREATE TABLE tableB (parent INTEGER NOT NULL REFERENCES tableA (id).) According to documentation, SERIAL acts as unsigned 4-byte integer while INTEGER is signed:
How to change column to serial in SQL?
If you want to make an existing (integer) column to work as a “serial”, just create the sequence by hand (the name is arbitrary), set its current value to the maximum (or bigger) of your current address.new_id value, at set it as default value for your address.new_id column.
How to determine if a column is defined as?
So checking these factors in the catalogs, as you proposed (with the addition of NOT NULL ), are sufficient to identify a serial column. For an actual query to find the (big)serials, see the excellent answer of Erwin Brandstetter. Thanks for contributing an answer to Database Administrators Stack Exchange!