Autoincremento PostgreSQL

Sto passando da MySQL a PostgreSQL e mi chiedevo come fare i valori di autoincremento. Ho visto nei documenti PostgreSQL un tipo di dati “seriale”, ma ottengo errori di syntax durante l’utilizzo (nella versione 8.0).

Sì, SERIAL è la funzione equivalente.

CREATE TABLE foo ( id SERIAL, bar varchar); INSERT INTO foo (bar) values ('blah'); INSERT INTO foo (bar) values ('blah'); SELECT * FROM foo; 1,blah 2,blah 

SERIAL è solo una macro per creare tabelle temporali attorno alle sequenze. Non è ansible modificare SERIAL su una colonna esistente.

È ansible utilizzare qualsiasi altro tipo di dati intero , ad esempio smallint .

Esempio :

 CREATE SEQUENCE user_id_seq; CREATE TABLE user ( user_id smallint NOT NULL DEFAULT nextval('user_id_seq') ); ALTER SEQUENCE user_id_seq OWNED BY user.user_id; 

Meglio utilizzare il proprio tipo di dati, piuttosto che il tipo di dati seriali dell’utente.

Se vuoi aggiungere la sequenza all’id nella tabella che già esiste puoi usare:

 CREATE SEQUENCE user_id_seq; ALTER TABLE user ALTER user_id SET DEFAULT NEXTVAL('user_id_seq'); 

Mentre sembra che le sequenze siano l’ equivalente di auto_increment MySQL, ci sono alcune differenze sottili ma importanti:

1. Incremento delle query non riuscite Sequenza / Seriale

La colonna seriale viene incrementata su query non riuscite. Ciò porta alla frammentazione delle query non riuscite, non solo delle eliminazioni di riga. Ad esempio, esegui le seguenti query sul tuo database PostgreSQL:

 CREATE TABLE table1 ( uid serial NOT NULL PRIMARY KEY, col_b integer NOT NULL, CHECK (col_b>=0) ); INSERT INTO table1 (col_b) VALUES(1); INSERT INTO table1 (col_b) VALUES(-1); INSERT INTO table1 (col_b) VALUES(2); SELECT * FROM table1; 

Dovresti ottenere il seguente risultato:

  uid | col_b -----+------- 1 | 1 3 | 2 (2 rows) 

Nota come uid va da 1 a 3 invece di 1 a 2.

Questo si verifica ancora se tu dovessi creare manualmente la tua sequenza con:

 CREATE SEQUENCE table1_seq; CREATE TABLE table1 ( col_a smallint NOT NULL DEFAULT nextval('table1_seq'), col_b integer NOT NULL, CHECK (col_b>=0) ); ALTER SEQUENCE table1_seq OWNED BY table1.col_a; 

Se si desidera verificare come MySQL è diverso, eseguire quanto segue su un database MySQL:

 CREATE TABLE table1 ( uid int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, col_b int unsigned NOT NULL ); INSERT INTO table1 (col_b) VALUES(1); INSERT INTO table1 (col_b) VALUES(-1); INSERT INTO table1 (col_b) VALUES(2); 

Dovresti ottenere quanto segue senza alcuna frustrazione :

 +-----+-------+ | uid | col_b | +-----+-------+ | 1 | 1 | | 2 | 2 | +-----+-------+ 2 rows in set (0.00 sec) 

2. L’impostazione manuale del valore della colonna seriale può causare il fallimento delle query future.

Questo è stato indicato da @trev in una risposta precedente.

Per simulare questo manualmente impostare l’uid su 4 che “scontrerà” più tardi.

 INSERT INTO table1 (uid, col_b) VALUES(5, 5); 

Dati della tabella:

  uid | col_b -----+------- 1 | 1 3 | 2 5 | 5 (3 rows) 

Esegui un altro inserto:

 INSERT INTO table1 (col_b) VALUES(6); 

Dati della tabella:

  uid | col_b -----+------- 1 | 1 3 | 2 5 | 5 4 | 6 

Ora se esegui un altro inserto:

 INSERT INTO table1 (col_b) VALUES(7); 

Fallirà con il seguente messaggio di errore:

ERRORE: il valore della chiave duplicata viola il vincolo univoco “table1_pkey” DETAIL: Key (uid) = (5) esiste già.

Al contrario, MySQL gestirà questo con garbo come mostrato di seguito:

 INSERT INTO table1 (uid, col_b) VALUES(4, 4); 

Ora inserisci un’altra riga senza impostare uid

 INSERT INTO table1 (col_b) VALUES(3); 

La query non fallisce, uid salta a 5:

 +-----+-------+ | uid | col_b | +-----+-------+ | 1 | 1 | | 2 | 2 | | 4 | 4 | | 5 | 3 | +-----+-------+ 

I test sono stati eseguiti su MySQL 5.6.33, per Linux (x86_64) e PostgreSQL 9.4.9

A partire da Postgres 10, sono supportate anche le colonne Identity come definite dallo standard SQL:

 create table foo ( id integer generated always as identity ); 

crea una colonna di id quadro che non può essere sovrascritta se non richiesta esplicitamente. Il seguente inserimento fallirà con una colonna definita come generated always :

 insert into foo (id) values (1); 

Questo può comunque essere annullato:

 insert into foo (id) overriding system value values (1); 

Quando si utilizza l’opzione generated by default questo è essenzialmente lo stesso comportamento dell’implementazione serial esistente:

 create table foo ( id integer generated by default as identity ); 

Quando un valore viene fornito manualmente, anche la sequenza sottostante deve essere regolata manualmente, come con una colonna serial .


Una colonna Identity non è una chiave primaria per impostazione predefinita (proprio come una colonna serial ). Se dovrebbe essere uno, è necessario definire manualmente un vincolo di chiave primaria.

Devi stare attento a non inserire direttamente nel tuo SERIAL o campo di sequenza, altrimenti la tua scrittura fallirà quando la sequenza raggiunge il valore inserito:

 -- Table: "test" -- DROP TABLE test; CREATE TABLE test ( "ID" SERIAL, "Rank" integer NOT NULL, "GermanHeadword" "text" [] NOT NULL, "PartOfSpeech" "text" NOT NULL, "ExampleSentence" "text" NOT NULL, "EnglishGloss" "text"[] NOT NULL, CONSTRAINT "PKey" PRIMARY KEY ("ID", "Rank") ) WITH ( OIDS=FALSE ); -- ALTER TABLE test OWNER TO postgres; INSERT INTO test("Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss") VALUES (1, '{"der", "die", "das", "den", "dem", "des"}', 'art', 'Der Mann küsst die Frau und das Kind schaut zu', '{"the", "of the" }'); INSERT INTO test("ID", "Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss") VALUES (2, 1, '{"der", "die", "das"}', 'pron', 'Das ist mein Fahrrad', '{"that", "those"}'); INSERT INTO test("Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss") VALUES (1, '{"der", "die", "das"}', 'pron', 'Die Frau, die nebenen wohnt, heißt Renate', '{"that", "who"}'); SELECT * from test; 

Nel contesto della domanda posta e in risposta al commento di @ sereja1c, la creazione di SERIAL crea implicitamente sequenze, quindi per l’esempio di cui sopra-

 CREATE TABLE foo (id SERIAL,bar varchar); 

CREATE TABLE implicitamente creerebbe la sequenza foo_id_seq per la colonna seriale foo.id Quindi, SERIAL [4 Bytes] è utile per la sua facilità d’uso a meno che non sia necessario un tipo di dati specifico per il proprio ID.

Spiacente, per ripetere una vecchia domanda, ma questa è stata la prima domanda / risposta Stack Overflow che è comparsa su Google.

Questo post (che è apparso per primo su Google) parla dell’uso della syntax più aggiornata per PostgreSQL 10: https://blog.2ndquadrant.com/postgresql-10-identity-columns/

che sembra essere:

 CREATE TABLE test_new ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, ); 

Spero possa aiutare 🙂

In questo modo funzionerà sicuramente, spero che aiuti:

 CREATE TABLE fruits( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL ); INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple'); or INSERT INTO fruits VALUES(DEFAULT,'apple'); 

Puoi controllare i dettagli nel link seguente: http://www.postgresqltutorial.com/postgresql-serial/