Date: Fri, 1 Aug 2014 02:15:41 -0700 (PDT)
From: DerekW <derek@cmainfo.co.za>
To: pgsql-sql@postgresql.org
Message-ID: <1406884541460-5813514.post@n5.nabble.com>
Subject: PostgreSQL add id column that increments based on data
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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