Current value of an "auto increment" field in Postgresql

Question

I would like to know, what are the ways of obtaining the current position of the value "auto increment" of columns of a table in the Postgresql.

Answer 1

The field type "Auto Increment" of Postgres is called SERIAL field INT and BIGSERIAL field BIGINT.

The command to create this column type is:

ALTER TABLE tabela ADD coluna SERIAL NOT NULL;

When creating this type of column, the database creates an internal SEQUENCE and sets the column default value as the next value of that SEQUENCE. (more information about sequences at this link)

The name of SEQUENCE created for fields of the type SERIAL follows the format:

table + '_' + spine + '_seq'

To get the current value of the column, you need to know the name of the SEQUENCE corresponding, and then use the command CURRVAL:

SELECT CURRVAL('nome_da_sequence');

So, the guy SERIAL is nothing more than a "shortcut" to the following command:

-- Criar uma coluna
ALTER TABLE tabela ADD COLUMN coluna INT NOT NULL;
-- Cria uma sequence que depende da coluna (OWNED BY), 
-- ou seja, quando a coluna for excluída, a sequence também será
CREATE SEQUENCE tabela_coluna_seq OWNED BY tabela.coluna;
-- Configurar o próximo valor da sequence (NEXTVAL) como o valor padrão da coluna
ALTER TABLE tabela ALTER COLUMN coluna SET DEFAULT NEXVAL('tabela_coluna_seq');

It is also possible to obtain the SEQUENCE field-type SERIAL using the function pg_get_serial_sequence. Would look like this:

SELECT CURRVAL(pg_get_serial_sequence('tabela', 'coluna'))

Answer 2

In the PostgreSQL an "auto increment is a field with data type serial or bigserial.

A guy serial is nothing more than a whole with a sequence associated.

To manipulate sequences you have the following functions: currval(regclass), lastval(), nextval(regclass), setval(regclass, bigint) and setval(regclass, bigint, boolean).

For more details see documentation.