Solución:
Mire los siguientes comandos (especialmente el bloque comentado).
DROP TABLE foo;
DROP TABLE bar;
CREATE TABLE foo (a int, b text);
CREATE TABLE bar (a serial, b text);
INSERT INTO foo (a, b) SELECT i, 'foo ' || i::text FROM generate_series(1, 5) i;
INSERT INTO bar (b) SELECT 'bar ' || i::text FROM generate_series(1, 5) i;
-- blocks of commands to turn foo into bar
CREATE SEQUENCE foo_a_seq;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
ALTER TABLE foo ALTER COLUMN a SET NOT NULL;
ALTER SEQUENCE foo_a_seq OWNED BY foo.a; -- 8.2 or later
SELECT MAX(a) FROM foo;
SELECT setval('foo_a_seq', 5); -- replace 5 by SELECT MAX result
INSERT INTO foo (b) VALUES('teste');
INSERT INTO bar (b) VALUES('teste');
SELECT * FROM foo;
SELECT * FROM bar;
También puedes usar START WITH
para comenzar una secuencia desde un punto en particular, aunque setval logra lo mismo, como en la respuesta de Euler, por ejemplo,
SELECT MAX(a) + 1 FROM foo;
CREATE SEQUENCE foo_a_seq START WITH 12345; -- replace 12345 with max above
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
TL; DR
Aquí hay una versión en la que no necesita un humano para leer un valor y escribirlo ellos mismos.
CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
Otra opción sería emplear el reutilizable Function
compartido al final de esta respuesta.
Una solución no interactiva
Solo agregando a las otras dos respuestas, para aquellos de nosotros que necesitamos tener estas Sequence
s creado por un secuencia de comandos no interactiva, mientras parchea una base de datos en vivo, por ejemplo.
Es decir, cuando no quieres SELECT
el valor manualmente y escríbalo usted mismo en un CREATE
declaración.
En resumen, puedes no hacer:
CREATE SEQUENCE foo_a_seq
START WITH ( SELECT max(a) + 1 FROM foo );
… desde el START [WITH]
cláusula en CREATE SEQUENCE
espera un valor, no una subconsulta.
Nota: Como regla general, eso se aplica a todos los que no son CRUD (es decir: cualquier otra cosa que
INSERT
,SELECT
,UPDATE
,DELETE
) declaraciones en pgSQL HASTA DONDE SE.
Sin embargo, setval()
¡lo hace! Por lo tanto, lo siguiente está absolutamente bien:
SELECT setval('foo_a_seq', max(a)) FROM foo;
Si no hay datos y no (quiere) saberlo, utilice coalesce()
para establecer el valor predeterminado:
SELECT setval('foo_a_seq', coalesce(max(a), 0)) FROM foo;
-- ^ ^ ^
-- defaults to: 0
Sin embargo, tener el valor de secuencia actual establecido en 0
es torpe, si no ilegal.
Usando la forma de tres parámetros de setval
sería más apropiado:
-- vvv
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
-- ^ ^
-- is_called
Configuración del tercer parámetro opcional de setval
para false
evitará el próximo nextval
de avanzar la secuencia antes de devolver un valor, y así:
el siguiente
nextval
devolverá exactamente el valor especificado, y el avance de la secuencia comienza con lo siguientenextval
.
– de esta entrada en la documentación
En una nota no relacionada, también puede especificar la columna que posee el Sequence
directamente con CREATE
, no tienes que modificarlo más tarde:
CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
En resumen:
CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
Usando un Function
Alternativamente, si planea hacer esto para varias columnas, puede optar por usar una Function
.
CREATE OR REPLACE FUNCTION make_into_serial(table_name TEXT, column_name TEXT) RETURNS INTEGER AS $$
DECLARE
start_with INTEGER;
sequence_name TEXT;
BEGIN
sequence_name := table_name || '_' || column_name || '_seq';
EXECUTE 'SELECT coalesce(max(' || column_name || '), 0) + 1 FROM ' || table_name
INTO start_with;
EXECUTE 'CREATE SEQUENCE ' || sequence_name ||
' START WITH ' || start_with ||
' OWNED BY ' || table_name || '.' || column_name;
EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name ||
' SET DEFAULT nextVal(''' || sequence_name || ''')';
RETURN start_with;
END;
$$ LANGUAGE plpgsql VOLATILE;
Úselo así:
INSERT INTO foo (data) VALUES ('asdf');
-- ERROR: null value in column "a" violates not-null constraint
SELECT make_into_serial('foo', 'a');
INSERT INTO foo (data) VALUES ('asdf');
-- OK: 1 row(s) affected