Добавление 'серийный' для существующего столбца в Postgres

У меня есть небольшая таблица (~30 строк) в моей базе данных Postgres 9.0 с целочисленным полем ID (первичный ключ), которое в настоящее время содержит уникальные последовательные целые числа, начинающиеся с 1, но которые не были созданы с помощью ключевого слова "serial".



Как я могу изменить эту таблицу таким образом, что теперь вставки в эту таблицу заставят это поле вести себя так, как если бы оно было создано с "последовательным" типом?

422   3  

3 ответов:

посмотрите на следующие команды (особенно комментируемый блок).

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;
97  
2012-02-29 00:09:20

вы также можете использовать START WITH чтобы начать последовательность с определенной точки, хотя setval выполняет то же самое, что и в ответе Эйлера, например, 

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');
29  
2014-08-13 10:17:40

неинтерактивное решение

просто добавляя к двум другим ответам, для тех из нас, кто должен иметь эти Sequenceы, созданные неинтерактивный скрипт, в то время как исправление живой-иш БД, например.

то есть, когда вы не хотите SELECT значение вручную и введите его в последующем CREATE заявление.

короче говоря, вы можете не do:

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

... так как START [WITH] пункт в CREATE SEQUENCE ждет стоимостью, а не подзапрос.

Примечание: как правило, это относится ко всем non-CRUD (т. е.: ничего другого, кроме INSERT,SELECT,UPDATE,DELETE) с заявлениями в pgSQL AFAIK.

, setval() делает! Таким образом, абсолютно нормально следующее: 
SELECT setval('foo_a_seq', max(a)) FROM foo;

если нет данных и вы не (хотите) знать об этом, использовать coalesce() установить значение по умолчанию:

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

на несвязанной ноте, вы также можете указать столбец, владеющие Sequence непосредственно с CREATE, вы не должны изменить его позже:

CREATE SEQUENCE foo_a_seq OWNED BY foo.a;

в итоге:

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

С помощью Function

кроме того, если вы планируете делать это для нескольких столбцов, вы можете выбрать для использования фактическое 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;

используйте его так:

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
2  
2018-05-28 18:01:17

Comments

    Ничего не найдено.
Click here to cancel reply.