Question Autoincrement PostgreSQL


Je passe de MySQL à PostgreSQL et je me demandais comment je pouvais faire des valeurs auto-incrémentées. J'ai vu dans le document PostgreSQL un type de données "série", mais j'obtiens des erreurs de syntaxe lors de son utilisation (en version 8.0).


474
2018-04-24 22:00


origine


Réponses:


Oui, SERIAL est la fonction équivalente.

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 est juste une macro de création de table autour des séquences. Vous ne pouvez pas modifier SERIAL sur une colonne existante.


604
2018-04-24 22:16



Vous pouvez utiliser tout autre type de données entier, tel que smallint.

Exemple :

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;

Mieux vaut utiliser votre propre type de données, plutôt que l'utilisateur type de données série.


204
2017-07-09 02:19



Si vous voulez ajouter la séquence à l'identifiant dans la table qui existe déjà, vous pouvez utiliser:

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

85
2017-07-16 00:50



Alors qu'il semble que les séquences sont les équivalent à MySQL auto_increment, il y a quelques différences subtiles mais importantes:

1. Echec des requêtes Incrément de la séquence / série

La colonne série est incrémentée sur les requêtes échouées. Cela conduit à une fragmentation à partir des requêtes échouées, pas seulement des suppressions de lignes. Par exemple, exécutez les requêtes suivantes sur votre base de données 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;

Vous devriez obtenir la sortie suivante:

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

Remarquez que l'uid va de 1 à 3 au lieu de 1 à 2.

Cela se produit toujours si vous créez manuellement votre propre séquence avec:

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;

Si vous souhaitez tester la différence de MySQL, exécutez les opérations suivantes sur une base de données 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);

Vous devriez obtenir ce qui suit avec pas de fragementation:

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

2. La définition manuelle de la valeur de la colonne série peut entraîner l'échec des requêtes futures.

Cela a été souligné par @trev dans une réponse précédente.

Pour simuler cela manuellement, définissez le uid sur 4, ce qui "se heurtera" plus tard.

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

Données de table:

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

Exécuter un autre insert:

INSERT INTO table1 (col_b) VALUES(6);

Données de table:

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

Maintenant, si vous exécutez un autre insert:

INSERT INTO table1 (col_b) VALUES(7);

Il échouera avec le message d'erreur suivant:

ERREUR: la valeur de la clé en double viole la contrainte unique "table1_pkey"   DETAIL: La clé (uid) = (5) existe déjà.

En revanche, MySQL gérera cela gracieusement comme indiqué ci-dessous:

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

Maintenant, insérez une autre ligne sans mettre l'uid

INSERT INTO table1 (col_b) VALUES(3);

La requête n'échoue pas, uid saute juste à 5:

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

Les tests ont été effectués sur MySQL 5.6.33, pour Linux (x86_64) et PostgreSQL 9.4.9


33
2017-10-08 21:08



À partir de Postgres 10, les colonnes d'identité définies par le standard SQL sont également prises en charge:

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

crée une colonne d'identité qui ne peut pas être substituée sauf demande explicite. L’insertion suivante échouera avec une colonne définie comme generated always:

insert into foo (id) 
values (1);

Cela peut cependant être annulé:

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

En utilisant l'option generated by default c'est essentiellement le même comportement que l'existant serial la mise en oeuvre:

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

Lorsqu'une valeur est fournie manuellement, la séquence sous-jacente doit également être ajustée manuellement - comme avec une serial colonne.


Une colonne d'identité n'est pas une clé primaire par défaut (tout comme une serial colonne). S'il en est un, une contrainte de clé primaire doit être définie manuellement.


19
2017-10-31 12:19



Vous devez faire attention à ne pas insérer directement dans votre champ SERIAL ou de séquence, sinon votre écriture échouera lorsque la séquence atteindra la valeur insérée:

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

16
2017-07-28 18:22



Dans le contexte de la question posée et en réponse au commentaire de @ sereja1c, créer SERIAL crée implicitement des séquences, donc pour l'exemple ci-dessus-

CREATE TABLE foo (id SERIAL,bar varchar);

CREATE TABLE créerait implicitement une séquence foo_id_seq pour la colonne série foo.id. Par conséquent, SERIAL [4 octets] est bon pour sa facilité d'utilisation, sauf si vous avez besoin d'un type de données spécifique pour votre identifiant.


14
2018-04-26 10:57



Désolé, pour reprendre une vieille question, mais c'était la première question / réponse de Stack Overflow qui apparaissait sur Google.

Ce message (qui a été publié en premier sur Google) parle de l'utilisation de la syntaxe plus mise à jour pour PostgreSQL 10: https://blog.2ndquadrant.com/postgresql-10-identity-columns/

ce qui arrive à être:

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

J'espère que cela pourra aider :)


10
2018-02-08 21:29