Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres doesn't seem to enforce array dimensions

Postgres doesn't seem to enforce array dimensions

From
Joseph S
Date:
What's up with Postgres and array dimensions?  The table definition
doesn't even store how many dimensions the array should be.

[local]:playpen=> create table arrtest(
playpen(>    id SERIAL PRIMARY KEY,
playpen(>   arrone int[],
playpen(>   arrtwo int[][]
playpen(> );
NOTICE:  CREATE TABLE will create implicit sequence "arrtest_id_seq" for
serial column "arrtest.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"arrtest_pkey" for table "arrtest"
CREATE TABLE
Time: 22.688 ms
[local]:playpen=> INSERT into arrtest (arrone) VALUES (null);  --should
work, and does
INSERT 0 1
Time: 3.798 ms
[local]:playpen=> INSERT into arrtest (arrone) VALUES ( ARRAY[1,2]);
INSERT 0 1
Time: 0.668 ms
[local]:playpen=> INSERT into arrtest (arrone) VALUES ( ARRAY[[3,4]]);
--should not work
INSERT 0 1
Time: 0.715 ms
[local]:playpen=> INSERT into arrtest (arrtwo) VALUES ( ARRAY[5,6]);
--should not work
INSERT 0 1
Time: 0.671 ms
[local]:playpen=> INSERT into arrtest (arrtwo) VALUES ( ARRAY[[7,8]]);
INSERT 0 1
Time: 0.704 ms
[local]:playpen=> select * from arrtest;
  id | arrone  | arrtwo
----+---------+---------
   1 |         |
   2 | {1,2}   |
   3 | {{3,4}} |
   4 |         | {5,6}
   5 |         | {{7,8}}
(5 rows)

Time: 1.217 ms
[local]:playpen=>  \d arrtest
                           Table "public.arrtest"
  Column |   Type    |                      Modifiers
--------+-----------+------------------------------------------------------
  id     | integer   | not null default nextval('arrtest_id_seq'::regclass)
  arrone | integer[] |
  arrtwo | integer[] |
Indexes:
     "arrtest_pkey" PRIMARY KEY, btree (id)

[local]:playpen=> select version();
                                                   version

-----------------------------------------------------------------------------------------------------------
  PostgreSQL 8.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
20060404 (Red Hat 3.4.6-9), 32-bit

Re: Postgres doesn't seem to enforce array dimensions

From
Jeff Davis
Date:
On Thu, 2010-04-08 at 13:30 -0400, Joseph S wrote:
> What's up with Postgres and array dimensions?  The table definition
> doesn't even store how many dimensions the array should be.
>

That's a little strange, but it is documented behavior:

From http://www.postgresql.org/docs/8.4/static/arrays.html :

"However, the current implementation ignores any supplied array size
limits, i.e., the behavior is the same as for arrays of unspecified
length.

The current implementation does not enforce the declared number of
dimensions either. Arrays of a particular element type are all
considered to be of the same type, regardless of size or number of
dimensions. So, declaring the array size or number of dimensions in
CREATE TABLE is simply documentation; it does not affect run-time
behavior."

Regards,
    Jeff Davis

Re: Postgres doesn't seem to enforce array dimensions

From
Simon Riggs
Date:
On Thu, 2010-04-08 at 11:02 -0700, Jeff Davis wrote:

> The current implementation does not enforce the declared number of
> dimensions either. Arrays of a particular element type are all
> considered to be of the same type, regardless of size or number of
> dimensions. So, declaring the array size or number of dimensions in
> CREATE TABLE is simply documentation; it does not affect run-time
> behavior.

And if you don't like that, use a CHECK constraint to enforce whatever
rule you think appropriate. All, some or none: your choice. Cool.

--
 Simon Riggs           www.2ndQuadrant.com