Thread: 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