Thread: Array issue....

I wanted to create index 0f 3 numeric array. But it is allowing to insert
more than 4 elements

CREATE TABLE abs(no int[3]);
CREATE TABLE

zivah=# INSERT INTO abs VALUES('{1,3,34,343,4342342,342}');
INSERT 0 1

I need a solution to store an elements exactly 3. More than 3 should not be
allowed.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Array-issue-tp3384334p3384334.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

On Mon, Feb 14, 2011 at 5:21 PM, dba <dbadbb@gmail.com> wrote:

>
>
> I wanted to create index 0f 3 numeric array. But it is allowing to insert
> more than 4 elements
>
> CREATE TABLE abs(no int[3]);
> CREATE TABLE
>
> zivah=# INSERT INTO abs VALUES('{1,3,34,343,4342342,342}');
> INSERT 0 1
>
> I need a solution to store an elements exactly 3. More than 3 should not be
> allowed.
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Array-issue-tp3384334p3384334.html
> Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>


This behavior is documented.

Please check the below link:
http://www.postgresql.org/docs/9.0/static/arrays.html

<<snip>

The syntax for CREATE TABLE allows the exact size of arrays to be specified,
for example:

CREATE TABLE tictactoe (
    squares   integer[3][3]

);

* 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.
<<snip>>

--
Chetan Sutrave
http://www.enterprisedb.com

dba wrote:
>
> I wanted to create index 0f 3 numeric array. But it is allowing to insert
> more than 4 elements
>
> CREATE TABLE abs(no int[3]);
> CREATE TABLE
>
> zivah=# INSERT INTO abs VALUES('{1,3,34,343,4342342,342}');
> INSERT 0 1
>
> I need a solution to store an elements exactly 3. More than 3 should not be
> allowed.
If you treat simple arrays, you may use "CHECK" as following example...

=# CREATE TABLE abs(no int[3], CHECK (array_upper(no, 1) < 3));
CREATE TABLE
=# INSERT INTO abs VALUES ('{1,2}');
INSERT 0 1
=# INSERT INTO abs VALUES ('{1,2,3}');
ERROR:  23514: new row for relation "abs" violates check constraint "abs_no_check"
LOCATION:  ExecConstraints, execMain.c:2049

--
NTT OSS Center
Tatsuhito Kasahara