agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: DerekW <derek@cmainfo.co.za>
To: pgsql-sql@postgresql.org
Subject: Re: PostgreSQL add id column that increments based on data
Date: Fri, 01 Aug 2014 06:30:57 -0700
Message-ID: <53DB9691.8060409@aklaver.com> (raw)
In-Reply-To: <1406884541460-5813514.post@n5.nabble.com>
References: <1406884541460-5813514.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 08/01/2014 02:15 AM, DerekW wrote:
> 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
I am not following the logic of the id numbering scheme. I see the
sequential numbering for indicator 1. Not why indicator 2 uses 225 again
when indicator 3 starts with 227 and indicator 4 has the same id for
both rows. My guess, whatever the logic is, it is going to involve
triggers and possibly another table to keep track of batch numbers
>
>
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-add-id-column-that-increments-based-on-data-tp581...
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (5+ messages) latest in thread
Message-ID: <53DB9691.8060409@aklaver.com>
Permalink: ../53DB9691.8060409@aklaver.com/
Also on: postgresql.org/message-id/53DB9691.8060409@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, derek@cmainfo.co.za
Subject: Re: PostgreSQL add id column that increments based on data
In-Reply-To: <53DB9691.8060409@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox