Vraag Handmatige invoegingen op een postgres-tabel met een primaire sleutelreeks


Ik converteer een MySQL-tabel naar PostgreSQL voor de eerste keer in mijn leven en tegen het traditionele newbie-probleem aan dat ik geen auto_increment heb.

Nu ben ik erachter gekomen dat de oplossing van postgres is om een ​​reeks te gebruiken en vervolgens telkens de volgende waarde () van deze reeks als de standaardwaarde op te vragen. Ik heb ook gelezen dat het type SERIAL automatisch een reeks en een primaire sleutel maakt en dat nextval () de teller opvoert, zelfs wanneer binnen transacties worden aangeroepen, om te voorkomen dat de reeks wordt vergrendeld.

Wat ik niet kan vinden, is de kwestie van wat er gebeurt als je handmatig waarden invoegt in een veld met een UNIQUE of PRIMARY-voorwaarde en een nextval () van een reeks als standaard. Zover ik kan zien, zorgt dit ervoor dat de INSERT mislukt als de reeks die waarde bereikt.

Is er een eenvoudige (of gemeenschappelijke) manier om dit op te lossen?

Een duidelijke uitleg zou zeer op prijs worden gesteld.

Update: als u vindt dat ik dit niet zou moeten doen, zal ik dit nooit kunnen oplossen of maak ik een aantal gebrekkige aannames, aarzel dan niet om ze in uw antwoorden aan te wijzen. Vertel me vooral wat ik moet doen om programmeurs een stabiele en robuuste database aan te bieden die niet kan worden beschadigd met een eenvoudige invoeging (bij voorkeur zonder alles te verbergen achter opgeslagen procedures)


10
2017-10-11 10:33


oorsprong


antwoorden:


Als u uw gegevens migreert, laat ik de volgordevermindering op de kolom vallen, voer ik al uw invoegingen uit, gebruik SETVAL () om de reeks in te stellen op de maximale waarde van uw gegevens en vervolgens de standaardvolgorde nextval () van uw kolomvolgorde te herstellen.


9
2017-10-11 10:42



Om het geweldige antwoord van Tometzky uit te breiden, is hier een meer algemene versie:

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;

Wat je in elke kolom kunt toepassen, zeg test.id, dus:

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

4
2017-08-12 20:36



U kunt een trigger maken die controleert of currval('id_sequence_name')>=NEW.id.

Als uw transactie geen standaardwaarde of nextval('id_sequence_name'), dan een currval functie zal een fout genereren, omdat deze alleen werkt als de reeks in de huidige sessie is bijgewerkt. Als je gebruikt nextval en probeer dan een grotere primaire sleutel in te voegen, dan zal het een nieuwe fout veroorzaken. Een transactie wordt vervolgens afgebroken.

Dit zou het invoegen van slechte primaire sleutels die serieel breken zou voorkomen.

Voorbeeldcode:

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

Dan zal invoegen met standaard primaire sleutel prima werken:

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

Maar het invoegen van een te grote primaire sleutel zal fouten veroorzaken en afbreken:

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

3
2017-10-12 22:06



Ik begrijp je vraag niet helemaal, maar als je doel gewoon is om de invoeging te doen en een geldig veld (bijvoorbeeld een id) hebt, voeg dan de waarden in zonder het veld id, dat is waar "standaard" voor staat. Het zal werken.

Bijv. een id serial NOT NULL en een CONSTRAINT table_pkey PRIMARY KEY(id) in de tabeldefinitie stelt de id automatisch in en wordt een reeks automatisch verhoogd table_id_seq.


2
2017-10-11 10:43



Hoe zit het met het gebruik van een 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;

1
2017-08-28 16:20