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?

• Erwin Brandstetter: Your question is too vague. A [serial](www.postgresql.org/docs/current/interactive/datatype-numeric.html) would be the obvious answer, like gbn provided. But it seems you might be trying to avoid gaps in your serial numbers? That's not always a good idea. Also, do you have concurrent write access? Clarify your objective, edit the question.
• Erdinc Ay: @Erwin, you are right, I need a better question, but i think people are confused when I modify this, I will give a new try with a new question. And yes I need conurrent write access, and the issue is this is for over half a million users. I just want a way to retrieve the last inserted column ID.
• Evan Carroll: I'm not sure what you're asking, but without table locking it's not even possible afaik. Use sequences is the right answer. I'm not sure why that's an "alternative" or not acceptable.

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

• Erdinc Ay: hello thanks for the answers, but we have a legacy system and the issue is that we need to retrieve the last inserted column id, which we currently get with a select(max)-query (not very performant and synchronous).
• ypercubeᵀᴹ: You can use the Sequence Manipulation Functions, probably currval() to get the latest value inserted (in the same session) to a serial column.
• Erdinc Ay: Yes the idea is good but the legacy system only uses one session to access the db, so the currval() value wont help when multiple users use it.
• ypercubeᵀᴹ: @ErdincAy You can still use currval or RETURNING clause. you just have to do it during the insert, and keep the value for each user in your application for when they need it.
• Erdinc Ay: I will have a look to the Returning Clause