Manual inserts on a postgres table with a primary key sequence

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);