How to insert a PostgreSQL timestamp datatype into a timestamp array

Question

I have a table containing a array of timestamps, like the following:

CREATE TABLE start_times
(
start_date timestamp[]
);

I am not sure how to insert the timestamp values into the array. I read in a article that I should use double quotes, instead of single quotes when inserting a timestamp into a array, like such:

INSERT INTO start_times VALUES (ROW('{{"10-JAN-15 12.51.14.340358000 AM"},{"11-JAN-15 12.51.14.340358000 AM"}}'));

However, when I tried that I got the following error:

ERROR: invalid input syntax for type timestamp: "10-JAN-15 12.51.14.340358000 AM"
SQL state: 22007
Character: 165

Can someone tell me how I can insert timestamp values into the timestamp array?

Kratz

Answer 1

Using the ARRAY keyword frees you of the necessity to quote every element, allowing to use single quotes as needed. And using ANSI timestamp literals makes it a lot easier as well:

INSERT INTO start_times 
VALUES 
(array[timestamp '2015-01-10 00:51:14', timestamp '2015-01-11 00:51:14']);

This will work regardless of the current locale settings.

Hippocrene

Thanks for the example and explanation. That worked perfectly. – Kratz

@user3439729: if that worked, please accept then the answer. – Loveliesbleeding

It doesn't look like the answer will be accepted. It's like a panda with bad punctuation. He eats, shoots, and leaves. – Droit