我的Postgres 9.0數據庫中有一個小表（約30行），帶有一個整數ID字段（主鍵），該字段當前包含從1開始的唯一連續(xù)整數，但不是使用'serial'關鍵字創(chuàng)建的。如何更改此表，以便從現在開始對該表進行插入將導致此字段的行為就像它是使用“ serial”作為類型創(chuàng)建的一樣？
3 回答
莫回無
TA貢獻1865條經驗 獲得超7個贊
查看以下命令（尤其是注釋的塊）。
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;
千巷貓影
TA貢獻1829條經驗 獲得超7個贊
您也可以使用START WITH從特定點開始序列，盡管setval可以完成與Euler的回答相同的事情，例如，
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');
婷婷同學_
TA貢獻1844條經驗 獲得超8個贊
非交互式解決方案
只需添加其他兩個答案，對于那些需要Sequence通過非交互式腳本創(chuàng)建這些的人，例如在修補一個實時數據庫時。
也就是說，當您不想SELECT手動輸入該值并將其自己鍵入到后續(xù)CREATE語句中時。
簡而言之，您不能執(zhí)行以下操作：
CREATE SEQUENCE foo_a_seq
START WITH ( SELECT max(a) + 1 FROM foo);
...由于START [WITH]in中的子句CREATE SEQUENCE需要一個值，而不是子查詢。
注：作為一個經驗法則，適用于所有非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;
-- ^ ^ ^
-- defaults to: 0
但是，0如果不是非法的話，將當前序列值設置為笨拙。
使用的三參數形式setval會更合適：
-- vvv
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
-- ^ ^
-- is_called
將可選的第三個參數設置為setvalto false將防止next nextval在返回值之前推進序列，因此：
下一個nextval將精確返回指定的值，并且序列前進從以下開始nextval。
—從文檔中的此項開始
在不相關的注釋上，您還可以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) + 1, false) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
用一個 Function
另外，如果您打算對多個列執(zhí)行此操作，則可以選擇使用real 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
添加回答
舉報
0/150
提交
取消