CODEWITHSUNDEEP
postgresqlpython-3.xpsycopg2postgresql-9.3
user3403354
user3403354

Reputation: 51

Insert date array in PostgreSQL table

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?

Upvotes: 0

Views: 4837

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

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}' ..

Upvotes: 3

Related Questions