On Jun 16, 2009, at 12:11 AM, A B wrote:
Hi.
I have a little problem (and a suggestion for a solution) that I
wondered if anyone would care to comment on.
I have a standard table filled with customers (with a unique customer
id, names etc.) and for each customer I need to store some integer
values. The problem is that the number of integer values that should
be stored WILL change over time (it can both increase and decrease).
It will most likely grow from zero to 10-18 and then perhaps add 1 or
remove one value per year but basically be about 10-18 or so.
I must be able to add a new integer value to all customers, and remove
an integer value from all customers Altering the table by adding and
deleting columns would theoretically solve it, but since columns are
not really dropped, just hidden away, and there is a 1600 column
limit on tables as I understand it, this would crash the application
at some time in the future, or at least it will waste a lot of
discspace.
Other things I must be able to do is to alter values for a specific
integer value for one customer or all customers simultaneous. This can
be like "change all value 4 to 8 for the third integer value".
And I must be able to quickly select all the integers for a specific
customer for further processing. There will also be access to single
integer values for a specific customer. It's hard to say how much
compared to "get all integer values" for a specific customer. All
customers will be equally accessed.
As I see it I have these options.
Method C)
Have a metadata table as usual, and then store the values in an array.
I must admit that I have not looked enough at arrays yet.
The drawback I can see right now will be the access to a specific
value in the array, but I guess PostgreSQL is fast...
You may want to try this.
pagila=# create table test (id serial primary key, a int[]);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
Time: 12.844 ms
-- let's populate it with some starter data
pagila=# insert into test (a) values ('{1}'), ('{2}'), ('{3}');
INSERT 0 3
Time: 2.127 ms
pagila=# select * from test;
id | a
----+-----
1 | {1}
2 | {2}
3 | {3}
(3 rows)
Time: 2.823 ms
-- so, lets say you want to add a second integer value of 5 to all
rows (remember, postgres arrays are 1-based):
pagila=# update test set a[2] = 5;
UPDATE 3
Time: 1.157 ms
pagila=# select * from test;
id | a
----+-------
1 | {1,5}
2 | {2,5}
3 | {3,5}
(3 rows)
Time: 0.445 ms
-- delete the first integer value for just id=1, the key thing here is
that you use update to delete an individual value in an array
pagila=# update test set a[1] = null where id = 1;
UPDATE 1
Time: 1.688 ms
pagila=# select * from test;
id | a
----+----------
2 | {2,5}
3 | {3,5}
1 | {NULL,5}
(3 rows)
Time: 0.527 ms
-- get integer value 1 for all rows
pagila=# select a[1] from test;
a
----
2
3
\N
(3 rows)
Time: 0.489 ms
-- you can even skip positions
pagila=# update test set a[5] = 10;
UPDATE 3
Time: 1.180 ms
pagila=# select * from test;
id | a
----+-----------------------
2 | {2,5,NULL,NULL,10}
3 | {3,5,NULL,NULL,10}
1 | {NULL,5,NULL,NULL,10}
(3 rows)
Time: 0.431 ms
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
