From: | Stefan Keller <sfkeller(at)gmail(dot)com> |
---|---|
To: | pgsql-general List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to append an element to a row inside a 2-dim. array? |
Date: | 2013-03-11 16:15:35 |
Message-ID: | CAFcOn28=O_o_vKJxG=WEveGD6itw3e-6BT1b+uYw5tFpeKZhcA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
... and I'm wondering if an index really speeds up array functions:
CREATE INDEX idx_ourarrtable_arr ON ourarrtable USING GIN(arr);
Stefan
2013/3/11 Stefan Keller <sfkeller(at)gmail(dot)com>:
> Hi,
>
> Question regarding arrays: How can I append an element to a row inside
> a 2-dim. array?
> See example below.
> And:
> Does anybody have experiences how arrays perform if data grows (it's
> "read-mostly")?
>
> Yours, Stefan
>
> --
> -- Arrays Test
> --
>
> CREATE TABLE ourarrtable (id int primary key, arr int[]);
>
> INSERT INTO ourarrtable VALUES (1, '{1,2,3}');
> INSERT INTO ourarrtable VALUES (2, '{{11,12,13},{21,22,23},{31,32,33}}');
>
> SELECT * FROM ourarrtable ORDER BY id;
> /*
> 1;"{1,2,3}"
> 2;"{{11,12,13},{21,22,23},{31,32,33}}"
> */
> SELECT arr[1:1] FROM ourarrtable WHERE id=2;
> -- "{{11,12,13}}"
>
> -- Works for 1-dim. array in row 1:
> UPDATE ourarrtable SET arr = array_append(arr, 4) WHERE id = 1;
>
> -- How to append say number 14 to '{11,12,13}' inside 2-dim. array?
> UPDATE ourarrtable SET arr[1:1] = array_cat(arr[1:1], 14) WHERE id = 1;
> /*
> ERROR: Function array_cat(integer[], integer) does not exist
> LINE 1: UPDATE ourarrtable SET arr[1:1] = array_cat(arr[1:1], 14) WH...
> */
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-03-11 17:10:25 | Re: Upgrading postgresql-8.4 |
Previous Message | Joe Van Dyk | 2013-03-11 16:10:29 | Re: Joining against a view that uses an aggregate - performance issue |