3

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
  • 605,456
  • 145
  • 1,078
  • 1,228
pablo masri
  • 33
  • 1
  • 4

2 Answers2

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:

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
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[]
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156