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?

user330315 · Accepted Answer · 2015-10-14 17:03:43Z

12

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.

answered Oct 14, 2015 at 17:03

user330315

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

3 Comments

user3439729 Over a year ago

Thanks for the example and explanation. That worked perfectly.

Christian Over a year ago

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

Jamie Over a year ago

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

Collectives™ on Stack Overflow

How to insert a PostgreSQL timestamp datatype into a timestamp array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related