Обсуждение: Inputting columns of data

Dear All,

I have a text file with data like:
1 2 3
4 5 6
7 8 9
i.e. so I have three columns of numbers.

I wish to put this data into a table. However, I do not want it to take up
three rows, rather only a single row and a single column. I was think
something like this

CREATE TABLE t1(data numeric[]);
INSERT INTO TABLE t1 VALUES ('{1 4 7, 2 5 8, 3 6 9}')

but this does not work.

What's the best way of approaching this problem? All the files I'll put into
the table will not have the same number of columns, or be the same length. I
would only like to access a single column of data, but never a single
number.

Many thanks

Colin

_________________________________________________________________
Sign-up for a FREE BT Broadband connection today!
http://www.msn.co.uk/specials/btbroadband

On Monday 16 February 2004 15:46, C G wrote:
> Dear All,
>
> I have a text file with data like:
> 1 2 3
> 4 5 6
> 7 8 9
> i.e. so I have three columns of numbers.
>
> I wish to put this data into a table. However, I do not want it to take up
> three rows, rather only a single row and a single column. I was think
> something like this
>
> CREATE TABLE t1(data numeric[]);
> INSERT INTO TABLE t1 VALUES ('{1 4 7, 2 5 8, 3 6 9}')
>
> but this does not work.

I'm not sure what you thought it would do - this is supposed to be a three
element array of what type?
Oh - you don't need the "TABLE" on the insert either.

> What's the best way of approaching this problem? All the files I'll put
> into the table will not have the same number of columns, or be the same
> length. I would only like to access a single column of data, but never a
> single number.

Define your table:
CREATE TABLE t1 (data text);
INSERT INTO t1 VALUES ('1 2 3 4 5 6 7 8 9');

That'll store pretty much anything you like. If you need null values you might
want to investigate bytea type.

On the other hand, that's a poor way of using a relational database - can you
explain what you are trying to achieve? Someone might have a better way to do
it.
--
  Richard Huxton
  Archonet Ltd

>On the other hand, that's a poor way of using a relational database - can
>you
>explain what you are trying to achieve? Someone might have a better way to
>do
>it.
>--
>   Richard Huxton
>   Archonet Ltd

I'm using the database to store results from a numerical simulation, so the
first column of the data array would be 'time', then the next column(s)
would be population(s). There are about 1000 rows in each array.

I about thought putting each row of the array into a row in the table.
However, this could very quickly lead to a table with more than a million
rows. Would this be a problem?

Any suggestions welcome.

Thanks

Colin

_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger

On Monday 16 February 2004 17:16, C G wrote:
> >On the other hand, that's a poor way of using a relational database - can
> >you
> >explain what you are trying to achieve? Someone might have a better way to

> I'm using the database to store results from a numerical simulation, so the
> first column of the data array would be 'time', then the next column(s)
> would be population(s). There are about 1000 rows in each array.
>
> I about thought putting each row of the array into a row in the table.
> However, this could very quickly lead to a table with more than a million
> rows. Would this be a problem?

Well PG will handle that quantity of data comfortably, but without knowing
your performance requirements and hardware it's difficult to estimate speed.

I'd recommend putting together a test - I usually regret compromising design
for performance.

--
  Richard Huxton
  Archonet Ltd