PostgreSQL array decorator

Question

I realized that there are two ways to indicate arrays in a table in PostgreSQL but I cannot figure out what the difference is, or if either one has any advantages.

There seem to be two syntaxes:

{a,b,c,d,e}
[0:4]={a,b,c,d,e}

For example:

CREATE TABLE table1
     (id serial, arrayspalte smallint[], dt timestamp)
;

INSERT INTO table1
     (id, arrayspalte, dt)
VALUES
     (330, '[0:4]={12,14,27,45,50}', '2007-09-30 10:39:52'),
     (331, '{2, 6,100,200,500,1000}', '2007-09-30 10:41:52')

;

Which in the table then looks like this:

Both an array, but both look very different. It's clear that the former one shows the dimensions of the array, but is there any bigger difference between the two? And is it OK to have both kinds in the same table, or could that cause issues?

Clodoaldo Neto · Accepted Answer · 2015-10-14 14:58:15Z

3

By default the array lower bound is 1:

select ('{12,14,27,45,50}'::int[])[5];
 int4 
------
   50

You changed the lower bound to zero:

select ('[0:4]={12,14,27,45,50}'::int[])[5];
 int4 
------

(1 row)

In your example the first element of the second array can not be returned at [0]

select
    coalesce(a[0]::text, 'null'),
    array_lower(a, 1),
    array_upper(a, 1)
from (values
    ('[0:4]={12,14,27,45,50}'::int[]),
    ('{2, 6,100,200,500,1000}')
) s(a)
;
 coalesce | array_lower | array_upper 
----------+-------------+-------------
 12       |           0 |           4
 null     |           1 |           6

edited Oct 14, 2015 at 14:58

answered Oct 14, 2015 at 14:49

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BritishSteel Over a year ago

So, it is all about changing the array's bounds; good to know. So, it would not be wrong to use both kinds in the same table but it would most likely not be advised, as the query would return unexpected results, right?

BritishSteel Over a year ago

Unexpected as in unexpected for those, who used both when defining the arrays and who are now surprised why the 'indexig does not work'.

Clodoaldo Neto Over a year ago

@BritishSteel I'm unaware of a use case for the decorator. But it must be somewhere in the mailing list.

Collectives™ on Stack Overflow

PostgreSQL array decorator

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related