Insert In PostrgeSQL array without first retrieving the array

Question

I have a column in PostgreSQL Table

modification_dates timestamp without time zone[]

I need to be able to add a date to the array. The easy way would be to get the array and add element then rewrite it.

Another way would be to get the array , count it and use the following syntax to set the last value.

UPDATE my_table SET timestamp [countOfArray] = newDate;

Is there a way to do it with one SQL Statement?

wonderb0lt · Accepted Answer · 2015-05-27 11:39:07Z

3

You can use the concatenation operator or array_cat, as per the postgres documentation:

UPDATE my_table SET timestamp = timestamp || newDate;

or

UPDATE my_table SET timestamp = array_cat(timestamp, ARRAY[newDate]);

where the preferred way is the former. Adding a condition will work just as it does in any other update query.

answered May 27, 2015 at 11:39

wonderb0lt

2,0531 gold badge25 silver badges37 bronze badges

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

Comments

user330315 · Accepted Answer · 2015-05-27 11:38:45Z

1

To append a new value to the array use the standard SQL concatenation operator:

update my_table
   set modification_dates = modification_dates||current_timestamp
where ...;

You can't insert a new element in between existing elements.

answered May 27, 2015 at 11:38

user330315