PostgreSQL bigserial & nextval
I have a PgSQL 9.4.3 server setup and previously I only used the public schema and for example created a table like this:
CREATE TABLE ma_accessed_by_members_tracking (
reference bigserial NOT NULL,
ma_reference bigint NOT NULL,
membership_reference bigint NOT NULL,
date_accessed timestamp without time zone,
points_awarded bigint NOT NULL
);
Using the Windows PgAdmin III program, I can see that it has generated the correct information and sequence.
However, I recently added another schema called "test" to the same database and created the same table as before.
However, this time I see:
CREATE TABLE test.ma_accessed_by_members_tracking
(
reference bigint NOT NULL DEFAULT nextval('ma_accessed_by_members_tracking_reference_seq'::regclass),
ma_reference bigint NOT NULL,
membership_reference bigint NOT NULL,
date_accessed timestamp without time zone,
points_awarded bigint NOT NULL
);
My question / curiosity is why is it public
reference
displayed in the diagram bigserial
, but is test
reference
displayed bigint
with in the diagram nextval
?
Both work as expected. I just don't understand why the difference in schema shows different table creation. I understand that bigint and bigserial allow the same scope of ints.
source to share
Simple, innovative convenience
According to the documentation for serial types smallserial
, serial
and bigserial
are not true data types. Rather, they represent an entry for creating both a sequence and a column with a default that points to that sequence.
I created a test table according to the schema public
. The command psql \d
shows the type of the column bigint
. Maybe this is the behavior of PgAdmin?
Update
I checked the PgAdmin source code. In a function, pgColumn::GetDefinition()
it scans the table pg_depend
for automatic dependency, and when it is found, it replaces it bigint
with bigserial
to mimic the source code for creating the table.
source to share
When creating a serial column in the standard way:
CREATE TABLE new_table (
new_id serial);
Postgres creates a sequence with commands:
CREATE SEQUENCE new_table_new_id_seq ...
ALTER SEQUENCE new_table_new_id_seq OWNED BY new_table.new_id;
From the documentation : The OWNED BY option causes the sequence to bind to a specific table column, so if that column (or its entire table) is dropped, the sequence is automatically dropped as well.
The default sequence name is built from the table name, column name, and suffix _seq
.
If a sequential column was created this way, PgAdmin shows its type as serial
.
If the sequence has a non-standard name or is not associated with a column, PgAdmin shows nextval()
as the default.
source to share