How to unnest an ARRAY of dates in Postgres?

Question

I want to do an insert on the following table but I am not able to convert an ARRAY of dates.

CREATE TABLE schedule (
  idschedule serial NOT NULL,
  idzone integer NOT NULL,
  "time" timestamp without time zone NOT NULL,
  automatic boolean NOT NULL,
  idrecurrence character varying(20),
  duration integer,
  date date,
)

The INSERT I am trying to perform:

INSERT INTO schedule(idzone, "date", "time", duration, automatic) 
SELECT x, y, '20:00:00', '20', 'FALSE' 
FROM   unnest(ARRAY[3,4,5]) x
     , unnest(ARRAY[2015-4-12, 2015-4-19, 2015-4-26]) y

I get the following error:

ERROR: Column 'date' is of type date But the expression is of type integer

Erwin Brandstetter · Accepted Answer · 2020-06-28 23:41:48Z

7

An array literal is even simpler than an array constructor:

'{2015-4-12, 2015-4-19}'::date[]

With Postgres 9.4 or later, there is a safe way to unnest two arrays in parallel:

INSERT INTO schedule
      (idzone, "date", "time"    , duration, automatic) 
SELECT x     , y     , '20:00:00', 20      , false
FROM   unnest('{3, 4, 5}'::int[]
            , '{2015-4-12, 2015-4-19, 2015-4-26}'::date[]
             ) AS t (x, y)  -- produces 3 rows

See:

Unnest multiple arrays in parallel

Unless you wanted to cross join each element of one array to each element of the other to produce a Cartesian product of 9 rows. Then your original form is right.

Aside: It's good practice to never use reserved words or basic type names like "date" and "time" as identifiers.

edited Jun 28, 2020 at 23:41

answered Apr 12, 2015 at 20:58

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Daniel Vérité · Accepted Answer · 2015-04-12 18:55:53Z

4

As the error message tells, 2015-4-12 is not a date. It's the number 2015 minus the number 4 minus the number 12.

You may write an individual date as :

'2014-04-12'::date

or

 date '2015-4-12'

A shorter form for an array (avoiding individual casts) would be:

ARRAY['2015-4-12', '2015-4-19']::date[]

answered Apr 12, 2015 at 18:55

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

1 Comment

pablo masri Over a year ago

I just tested and the solution ARRAY['2015-4-12', '2015-4-19']::date[] worked perfect! Thnaks!

Collectives™ on Stack Overflow

How to unnest an ARRAY of dates in Postgres?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related