0

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?

  • 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. – Erwin Brandstetter Dec 8 '14 at 8:36
  • @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. – Erdinc Ay Dec 9 '14 at 10:17
  • 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. – Evan Carroll Jan 19 '18 at 1:27
4

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

  • 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). – Erdinc Ay Dec 5 '14 at 15:02
  • 2
    You can use the Sequence Manipulation Functions, probably currval() to get the latest value inserted (in the same session) to a serial column. – ypercubeᵀᴹ Dec 5 '14 at 15:05
  • 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. – Erdinc Ay Dec 5 '14 at 15:18
  • 1
    @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. – ypercubeᵀᴹ Dec 5 '14 at 15:53
  • I will have a look to the Returning Clause – Erdinc Ay Dec 5 '14 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.