update - insertar varios registros en postgresql

Agregar ''serie'' a la columna existente en Postgres

Question

Tengo una pequeña tabla (~ 30 filas) en mi base de datos Postgres 9.0 con un campo de ID entero (la clave principal) que actualmente contiene enteros secuenciales únicos que comienzan en 1, pero que no se crearon usando la palabra clave ''serial''.

¿Cómo puedo modificar esta tabla de forma que a partir de ahora las inserciones en esta tabla causen que este campo se comporte como si hubiera sido creado con ''serie'' como tipo?

Answer 1

Una solución no interactiva

Simplemente agregando a las otras dos respuestas, para aquellos de nosotros que necesitamos tener estas Sequence creadas por una secuencia de comandos no interactiva , al mismo tiempo que aplicamos parches a una base de datos en vivo.

Es decir, cuando no desea SELECT el valor manualmente y escribirlo usted mismo en una declaración CREATE posterior.

En resumen, no puedes hacer:

CREATE SEQUENCE foo_a_seq
    START WITH ( SELECT max(a) + 1 FROM foo);

... ya que la cláusula START [WITH] en CREATE SEQUENCE espera un valor , no una subconsulta.

Nota: Como regla general, eso se aplica a todas las declaraciones que no sean CRUD ( es decir, cualquier otra que INSERT , SELECT , UPDATE , DELETE ) en pgSQL AFAIK.

Sin embargo, setval() hace! Por lo tanto, lo siguiente es absolutamente correcto:

SELECT setval(''foo_a_seq'', max(a)) FROM foo;

Si no hay datos y no desea (desea) conocerlos, use coalesce() para establecer el valor predeterminado:

SELECT setval(''foo_a_seq'', coalesce(max(a), 0)) FROM foo;

En una nota no relacionada, también puede especificar que la columna posee la Sequence directamente con CREATE , no tiene que modificarla más adelante:

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)) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval(''foo_a_seq''); 

Usando una Function

Alternativamente, si planea hacer esto para varias columnas, puede optar por usar una Function real.

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

Answer 2

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

Answer 3

También puede usar START WITH para comenzar una secuencia desde un punto 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'');