Manual inserts on a postgres table with a primary key sequence

I'm con开发者_StackOverflow中文版verting a MySQL table to PostgreSQL for the first time in my life and running into the traditional newbie problem of having no auto_increment.

Now I've found out that the postgres solution is to use a sequence and then request the nextval() of this sequence as the default value every time you insert. I've also read that the SERIAL type creates a sequence and a primary key automatically, and that nextval() increments the counter even when called inside transactions to avoid locking the sequence.

What I can't find addressed is the issue of what happens when you manually insert values into a field with a UNIQUE or PRIMARY constraint and a nextval() of a sequence as default. As far as I can see, this causes the INSERT to fail when the sequence reaches that value.

Is there a simple (or common) way to fix this ?

A clear explanation would be very much appreciated.

Update: If you feel I shouldn't do this, will never be able to fix this or am making some flawed assumptions, please feel free to point them out in your answers. Above all, please tell me what to do instead to offer programmers a stable and robust database that can't be corrupted with a simple insert (preferably without hiding everything behind stored procedures)

---

If you're migrating your data then I would drop the sequence constraint on the column, perform all of your inserts, use setval() to set the sequence to the maximum value of your data and then reinstate your column sequence nextval() default.

---

You can create a trigger which will check if currval('id_sequence_name')>=NEW.id.

If your transaction did not use default value or nextval('id_sequence_name'), then a currval function will throw an error, as it works only when sequence was updated in current session. If you use nextval and then try to insert bigger primary key then it will throw another error. A transaction will be then aborted.

This would prevent inserting any bad primary keys which would break serial.

Example code:

create table test (id serial primary key, value text);

create or replace function test_id_check() returns trigger language plpgsql as
$$ begin
  if ( currval('test_id_seq')<NEW.id ) then
    raise exception 'currval(test_id_seq)<id';
  end if;
  return NEW;
end; $$;

create trigger test_id_seq_check before insert or update of id on test
  for each row execute procedure test_id_check();

Then inserting with default primary key will work fine:

insert into test(value) values ('a'),('b'),('c'),('d');

But inserting too big primary key will error out and abort:

insert into test(id, value) values (10,'z');

---

To expand on Tometzky's great answer, here is a more general version:

CREATE OR REPLACE FUNCTION check_serial() RETURNS trigger AS $$
BEGIN
  IF currval(TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || '_' || TG_ARGV[0] || '_seq') <
    (row_to_json(NEW)->>TG_ARGV[0])::bigint
  THEN RAISE SQLSTATE '55000';  -- same as currval() of uninitialized sequence
  END IF;
  RETURN NULL;
EXCEPTION     
  WHEN SQLSTATE '55000'
  THEN RAISE 'manual entry of serial field %.%.% disallowed',
    TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0]
    USING HINT = 'use DEFAULT instead of specifying value manually',
      SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0];
END;
$$ LANGUAGE plpgsql;

Which you can apply to any column, say test.id, thusly:

CREATE CONSTRAINT TRIGGER test_id_check
  AFTER INSERT OR UPDATE OF id ON test
  FOR EACH ROW EXECUTE PROCEDURE check_serial(id);

---

I don't exactly understand you question, but if your goal is just to do the insert, and have a valid field (e.g. an id), then insert the values without the id field, that's what "default" stands for. It will work.

E.g. havin a id serial NOT NULL and a CONSTRAINT table_pkey PRIMARY KEY(id) in the table definition will auto-set the id and auto-increment a sequence table_id_seq.

---

What about using a CHECK?

CREATE SEQUENCE pk_test
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE test (
    id INT PRIMARY KEY CHECK (id=currval('pk_test')) DEFAULT nextval('pk_test'),
    num int not null
    );
ALTER SEQUENCE pk_test OWNED BY test.id;

-- Testing:
INSERT INTO test (num) VALUES (3) RETURNING id, num;
1,3 -- OK
2,3 -- OK

INSERT INTO test (id, num) values (30,3) RETURNING id, num;
/*
ERROR:  new row for relation "test" violates check constraint "test_id_check"
DETAIL:  Failing row contains (30, 3).

********** Error **********

ERROR: new row for relation "test" violates check constraint "test_id_check"
SQL state: 23514
Detail: Failing row contains (30, 3).
*/

DROP TABLE test;