0

I have a table with a SERIAL ID as primary key. As you know the serial id increments itself automatically, and I need this feature in my table.

ID | info
---------
1  | xxx
2  | xxx
3  | xxx

For ordering matters, I want to insert a row between 1 and 2. Thus give to the new row an ID equal to 2, and want the other ID's to automatically increment to 3,4. If I execute such a query I get a duplicate key error.

Is there a way to make it possible, maybe changing the SERIAL ID to some other type?

Houari
  • 5,326
  • 3
  • 31
  • 54
buster
  • 167
  • 1
  • 5
  • 11

1 Answers1

2

What you are describing is not what most people would consider an ID, which should be a permanent and arbitrary identifier, for which an auto-increment column is just a convenient way of creating unique values. You couldn't use a value that kept changing as a foreign key, for example, so might well want both columns.

However, the task you've described is easily achieved with just an ordinary Integer column, let's call it "position", since that seems a more logical label for this behaviour.

The algorithm is simple:

  1. Make a space for the new value by shifting all existing elements up one place.
  2. Insert your new element.

In SQL, that would look something like this, to insert at position 42:

UPDATE items SET position=position + 1 WHERE position >= 42;
INSERT INTO items ( position, name ) VALUES ( 42, 'Answer' );

You could wrap this up in an SQL function on the server, and wrap it in a transaction to prevent concurrent inserts messing each other up.

Note that by default, a PRIMARY KEY or UNIQUE constraint on the position column may be invalidated during the update, as changes to each row are validated separately. To get around this, you can use a "deferrable constraint"; even in "immediate" mode, this will only be checked at the end of the statement, so the update will not violate it.

CONSTRAINT uq_position UNIQUE (position) DEFERRABLE INITIALLY IMMEDIATE

Note also that a Serial column doesn't have to be unique, so you could still have the default value be an auto-increment. However, it won't notice you inserting extra values, so you need to reset the sequence after a manual insert:

SELECT setval(
     pg_get_serial_sequence('items', 'position'),
     ( SELECT max(position) FROM items )
);

Here is a live demo putting it all together. (SQLFiddle seems to have a bug which isn't dropping/resetting the sequence, making the id values look rather odd.)

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • @Houari How? Every item is being moved up one place, how could they become duplicates unless they already were? – IMSoP May 21 '15 at 20:25
  • I understand now how stupid my question was and how simple the solution to the problem is. Thank you. – buster May 21 '15 at 20:28
  • 1
    @Houari Hm, fair enough, I forgot that the constraint would be checked for each row, not each statement. You'd have to set any UNIQUE constraint to be DEFERRABLE. I'll edit into my answer. – IMSoP May 21 '15 at 20:38
  • @Houari It is in fact enough to mark the constraint DEFERRABLE, but leave it as IMMEDIATE, because this is enough for it to be checked once per statement, rather than once per row: http://www.sqlfiddle.com/#!15/0f273/4 – IMSoP May 21 '15 at 20:48