pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mark Volpe <volpe.mark@epamail.epa.gov>
To: Indraneel Majumdar <indraneel@www.cdfd.org.in>
Cc: pgsql-sql@postgresql.org
Subject: Re: dynamic object creation
Date: Thu, 12 Oct 2000 10:22:12 -0400
Message-ID: <39E5C914.F764BA47@epamail.epa.gov> (raw)
References: <Pine.SGI.3.96.1001012124609.90724C-100000@www.cdfd.org.in>

You may want to think about creating your table like this (for example):

CREATE TABLE data
(
	key text,
	field_type char,
	value text
);

CREATE UNIQUE INDEX data_key ON data(key, field_type, value);

So this way each "record" takes up several rows in the table, and each "field"
can take up as many rows as you need. A table like this, with two columns
being arrays:

key  | field1  | field2 
-------------------------
a    | [x,y,z] | [a,d,f]
b    | [m,n]   | (NULL)

Can be represented like this instead:

key  | field_type | value
-------------------------
a    | 1          | x
a    | 1          | y
a    | 1          | z
a    | 2          | a
a    | 2          | d
a    | 2          | f
b    | 1          | m
b    | 1          | n


I'm not sure what your data looks like, but I hope this helps.

Mark

Indraneel Majumdar wrote:
> 
> Hi,
> 
> I'm not sure if the subject line has been proper. I have this following
> problem which I hope PostgreSQL can handle.
> 
> I'm converting a complex flatfile where records are arranged serially.
> some fields are as 'n' times repeating blocks of multiple lines. Some
> subfields within these are also 'n' time repeating blocks of multiple
> lines. So in my main table I do not know (until at run time) how many
> fields to create (same for any sub tables). How can I do this dynamically?
> 
> I tried using arrays, but retrieval from that is causing some problems. I
> have already checked the array utilities in the contrib section and have
> extended the operator list for other types (I'll send the file to it's
> original author so that he may include it if he wishes).
> 
> I think there must be some object-oriented way of doing this without
> creating too many keys. or are keys the only and best method? Using this
> is causing a performance hit. If it's any help, what I'm trying to convert
> are biological databases distributed in 'SRS' flatfile format from
> ftp.ebi.ac.uk/pub/databases/
> 
> Thank you,
> Indraneel
> 
> /************************************************************************.
> # Indraneel Majumdar                  ¡  E-mail: indraneel@123india.com  #
> # Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
> # Centre for DNA Fingerprinting and Diagnostics,                         #
> # Hyderabad, India - 500076                                              #
> `************************************************************************/



view thread (4+ messages)  latest in thread

Message-ID: <39E5C914.F764BA47@epamail.epa.gov>
Permalink:  ../39E5C914.F764BA47@epamail.epa.gov/
Also on:    postgresql.org/message-id/39E5C914.F764BA47@epamail.epa.gov

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: volpe.mark@epamail.epa.gov, indraneel@www.cdfd.org.in
  Subject: Re: dynamic object creation
  In-Reply-To: <39E5C914.F764BA47@epamail.epa.gov>
  References: <Pine.SGI.3.96.1001012124609.90724C-100000@www.cdfd.org.in> <39E5C914.F764BA47@epamail.epa.gov>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox