Re: Dynamic table

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