在INSERT语句中将一列的默认值设置为另一列

I have a table, call it t1, with three integer columns c1, c2, c3. c1 has a default value of:

我有一个表，称之为t1，有三个整数列c1，c2，c3。 c1的默认值为：

not null default nextval

For the INSERT statements I am currently doing, I want c2 to have the same value as will be assigned to c1. This is not the case for most of my inserts so it does not make sense to define c2 to have a default value or to have a trigger on update. Currently I am doing two statements:

对于我目前正在进行的INSERT语句，我希望c2具有与将分配给c1相同的值。对于我的大多数插入都不是这种情况，因此将c2定义为具有默认值或在更新时具有触发器没有意义。目前我正在做两个陈述：

INSERT INTO t1 (c3) VALUES (val3);
UPDATE t1 SET c2 = c1 WHERE //Get correct row

3 个解决方案

#1

There is no guarantee in which order elements of a set will be processed. There is also no need to make two function calls. Use a sub-select or a CTE:

无法保证处理集合的顺序元素。也没有必要进行两个函数调用。使用子选择或CTE：

INSERT INTO t (c1, c2, c3)
SELECT x.s, x.s, val3
FROM  (SELECT nextval('c1_seq') AS s) x;

Or with a CTE:

或者使用CTE：

WITH   x(s) AS (SELECT nextval('c1_seq'))
INSERT INTO t (c1, c2, c3)
SELECT x.s, x.s, val3
FROM   x;

#2

In your case, you need to set the default value for c2 to the current value of the sequence, associated with c1, i.e.:

在您的情况下，您需要将c2的默认值设置为序列的当前值，与c1相关联，即：

ALTER TABLE
  t1
ALTER COLUMN
  c2
SET DEFAULT
  CURRVAL(PG_GET_SERIAL_SEQUENCE('t1', 'c1'));

Of course, that won't work in case you specify some explicit value for c1. If there are such cases, then you should make a BEFORE trigger, in order to be sure that c2 will always be the same as c1 on insert.

当然，如果您为c1指定了一些显式值，那么这将不起作用。如果存在这种情况，那么您应该进行BEFORE触发，以确保c2始终与插入时的c1相同。

CREATE FUNCTION sync_c2() RETURNS trigger AS $$
BEGIN
  IF NEW.c2 IS NULL THEN
    NEW.c2 := NEW.c1;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER
  sync_c2
BEFORE INSERT ON
  t1
FOR EACH ROW EXECUTE PROCEDURE
  sync_c2();

#3

The default value for c1 will only be used if you don't supply an explicit value for it or, of course, if you explicitly ask for the default value with default in your VALUES. You are also free to access the sequence yourself using nextval and currval inside your VALUES; the nextval function does this:

只有在没有为其提供显式值时才会使用c1的默认值，当然，如果您在VALUES中明确要求默认值为default，则仅使用c1的默认值。您也可以使用VALUES中的nextval和currval*访问序列; nextval函数执行此操作：

Advance sequence and return new value.

提前序列并返回新值。

and currval:

和currval：

Return value most recently obtained with nextval for specified sequence

最近使用nextval获取指定序列的返回值

So you could do this to get the next sequence value for c1 and get the same value for c2:

所以你可以这样做以获得c1的下一个序列值并获得c2的相同值：

insert into t1 (c1, c2, c3) values (nextval('c1_seq'), currval('c1_seq'), val3)

where c1_seq is the name of the sequence that you're using to supply default values for c1.

其中c1_seq是您用于为c1提供默认值的序列的名称。

#1