Postgres - array date column with constraint

Question

I am trying to create a table test_table that has a column year which takes an array of 'years', like so:

CREATE TABLE avg_yearly_currencies_used (
    id serial PRIMARY KEY,
    year date[],
    CONSTRAINT first_jan_check CHECK ( date_trunc('year', year) = year )
);

The CONSTRAINT checks to see if the date is in the format such as: 2010-01-01, or 2012-01-01.

If the year column is not an array, then the above command works fine and the table is created. However, by making the date an array, and by having the CONSTRAINT, I get the following error:

ERROR: function date_trunc(unknown, date[]) does not exist

How do apply the CONSTRAINT to the array column year?

You can maybe do something like this CHECK (year <@ ARRAY[2000,2001,2002...]) that is all i can think of right now — Shubham Srivastava
– Shubham Srivastava, Commented Sep 22, 2020 at 3:42
If you just want to store the years, then why don't you use an integer array? You can easily create a proper date at the first of January from them. — user330315
– user330315, Commented Sep 22, 2020 at 7:53
That is a good point, and I might look into doing that instead. — gb_spectrum
– gb_spectrum, Commented Sep 22, 2020 at 16:00

Akhilesh Mishra · Accepted Answer · 2020-09-22 04:24:47Z

1

You can use following approach

Write a custom function to check whether all values in the array is complying with the condition:

create function check_date (
    date_ date[]) returns boolean as 
    $$
    declare
    result boolean;
    begin
    select bool_and (date_trunc('year', n) = n) into result
    from unnest(date_) s(n);
    return result;
    end;
    $$ 
    language plpgsql immutable;

Add above function in check constraint

CREATE TABLE avg_yearly_currencies_used (
    id serial PRIMARY KEY,
    year date[],
    CONSTRAINT first_jan_check CHECK (check_date(year))
);

DEMO

edited Sep 22, 2020 at 4:24

answered Sep 22, 2020 at 4:18

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Postgres - array date column with constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related