PostgreSQL添加基于数据增量的id列

Question

我对Postgres的使用相对来说还是比较新的，它来自MySQL背景。我在Windows x64上使用Postgres 9.3.4。

我们被提供了多个固定长度文本文件中的数据。每行的第一个数字是介于1到4之间的数字，它指示该行中数据的记录类型。这些行是按顺序分组的，因此总是先有类型1的行，然后是其他类型的零行或多行。

data_x.txt
---------------------
1data01
2data02
4data03
4data04
1data05
1data06
3data07

要将其导入Postgres，我使用了以下SQL命令：

CREATE TABLE data_raw (
    raw_data TEXT
);

COPY data_raw FROM 'C:\path\data_x.txt' ...; -- Repeated for each file

ALTER TABLE data_raw
    ADD COLUMN indicator integer;

UPDATE data_raw SET
    indicator = CAST(substr(raw_data, 1, 1) AS integer),
    raw_data = substr(raw_data, 2);

然后为4种记录类型中的每一种创建表：

CREATE TABLE table_1 SELECT raw_data FROM data_raw WHERE indicator = 1;
CREATE TABLE table_2 SELECT raw_data FROM data_raw WHERE indicator = 2;
CREATE TABLE table_3 SELECT raw_data FROM data_raw WHERE indicator = 3;
CREATE TABLE table_4 SELECT raw_data FROM data_raw WHERE indicator = 4;

我需要做的，但我不知道如何，也是添加一个" id“列，每组的指示符从1开始。我们将得到每周更新，所以我需要为每批指定的初始id。因此，如果这个批处理从id = 225开始，那么我希望从示例数据中获得以下表：

table_1
id  | raw_data
--------------------
225 | data01
226 | data05
227 | data06

table_2
id  | raw_data
--------------------
225 | data02

table_3
id  | raw_data
--------------------
227 | data07

table_4
id  | raw_data
--------------------
225 | data03
225 | data04

Answer 1

尝试这样为每个数据组生成id：

SELECT sum(case when indicator = 1 then 1 else 0 end ) over(order by /*something to define the order*/) as id_base
from data_raw

它将为每个数据组生成一个id_base。如果您需要从某个特定的id开始--只需将这个id添加到id_base。

Answer 2

1)加入PK，

2)为类型创建字典表.

CREATE TABLE "public"."data_raw"(
 "id" Serial  NOT NULL,
 "id_type" Integer  NOT NULL,
 "raw_data" Text
);


CREATE TABLE "public"."data_raw_type"(
 "id" Serial  NOT NULL,
 "name" Character varying(30));

Answer 3

谢谢你的意见。

使用Igor的建议，我提出了以下解决方案：

CREATE TABLE data_raw (
    raw_data TEXT
);

COPY data_raw FROM 'C:\path\data_x.txt' ...; -- Repeated for each file

ALTER TABLE data_raw
    ADD COLUMN pk_id serial,
    ADD COLUMN id integer,
    ADD COLUMN indicator integer;

UPDATE data_raw SET
    indicator = CAST(substr(raw_data, 1, 1) AS integer),
    raw_data = substr(raw_data, 2);

CREATE TABLE id_base AS
SELECT
    pk_id,
    sum(CASE WHEN indicator = 1 THEN 1 ELSE 0 END) OVER (ORDER BY pk_id) AS rec_id
FROM data_raw;

CREATE INDEX id_base_pk ON id_base USING btree(pk_id);

UPDATE data_raw r SET
    id = (SELECT rec_id FROM id_base b WHERE b.pk_id = r.pk_id);

DROP TABLE id_base;