PostgreSQL - retrieving max primary key column to insert a new row - any alternatives?

Question

We are using a software that needs to request the max primary key column value to add a new row into the table that was requested.

I know that there are some alternatives like:

• Sequences, and
• GUIDs

in PostgreSQL, but is there something similiar like:

• Identity-type in SQL Server or
• any auto-increment columns of any other DB (MySQL)

in PostgreSQL, is there a way to achieve this with a (stored) procedure or a trigger and how?

Answer 1

Postgres has the serial datatype which matches SQL Server's IDENTITY or MySQL's AUTO_INCREMENT.

Internally it is shorthand for a SEQUENCE but does that matter? It acts like IDENTITY/AUTO_INCREMENT:

The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Edit,

I think what OP means is "is there SCOPE_IDENTITY or such" in PostgreSQL. Yes. You'd need currval or another one