Re: 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(at)gmail(dot)com

2009/8/25 Rob Wultsch <wultsch(at)gmail(dot)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(at)gmail(dot)com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Pavel Stehule wrote:
> 2009/8/25 Rob Wultsch <wultsch(at)gmail(dot)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' AS col6,
'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(dot)stehule(at)gmail(dot)com> wrote:
> 2009/8/25 Rob Wultsch <wultsch(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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.