PostgreSQL sequence based on another column
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
+ seq
is a combined key.
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, lo开发者_开发百科rem 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 id
and seq
is unique.
I'm not sure how to set up my table (or insert statement?) to do this. I'd like to insert id
and data
, resulting in seq
being a sub-sequence dependent on id
.
No problem! We're going to make two tables, things
and stuff
. stuff
will be the table you describe in your question, and things
is the one it refers to:
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 things
up with a trigger that will create a new sequence every time a row is created:
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...
Now another trigger on stuff
so that it uses the right sequence each time:
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 id
column is used to find the right sequence to call nextval
on.
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=#
You could use a window function to assign your SEQ
values, something like:
INSERT INTO YourTable
(ID, SEQ, DATA)
SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATA), DATA
FROM YourSource
If seq
reflects (or should reflect) the order in which the rows are inserted, I'd rather use a timestamp
that gets populated automatically and generate the sequence number on the fly when selecting the rows using row_number()
:
create table some_table
(
id integer not null,
inserted_at timestamp not null default current_timestamp,
data text
);
The to get the seq
column, you can do:
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 seq
column (especially with an index on id, seq
).
If that becomes a problem you can either look into using a materialized view, or adding the seq
column and then updating it on a regular basis (I would not do this in a trigger for performance reasons).
SQLFiddle example: http://sqlfiddle.com/#!15/db69b/1
Just a guess.
INSERT INTO TABLE (ID, SEQ, DATA)
VALUES
(
IDVALUE,
(SELECT max(SEQ) +1 FROM TABLE WHERE ID = IDVALUU),
DATAVALUE
);
Here's a simple way using standard 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.
(If you wanted to be a bit cleverer you could consider creating a trigger to update the row using the same method immediately after an insert.)
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.
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.
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
PostgreSQL supports grouped unique columns, as such:
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
See PostgreSQL Documentation - Section 5.3.3
Easy :-)
I don't have any postgresql-specific experience, but can you use a subquery in your insert statement? Something like, in 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."
精彩评论