Re: SET syntax in INSERT

On 1/14/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Vitaly Burovoy <vitaly.burovoy@gmail.com> writes:
>> On 1/14/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> It's more than syntactic sugar; you are going to have to invent
>>> semantics,
>>> as well, because it's less than clear what partial-field assignments
>>> should do.
>>>
>>> Assume a table with an int-array column, and consider
>>> INSERT INTO foo SET arraycol[2] = 7, arraycol[4] = 11;
>
>> Right part is a column name, not an expression. Isn't it?
>
> UPDATE takes this just fine.  The difference is that in UPDATE there's
> no question what the starting value of the column is.
>
>> You can't now do something like
>> INSERT INTO foo (arraycol[2], arraycol[4]) VALUES(7, 11);
>
> Hm ... actually, you might want to try that before opining.

Oops… Thank you, It's a new feature for me.
But since INSERT has that feature there is no question what to do in such case:

postgres=# create table testtable(i int[] default '{1,3,5}'::int[]);
CREATE TABLE
postgres=# insert into testtable (i[5], i[3], i[1]) values (3,5,4);
INSERT 0 1
postgres=# select * from testtable;        i
-------------------{4,NULL,5,NULL,3}
(1 row)

Save current behavior, i.e. if any array subscript is given, don't
evaluate the default!

--
Best regards,
Vitaly Burovoy