agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: PostgreSQL add id column that increments based on data
Date: Fri, 1 Aug 2014 09:12:49 -0700 (PDT)
Message-ID: <1406909569687-5813543.post@n5.nabble.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>

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-tp581...
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

view thread (5+ messages)  latest in thread

Message-ID: <1406909569687-5813543.post@n5.nabble.com>
Permalink:  ../1406909569687-5813543.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1406909569687-5813543.post@n5.nabble.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: david.g.johnston@gmail.com
  Subject: Re: PostgreSQL add id column that increments based on data
  In-Reply-To: <1406909569687-5813543.post@n5.nabble.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