基于另一列的 PostgreSQL 序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6821871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL sequence based on another column
提问by Incognito
Lets say I have a table as such:
假设我有一张这样的桌子：
Column | Type | Notes
---------+------------ +----------------------------------------------------------
id | integer | An ID that's FK to some other table
seq | integer | Each ID gets its own seq number
data | text | Just some text, totally irrelevant.
id+ seqis a combined key.
id+seq是组合键。
What I'd like to see is:
我想看到的是：
ID | SEQ | DATA
----+------ +----------------------------------------------
1 | 1 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
1 | 2 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
1 | 3 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
1 | 4 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
2 | 1 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
3 | 1 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
3 | 2 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
3 | 3 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
3 | 4 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
As you can see, a combination of idand seqis unique.
正如你所看到的组合id和seq独特。
I'm not sure how to set up my table (or insert statement?) to do this. I'd like to insert idand data, resulting in seqbeing a sub-sequence dependent on id.
我不确定如何设置我的表（或插入语句？）来做到这一点。我想插入idand data，导致seq成为依赖于id.
回答by Jay Kominek
No problem! We're going to make two tables, thingsand stuff. stuffwill be the table you describe in your question, and thingsis the one it refers to:
没问题！我们要制作两张桌子，things和stuff。stuff将是您在问题中描述的表格，并且things是它所指的表格：
CREATE TABLE things (
id serial primary key,
name text
);
CREATE TABLE stuff (
id integer references things,
seq integer NOT NULL,
notes text,
primary key (id, seq)
);
Then we'll set thingsup with a trigger that will create a new sequence every time a row is created:
然后我们将设置things一个触发器，该触发器将在每次创建行时创建一个新序列：
CREATE FUNCTION make_thing_seq() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
execute format('create sequence thing_seq_%s', NEW.id);
return NEW;
end
$$;
CREATE TRIGGER make_thing_seq AFTER INSERT ON things FOR EACH ROW EXECUTE PROCEDURE make_thing_seq();
Now we'll end up with thing_seq_1, thing_seq_2, etc, etc...
现在我们将得到thing_seq_1, thing_seq_2, 等等, 等等...
Now another trigger on stuffso that it uses the right sequence each time:
现在另一个触发器，stuff以便它每次使用正确的序列：
CREATE FUNCTION fill_in_stuff_seq() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
NEW.seq := nextval('thing_seq_' || NEW.id);
RETURN NEW;
end
$$;
CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW EXECUTE PROCEDURE fill_in_stuff_seq();
That'll ensure that when rows go into stuff, the idcolumn is used to find the right sequence to call nextvalon.
这将确保当行进入时stuff，该id列用于找到要调用的正确序列nextval。
Here's a demonstration:
这是一个演示：
test=# insert into things (name) values ('Joe');
INSERT 0 1
test=# insert into things (name) values ('Bob');
INSERT 0 1
test=# select * from things;
id | name
----+------
1 | Joe
2 | Bob
(2 rows)
test=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+----------
public | stuff | table | jkominek
public | thing_seq_1 | sequence | jkominek
public | thing_seq_2 | sequence | jkominek
public | things | table | jkominek
public | things_id_seq | sequence | jkominek
(5 rows)
test=# insert into stuff (id, notes) values (1, 'Keychain');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Pet goat');
INSERT 0 1
test=# insert into stuff (id, notes) values (2, 'Family photo');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Redundant lawnmower');
INSERT 0 1
test=# select * from stuff;
id | seq | notes
----+-----+---------------------
1 | 1 | Keychain
1 | 2 | Pet goat
2 | 1 | Family photo
1 | 3 | Redundant lawnmower
(4 rows)
test=#
回答by Joe Stefanelli
You could use a window functionto assign your SEQvalues, something like:
您可以使用窗口函数来分配您的SEQ值，例如：
INSERT INTO YourTable
(ID, SEQ, DATA)
SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATA), DATA
FROM YourSource
回答by a_horse_with_no_name
If seqreflects (or should reflect) the order in which the rows are inserted, I'd rather use a timestampthat gets populated automatically and generate the sequence number on the fly when selecting the rows using row_number():
如果seq反映（或应该反映）插入行的顺序，我宁愿使用timestamp自动填充并在使用row_number()以下方法选择行时动态生成序列号：
create table some_table
(
id integer not null,
inserted_at timestamp not null default current_timestamp,
data text
);
The to get the seqcolumn, you can do:
要获取seq列，您可以执行以下操作：
select id,
row_number() over (partition by id order by inserted_at) as seq,
data
from some_table
order by id, seq;
The select is however going to be a bit slower compared to using a persisted seqcolumn (especially with an index on id, seq).
但是，与使用持久seq列（尤其是在 上有索引的情况下id, seq）相比，选择会慢一点。
If that becomes a problem you can either look into using a materialized view, or adding the seqcolumn and then updating it on a regular basis (I would notdo this in a trigger for performance reasons).
如果这成为一个问题，您可以考虑使用物化视图，或者添加seq列然后定期更新它（出于性能原因，我不会在触发器中执行此操作）。
SQLFiddle example: http://sqlfiddle.com/#!15/db69b/1
SQLFiddle 示例：http://sqlfiddle.com/#!15/db69b/1
回答by Abercrombieande
Just a guess.
只是一个猜测。
INSERT INTO TABLE (ID, SEQ, DATA)
VALUES
(
IDVALUE,
(SELECT max(SEQ) +1 FROM TABLE WHERE ID = IDVALUU),
DATAVALUE
);
回答by Steve Chambers
Here's a simple way using standard SQL:
这是使用标准 SQL 的简单方法：
INSERT INTO mytable (id, seq, data)
SELECT << your desired ID >>,
COUNT(*) + 1,
'Quick brown fox, lorem ipsum, lazy dog, etc etc.'
FROM mytable
WHERE id = << your desired ID (same as above) >>;
See SQL Fiddle Demo.
请参阅SQL 小提琴演示。
(If you wanted to be a bit cleverer you could consider creating a triggerto update the row using the same method immediately after an insert.)
（如果你想更聪明一点，你可以考虑创建一个触发器来在插入后立即使用相同的方法更新行。）
回答by Le Droid
I had the same need to dynamicaly store a tree-like structure, not to add all IDs at once.
I prefer not use sequence table for each group as there could be thousands of them.
It run in an intensive multi-processing environment, so it has to be race-condition-proof.
Here the insert fonction for the 1st level. Other levels follow the same principle.
我同样需要动态存储树状结构，而不是一次添加所有 ID。
我不喜欢为每个组使用序列表，因为它们可能有数千个。
它在密集的多处理环境中运行，因此它必须是竞争条件证明。
这里是第一级的插入函数。其他级别遵循相同的原则。
Each group as independent non-reusable sequencial IDs, the function receives a group name & sub-group name, gives you the existing ID or creates it & returns the new ID.
I tried a loop to have a single select, but the code is as long & harder to read.
每个组作为独立的不可重复使用的序列 ID，该函数接收组名和子组名，为您提供现有 ID 或创建它并返回新 ID。
我尝试了一个循环来进行单个选择，但代码同样长且难以阅读。
CREATE OR REPLACE FUNCTION getOrInsert(myGroupName TEXT, mySubGroupName TEXT)
RETURNS INT AS
$BODY$
DECLARE
myId INT;
BEGIN -- 1st try to get it if it already exists
SELECT id INTO myId FROM myTable
WHERE groupName=myGroupName AND subGroupName=mySubGroupName;
IF NOT FOUND THEN
-- Only 1 session can get it but others can read
LOCK TABLE myTable IN SHARE ROW EXCLUSIVE MODE;
-- 2nd try in case of race condition
SELECT id INTO myId FROM myTable
WHERE groupName=myGroupName AND subGroupName=mySubGroupName;
IF NOT FOUND THEN -- Doesn't exist. Get next ID for this group.
SELECT COALESCE(MAX(id), 0)+1 INTO myId FROM myTable
WHERE groupName=myGroupName;
INSERT INTO myTable (groupName, id, subGroupName)
VALUES (myGroupName, myId, mySubGroupName);
END IF;
END IF;
RETURN myId;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;
To try it:
试试看：
CREATE TABLE myTable (GroupName TEXT, SubGroupName TEXT, id INT);
SELECT getOrInsert('groupA', 'subgroupX'); -- Returns 1
...
SELECT * FROM myTable;
groupname | subgroupname | id
-----------+--------------+----
groupA | subgroupX | 1
groupA | subgroupY | 2
groupA | subgroupZ | 3
groupB | subgroupY | 1
回答by Ryan Kinal
PostgreSQL supports grouped unique columns, as such:
PostgreSQL 支持分组的唯一列，例如：
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
See PostgreSQL Documentation- Section 5.3.3
请参阅PostgreSQL 文档- 第 5.3.3 节
Easy :-)
简单 ：-）
回答by Nathan
I don't have any postgresql-specific experience, but can you use a subquery in your insert statement? Something like, in Mysqlish,
我没有任何特定于 postgresql 的经验，但是您可以在插入语句中使用子查询吗？类似的东西，在 Mysqlish 中，
INSERT INTO MYTABLE SET
ID=4,
SEQ=( SELECT MAX(SEQ)+1 FROM MYTABLE WHERE ID=4 ),
DATA="Quick brown fox, lorem ipsum, lazy dog, etc etc."
