From: | DerekW <derek(at)cmainfo(dot)co(dot)za> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | PostgreSQL add id column that increments based on data |
Date: | 2014-08-01 09:15:41 |
Message-ID: | 1406884541460-5813514.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I am relatively new to using Postgres, coming from a MySQL background. I am
using Postgres 9.3.4 on Windows x64.
We are being supplied data in multiple fixed length text files. The first
digit on each line is a number between 1 and 4 that indicates the record
type of the data in that row. The rows are grouped sequentially such that
there will always first be a row of type 1 followed by zero or more rows of
the other types.
data_x.txt
---------------------
1data01
2data02
4data03
4data04
1data05
1data06
3data07
To import this into Postgres I have used the following SQL commands:
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);
I then create tables for each of the 4 record types:
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;
What I need to do, but am unsure how, is to also add an "id" column for each
group where the indicator starts with 1. We will be getting weekly updates
so I need to specify the initial id for each batch. So if this batch starts
at id = 225, then I want to get the following tables from the sample data:
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
--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-add-id-column-that-increments-based-on-data-tp5813514.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Jonas Xie | 2014-08-01 09:55:31 | Re: PostgreSQL add id column that increments based on data |
Previous Message | CrashBandi | 2014-07-31 19:34:59 | Re: Reg: Sql Join |