Fixed size array in PostgreSQL 9.5

Question

I'm using PostgreSQL 9.5. I want create an array with a fixed size, like this:

CREATE TABLE eco.test ( 
    id           text  NOT NULL,
    test_array   integer[3],
    CONSTRAINT pk_aircrafts PRIMARY KEY ( id )
 );

That is, I want the size of test_array will be 3.

But, I can make this:

INSERT INTO eco.test(id, test_array) VALUES ('1', '{1,2,3,4}')

And everything will be fine.

select * from eco.test;
id | test_array
'1'   {1,2,3,4}

How can I make a fixed size array?

Laurenz Albe · Accepted Answer · 2016-12-05 15:01:50Z

10

Create a CHECK constraint that forces array_ndims(test_array) = 1 AND array_length(test_array, 1) = 3.

answered Dec 5, 2016 at 15:01

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

user330315 Over a year ago

alternatively use cardinality() with 9.5

elimisteve Over a year ago

Is this still the best answer in 2021?

Laurenz Albe Over a year ago

@elimisteve To my knowledge, yes.

northmaybe Over a year ago

As of Postgresql 16, still the same. You can check at postgresql.org/docs/current/arrays.html. As of today: "[...] However, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length."

Collectives™ on Stack Overflow

Fixed size array in PostgreSQL 9.5

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related