Re: PostgreSQL add id column that increments based on data

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
>     

1. Add a serial column to data_raw so that relative order can be recorded
during import.
2. Alter the COPY command to explicitly list only the raw_data column - so
the serial column uses its default.

At this point if you need to re-assign the sequence numbers on the
production tables based upon some business rules you can simply apply your
logic and feed in the raw data in the previously established order of
import.

The general logic would be to query all of the 1 rows and get their import
index.  Using lead/lag over this set you can, for each 1 record, get the
valid range of child indexes.  You can then join the non-1 data by use of a
between predicate.  In the same 1 query as the lead/lag you can assign base
group numbers using the row_number function and simply add some base offset.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PostgreSQL-add-id-column-that-increments-based-on-data-tp5813514p5813543.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.