Message-ID: <53DB6413.5010704@jxie.de>
Date: Fri, 01 Aug 2014 11:55:31 +0200
From: Jonas Xie <jonas@jxie.de>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.2.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: PostgreSQL add id column that increments based on data
References: <1406884541460-5813514.post@n5.nabble.com>
In-Reply-To: <1406884541460-5813514.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

You can try the *sequence* :
http://www.postgresql.org/docs/9.3/static/functions-sequence.html

Am 01.08.2014 11:15, schrieb DerekW:
> 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.
>
>


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql