14  

You could use a window function to assign your SEQ values, something like:

您可以使用窗口函数来分配SEQ值，比如:

INSERT INTO YourTable
    (ID, SEQ, DATA)
    SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATA), DATA
        FROM YourSource

14  

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...

现在我们将以thing_seq_1、thing_seq_2等结束……

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.

这将确保当行进入内容时，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=#

2  

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():

如果seq反映(或应该反映)插入行的顺序，我宁愿使用自动填充的时间戳，并在使用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:

要获得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 seq column (especially with an index on id, seq).

然而，与使用持久化seq列相比，select要慢一些(特别是对于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).

如果这成为一个问题，您可以使用物化视图查看，或者添加seq列，然后定期更新它(出于性能原因，我不会在触发器中这样做)。

SQLFiddle example: http://sqlfiddle.com/#!15/db69b/1

SQLFiddle示例:http://sqlfiddle.com/ ! 15 / db69b / 1

1  

You can use Sequences either It's also a great option

你可以使用序列，它也是一个很好的选择

CREATE SEQUENCE SEQ_NUM START 1;

INSERT INTO TABLE_NAME(ID, SEQ, DATA) VALUES(**SOME_ID**, SELECT nextval('SEQ_NUM') AS SEQ_NUM, **SOME DATA**);

Now In this case the Sequence "SEQ_NUM" value will be managed totally by PostgreSQL, Now you dont have to manage any counter or anything else.

在这种情况下，序列“SEQ_NUM”值将完全由PostgreSQL管理，现在您不必管理任何计数器或其他任何东西。

0  

Just a guess.

只是一个猜测。

INSERT INTO TABLE (ID, SEQ, DATA)
VALUES
(
 IDVALUE,
 (SELECT max(SEQ) +1 FROM TABLE WHERE ID = IDVALUU),
 DATAVALUE
);

0  

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 trigger to update the row using the same method immediately after an insert.)

(如果您想更聪明一点，可以考虑创建一个触发器，在插入之后立即使用相同的方法更新行。)

-1  

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 :-)

容易:-)

-1  

I don't have any postgresql-specific experience, but can you use a subquery in your insert statement? Something like, in Mysqlish,

我没有任何特定于postgresql的经验，但是可以在insert语句中使用子查询吗?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."