主页 / dba / 问题 / 267491
Accepted
425nesp
Asked: 2020-05-20 14:54:59 +0800 CST

数据迁移：使用插入选择复制数据并填充唯一列

  • 772

我在 Postgres 中有一些这样的表。

create table foo (
    txt text
);
insert into foo (txt) values ('a');
insert into foo (txt) values ('b');
insert into foo (txt) values ('c');

create table bar (
  txt text,
  n integer not null unique
);

我正在进行架构迁移。我想将数据foo从bar. 我想bar看起来像这样。

=# select * from new;
txt | n
----+--
a   | 1
b   | 2
c   | 3

n不必是连续的，但它必须是uniqueand not null。

我认为这个查询会起作用。

insert into bar (txt, n)
select txt, generate_series(1, (select count(*) from foo))
from foo;

但我认为这会一遍又一遍地为 each 生成第 1-N 行'a', 'b', 'c'，这违反了unique约束。

SQL 小提琴：http ://sqlfiddle.com/#!17/06e92c

更新

好的，我找到了一种 hacky 方法。

insert into bar (txt, n)
select txt, trunc(random() * 999999 + 1)
from foo;

当然，这不保证是unique. :/ 但是，如果数据集足够小，你也许可以勉强凑合。

另外，我理解为什么第一种方法现在失败了。generate_series返回多行，而random我只返回 1 行。

postgresql schema-migration

1 个回答

  1. Best Answer

    为什么不使用row_number()

    insert into bar (txt, n)
select txt, row_number() over ()
from foo;

    我遗漏了一个order by会减慢查询的速度，并且在您的情况下不会真正成为优势，因为您只想要一个唯一的号码。

    如果窗口函数的性能不够，另一种方法是为其创建一个序列。

    create sequence seq_foo_nr;

insert into bar (txt, n)
select txt, nextval('seq_foo_nr')
from foo;

drop sequence seq_foo_nr;

    如果您仍然需要自动生成的数字，则定义nr为标识列：

    create table bar (
  txt text,
  n integer not null unique generated always as identity
);

insert into bar (txt)
select txt
from foo;
    • 1

相关问题