Insert date array in PostgreSQL table

Question

I have a problem inserting a dynamic array with dates into a table. I'm working with Python 3.3 and using the package psycopg2 to communicate with a Postgres 9.3 database.

I create the table with following statement:

CREATE TABLE Test( id serial PRIMARY KEY, listdate DATE[] )

En easy example is a list of two dates. Let us assume the list would be dateList = ['2014-07-07','2014-07-08'].

Now I want to insert the complete list into the table. If I try the static version:

INSERT INTO Test(dateList[1],dateList[2]) VALUES(date '2014-07-07',date '2014-07-08')"

the inserting is no problem. But in the reality my list has a dynamic number of dates (at least 100) so the static version is not useful. I tried different approaches like:

INSERT INTO Test VALUES(array" + str(dateList)  +"::date[])
INSERT INTO Test VALUES(array date '" + str(dateList)  +"')
INSERT INTO Test VALUES(date array" + str(dateList)  +")

but nothing is successful. Maybe the problem is between the two prefixes date and array.

Any ideas for a simple SQL statement without an SQL function?

I found the solution a few minutes after my post by myself. It's INSERT INTO Test(listdate) VALUES(array" + str(dateList) +"::date[]) — user3403354
– user3403354, Commented Jul 8, 2014 at 13:17

Erwin Brandstetter · Accepted Answer · 2014-07-14 02:49:03Z

3

The other, somewhat simpler option is to use the string representation of a date array:

INSERT INTO test(listdate)
VALUES ('{2014-07-07,2014-07-08}')

You can add an explicit cast, but that's not required:

INSERT INTO test(listdate)
VALUES ('{2014-07-07,2014-07-08}'::date[])

Your variable would be: dateList = '{2014-07-07,2014-07-08}' ..

edited Jul 14, 2014 at 2:49

answered Jul 14, 2014 at 2:17

Erwin Brandstetter

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

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

1 Comment

Erwin Brandstetter Over a year ago

@muistooshort: It should. Thank you.

Collectives™ on Stack Overflow

Insert date array in PostgreSQL table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related