CODEWITHSUNDEEP
arrayspostgresqlsql-timestamp
user3439729
user3439729

Reputation: 79

How to insert a PostgreSQL timestamp datatype into a timestamp array

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?

Upvotes: 5

Views: 17726

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 11

Related Questions