Thread: SET syntax in INSERT

Given the recent discussion of "DELETE syntax on JOINS"  I thought it
might be interesting to bring a bit MySQL syntax that is in somewhat
widespread use, generally create somewhat cleaner code and I imagine
would not break much if implemented.

MySQL allows INSERTs of the form:

INSERT INTO t SET
col1='val1',
col2='va21',
col3='val3',
col4='val4',
col5='val5',
col6='val6',
col7='val7',
col8='val8',
col9='val9',
col10='val10',
col11='val11',
col12='val12',
col13='val13',
col14='val14',
col15='val15';

Which I think sometimes compares very favorably
INSERT INTO t
(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15)
VALUES
('val1','val2','val3','val4','val5','val6','val7','val8','val9','val10','val11','val12','val13','val14','val15')

Probably a pipe dream...
-- 
Rob Wultsch
wultsch@gmail.com

2009/8/25 Rob Wultsch <wultsch@gmail.com>:
> Given the recent discussion of "DELETE syntax on JOINS"  I thought it
> might be interesting to bring a bit MySQL syntax that is in somewhat
> widespread use, generally create somewhat cleaner code and I imagine
> would not break much if implemented.
>
> MySQL allows INSERTs of the form:
>
> INSERT INTO t SET
> col1='val1',
> col2='va21',
> col3='val3',
> col4='val4',
> col5='val5',
> col6='val6',
> col7='val7',
> col8='val8',
> col9='val9',
> col10='val10',
> col11='val11',
> col12='val12',
> col13='val13',
> col14='val14',
> col15='val15';
>
> Which I think sometimes compares very favorably
> INSERT INTO t
> (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15)
> VALUES
> ('val1','val2','val3','val4','val5','val6','val7','val8','val9','val10','val11','val12','val13','val14','val15')
>
> Probably a pipe dream...

-1 PostgreSQL isn't MySQL!

Regards
Pavel Stehule


> --
> Rob Wultsch
> wultsch@gmail.com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Pavel Stehule wrote:
> 2009/8/25 Rob Wultsch <wultsch@gmail.com>:
>> Given the recent discussion of "DELETE syntax on JOINS"  I thought it
>> might be interesting to bring a bit MySQL syntax that is in somewhat
>> widespread use, generally create somewhat cleaner code and I imagine
>> would not break much if implemented.
>>
>> MySQL allows INSERTs of the form:
>>
>> INSERT INTO t SET
>> col1='val1',
>> col2='va21',
>> col3='val3',
>> col4='val4',
>> col5='val5',
>> col6='val6',
>> col7='val7',
>> col8='val8',
>> col9='val9',
>> col10='val10',
>> col11='val11',
>> col12='val12',
>> col13='val13',
>> col14='val14',
>> col15='val15';
>>
>> Which I think sometimes compares very favorably
>> INSERT INTO t
>> (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15)
>> VALUES
>> ('val1','val2','val3','val4','val5','val6','val7','val8','val9','val10','val11','val12','val13','val14','val15')
>>
>> Probably a pipe dream...
> 
> -1 PostgreSQL isn't MySQL!

Agreed, I don't see us implementing that.

I do understand the point, though - it's much easier to edit and debug
long statements when the value is close to the column name. I find that
the INSERT .. SELECT makes that a lot nicer:

INSERT INTO t
(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15)
SELECT 'val1' AS col1,      'val2' AS col2,      'val3' AS col3,      'val4' AS col4,      'val5' AS col5,      'val6'
AScol6,      'val7' AS col7,      'val8' AS col8,      'val9' AS col9,      'val10' AS col10,      'val11' AS col11,
 'val12' AS col12,      'val13' AS col13,      'val14' AS col14,      'val15' AS col15;
 

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com

On Tue, Aug 25, 2009 at 10:36 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote:
> 2009/8/25 Rob Wultsch <wultsch@gmail.com>:
>> Given the recent discussion of "DELETE syntax on JOINS"  I thought it
>> might be interesting to bring a bit MySQL syntax that is in somewhat
>> widespread use, generally create somewhat cleaner code and I imagine
>> would not break much if implemented.
>>
>> MySQL allows INSERTs of the form:
>>
>> INSERT INTO t SET
>> col1='val1',
>> col2='va21',
>> col3='val3',
>> col4='val4',
>> col5='val5',
>> col6='val6',
>> col7='val7',
>> col8='val8',
>> col9='val9',
>> col10='val10',
>> col11='val11',
>> col12='val12',
>> col13='val13',
>> col14='val14',
>> col15='val15';
>>
>> Which I think sometimes compares very favorably
>> INSERT INTO t
>> (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15)
>> VALUES
>> ('val1','val2','val3','val4','val5','val6','val7','val8','val9','val10','val11','val12','val13','val14','val15')
>>
>> Probably a pipe dream...
>
> -1 PostgreSQL isn't MySQL!
>
> Regards
> Pavel Stehule

For an insert with many columns or with large value this syntax can
significantly improve readability. So it wasn't invented here, so
what? I don't see a downside to allowing this syntax other than MySQL
used it first, and there are multiple upsides (readability, easier
transitions).

--
Rob Wultsch
wultsch@gmail.com

>
> For an insert with many columns or with large value this syntax can
> significantly improve readability. So it wasn't invented here, so
> what? I don't see a downside to allowing this syntax other than MySQL
> used it first, and there are multiple upsides (readability, easier
> transitions).
>

Insert of too much columns is signal, so your database is badly designed.

If you afraid about readability, you can you named parameters - I hope
so this feature will be early committed. It can look like:

CREATE OR REPLACE FUNCTION insert_tab(p1 varchar = NULL, p2 varchar =
NULL, p3 varchar = NULL, ...
RETURNS void AS $$ INSERT INTO tab(p1,p2,p3,p4....   VALUES($1,$2,$3,$4, ...

then you can call this procedure

SELECT insert_tab(10 as p1, 20 as p3);

regards
Pavel Stehule


> --
> Rob Wultsch
> wultsch@gmail.com
>

Rob Wultsch wrote:
>> -1 PostgreSQL isn't MySQL!
>>
>>     
>
> For an insert with many columns or with large value this syntax can
> significantly improve readability. So it wasn't invented here, so
> what? I don't see a downside to allowing this syntax other than MySQL
> used it first, and there are multiple upsides (readability, easier
> transitions).
>
>   

We don't mind things that aren't invented here at all. We have a whole 
bunch or Oracle compatibility stuff, and we adopted "DROP ... IF EXISTS 
..." from MySQL. But we do prefer to use the standard syntax for any 
feature that it supports (for example, we rejected "connect by" in favor 
of the Standard) , and we're usually not terribly big on syntactic sugar.

That said, I don't have any very strong feelings against this proposal, 
if somebody wants to put the effort into making it possible.

cheers

andrew

Heikki Linnakangas escribió:

> I do understand the point, though - it's much easier to edit and debug
> long statements when the value is close to the column name. I find that
> the INSERT .. SELECT makes that a lot nicer:
> 
> INSERT INTO t
> (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15)
> SELECT 'val1' AS col1,
>        'val2' AS col2,

This example lists the columns twice, which is lame (you have to keep
both in sync) -- and if you take the first list out it works, but the
values can end up in the wrong places if they are not in the same order
as the columns in the table.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Hi,

SET syntax for INSERT was brought up a few years ago here: 
http://www.postgresql.org/message-id/2c5ef4e30908251010s46d9d566m1da21357891bab3d@mail.gmail.com
From the discussion it seems that one committer was against, one 
committer was not against, and one committer saw something good in the 
proposal.  Personally, I believe this would be a huge readability 
improvement to INSERTs with more than a few columns.  I'm willing to put 
in some work to make this happen for 9.7, but I'd like to know that I'm 
not wasting my time.

What do we think?


.m

On Thu, Jan 14, 2016 at 12:13 PM, Marko Tiikkaja <marko@joh.to> wrote:
> SET syntax for INSERT was brought up a few years ago here:
> http://www.postgresql.org/message-id/2c5ef4e30908251010s46d9d566m1da21357891bab3d@mail.gmail.com
>
> From the discussion it seems that one committer was against, one committer
> was not against, and one committer saw something good in the proposal.
> Personally, I believe this would be a huge readability improvement to
> INSERTs with more than a few columns.  I'm willing to put in some work to
> make this happen for 9.7, but I'd like to know that I'm not wasting my time.

I'm mildly in favor of this proposal.  I think that "-1 PostgreSQL
isn't MySQL!" is maybe the lamest reason for not supporting useful
syntax that I can think of.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2016-01-14 8:06 PM, Pavel Stehule wrote:
> Probably there is less risk than 7 years ago, but still creating own syntax
> isn't the best idea. This is syntactic sugar only and different from ANSi
> SQL or common standard.

So is RETURNING, UPSERT, PL/PgSQL and many other useful features.


.m

>SET syntax for INSERT was brought up a few years ago here:
>http://www.postgresql.org/message-id/2c5ef4e30908251010s46d9d566m1da21357891bab3d@mail.gmail.com

>What do we think?

+1
this would save comments in long queries.
and usindg AS as style helper as suggested in the old post has its caveat:

create temp table t( a int,b int);

insert into t select
1 as b,
2 as a;

select * from t ...

regards,
Marc Mamin

Pavel Stehule <pavel.stehule@gmail.com> writes:
>>> Probably there is less risk than 7 years ago, but still creating own
>>> syntax isn't the best idea. This is syntactic sugar only and different
>>> from ANSi SQL or common standard.

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;

I wonder what the other elements of the array will be set to, and what
the array dimensions will end up being.

If there's a default expression for the array column, does that change
your answer?

If you say "we'll apply the default and then perform the SET assignments",
what's your criterion for deciding that you *don't* need to evaluate the
default?  If the default has side effects (think nextval()) this is a
user-visible choice.

I don't say that these questions are unresolvable, but there is certainly
more here than meets the eye; and therefore there's a nonzero chance of
being blindsided if the SQL committee someday standardizes this syntax
and makes some different decisions about what it means.
        regards, tom lane

On 2016-01-14 20:33, Tom Lane wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>>>> Probably there is less risk than 7 years ago, but still creating own
>>>> syntax isn't the best idea. This is syntactic sugar only and different
>>>> from ANSi SQL or common standard.
>
> 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.

I don't really care for such.  In my opinion it would be fine if this 
simply was only "syntactic sugar", and trying to do any tricks like this 
would simply raise an exception.


.m

On 1/14/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>>>> Probably there is less risk than 7 years ago, but still creating own
>>>> syntax isn't the best idea. This is syntactic sugar only and different
>>>> from ANSi SQL or common standard.
>
> 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?
So "arraycol[2]" is not possible there.

You can't now do something like
INSERT INTO foo (arraycol[2], arraycol[4]) VALUES(7, 11);

> I wonder what the other elements of the array will be set to, and what
> the array dimensions will end up being.
>
> If there's a default expression for the array column, does that change
> your answer?
>
> If you say "we'll apply the default and then perform the SET assignments",
> what's your criterion for deciding that you *don't* need to evaluate the
> default?  If the default has side effects (think nextval()) this is a
> user-visible choice.

Default values can be explicitly set as they are set in UPDATE:
INSERT INTO foo SET defcol = DEFAULT;

> I don't say that these questions are unresolvable, but there is certainly
> more here than meets the eye; and therefore there's a nonzero chance of
> being blindsided if the SQL committee someday standardizes this syntax
> and makes some different decisions about what it means.
>
>             regards, tom lane

Be honest I've dreamed about that syntax since I started to work with PG, so +1
-- 
Best regards,
Vitaly Burovoy

On 2016-01-14 20:50, Vitaly Burovoy wrote:
> On 1/14/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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?
> So "arraycol[2]" is not possible there.

I think the idea here was that it's allowed in UPDATE.  But I don't see 
the point of allowing that in an INSERT.


.m

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.
        regards, tom lane

On 01/14/2016 03:00 PM, Marko Tiikkaja wrote:
> On 2016-01-14 20:50, Vitaly Burovoy wrote:
>> On 1/14/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> 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?
>> So "arraycol[2]" is not possible there.
>
> I think the idea here was that it's allowed in UPDATE.  But I don't 
> see the point of allowing that in an INSERT.
>
>
>


Right. Why not just forbid anything other than a plain column name on 
the LHS for INSERT, at least as a first cut.

cheers

andrew

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

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Jan 14, 2016 at 1:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Vitaly Burovoy <vitaly.burovoy@gmail.com> writes:
>>> 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

> So what's the problem, then?  It seems like a decision has already been
> made.

Yeah, but is it a decision that we might later find to be at odds
with a future SQL standard?  The more places we embed that behavior,
the more risk we take.
        regards, tom lane

On Thu, Jan 14, 2016 at 3:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On Thu, Jan 14, 2016 at 1:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Vitaly Burovoy <vitaly.burovoy@gmail.com> writes:
>>>> 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
>
>> So what's the problem, then?  It seems like a decision has already been
>> made.
>
> Yeah, but is it a decision that we might later find to be at odds
> with a future SQL standard?  The more places we embed that behavior,
> the more risk we take.

I don't see it.  If the SQL standard committee defines foo[2] to mean
something other than array access to element 2 of foo, then we've got
a problem, but they're not going to define it different ways for
SELECT, INSERT, and UPDATE.  And even if they did, we're certainly not
going to want those to mean different and incompatible things.  So I
don't think doubling down on the syntax we already support loses
anything, really.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company