PostgreSQL bigserial & nextval

Question

I've got a PgSQL 9.4.3 server setup and previously I was only using the public schema and for example I 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 Program PgAdmin III I can see it created the proper information and sequence.

However I've recently added another schema called "test" to the same database and created the exact same table, just like 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 in a public schema the reference shows bigserial but in the test schema reference shows bigint with a nextval?

Both work as expected. I just do not understand why the difference in schema's would show different table creations. I realize that bigint and bigserial allow the same volume of ints to be used.

Tomasz Myrta · Accepted Answer · 2015-06-29T05:56:31.773

9

Merely A Notational Convenience

According to the documentation on Serial Types, smallserial, serial, and bigserial are not true data types. Rather, they are a notation to create at once both sequence and column with default value pointing to that sequence.

I created test table on schema public. The command psql \d shows bigint column type. Maybe it's PgAdmin behavior ?

Update

I checked PgAdmin source code. In function pgColumn::GetDefinition() it scans table pg_depend for auto dependency and when found it - replaces bigint with bigserial to simulate original table create code.

edited Jun 29 '15 at 05:56

answered Jun 24 '15 at 20:58

Tomasz Myrta

1,114
8
10

2

This post does not even try to answer the question. – Adam Piotrowski Jun 25 '15 at 14:02
1

I checked PgAdmin source code. In function pgColumn::GetDefinition() it scans table pg_depend for auto dependency and when found it - replaces bigint with bigserial to simulate original table create code. – Tomasz Myrta Jun 25 '15 at 18:44
@TomaszMyrta I suggest you work your comment about source code into your Answer. That will more fully and explicitly address the Question. – Basil Bourque Jun 29 '15 at 03:35

klin · Answer 2 · 2015-06-25T00:35:53.517

When you create 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 documentation: The OWNED BY option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well.

Standard name of a sequence is built from table name, column name and suffix _seq.

If a serial column was created in such a way, PgAdmin shows its type as serial.

If a sequence has non-standard name or is not associated with a column, PgAdmin shows nextval() as default value.

PostgreSQL bigserial & nextval

2 Answers2

Merely A Notational Convenience

Update

Linked