Actualización: Reproducir la funcionalidad auto_increment en PostgreSQL (II).
Una característica muy utilizada de MySQL es la existencia de campos AUTO_INCREMENT. Un campo AUTO_INCREMENT es una columna numérica de una tabla que genera automáticamente un valor secuencial ascendente para los registros que se insertan sin especificar otro valor. En PostgreSQL podemos imitar ese tipo de campos mediante secuencias:
CREATE SEQUENCE people_id_seq;
CREATE TABLE people (
people_id BIGINT PRIMARY KEY DEFAULT NEXTVAL('people_id_seq'),
name VARCHAR(16),
surname VARCHAR(16)
);
INSERT INTO people(name, surname) VALUES ('Bruce', 'Momjian');
INSERT INTO people(name, surname) VALUES ('Larry', 'Wall');
SELECT * FROM people;
people_id | name | surname
-----------+-------+---------
1 | Bruce | Momjian
2 | Larry | Wall
(2 rows)
No funciona del todo bien, porque la secuencia puede pisar valores de la clave primaria, como por ejemplo así:
INSERT INTO people(people_id, name, surname)
VALUES (4, 'Guido', 'van Rossum');
INSERT INTO people(name, surname)
VALUES ('Zeev', 'Suraski'); -- people_id=3
INSERT INTO people(name, surname)
VALUES ('Andy', 'Gutmans'); -- people_id=4
Una posible solución es:
CREATE RULE insert_people_rule AS
ON INSERT TO people DO ALSO
SELECT SETVAL('people_id_seq', (SELECT MAX(people_id) FROM people));
Que funciona bien porque el SELECT SETVAL(… se realiza antes que el INSERT
INSERT INTO people(people_id, name, surname)
VALUES (4, 'Guido', 'van Rossum');
INSERT INTO people(name, surname)
VALUES ('Zeev', 'Suraski');
INSERT INTO people(name, surname)
VALUES ('Andy', 'Gutmans');
SELECT * FROM people;
people_id | name | surname
-----------+-------+------------
1 | Bruce | Momjian
2 | Larry | Wall
4 | Guido | van Rossum
5 | Zeev | Suraski
6 | Andy | Gutmans
(5 rows)
Aún hay alguna diferencia con respecto al AUTO_INCREMENT de MySQL:
Al hacer un TRUNCATE TABLE no se reinicializa la secuencia. Podemos hacerlo manualmente con
SELECT SETVAL('people_id_seq', 1, FALSE);
o
ALTER SEQUENCE people_id_seq RESTART WITH 1;
Podemos aplicar este proceso a tantas columnas de la tabla como queramos, mientras que MySQL obliga a que las columnas AUTO_INCREMENT sean parte de una PRIMARY KEY, y limita a una columna AUTO_INCREMENT por tabla. (¿Es realmente esto una ventaja?).
Podemos utilizar una secuencia con valores negativos y con el incremento que queramos. En MySQL estamos limitados a poder almacenar (explícitamente) un valor menor que 1 y en cada momento el incremento está determinado por la variable de sistema auto_increment_increment (común para todos los campos AUTO_INCREMENT).
El elefante y la serpiente 