Inputting columns of data

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