Re: SET syntax in INSERT

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:
/messages/by-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

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

2016-01-14 19:51 GMT+01:00 Robert Haas <robertmhaas@gmail.com>:

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:

/messages/by-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.

Now I am able to write more correct argument. It is one from basic SQL
statement, and for using proprietary syntax should be pretty strong reason.

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.

Regards

Pavel

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

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

SET syntax for INSERT was brought up a few years ago here:
/messages/by-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

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

2016-01-14 20:09 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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,

is it ANSI SQL redundant?

UPSERT,

the behave is partially different than MERGE, so different syntax is 100%
valid

PL/PgSQL and many other useful features.

PL/pgSQL is PL/SQL clone, and because the base is Ada, it cannot be
compatible with SQL/PSM.

Regards

Pavel

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

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

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

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

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

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

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

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

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

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

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

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

On Thu, Jan 14, 2016 at 1:07 PM, 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?

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.

David J.
​

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

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

"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

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

On Thu, Jan 14, 2016 at 1: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.

While I agree with the sentiment I'm not seeing the added risk introduced
as being a major blocker if the syntactic sugar is indeed popular and
otherwise desirable from a code maintenance standpoint. If the standard
introduces a contradictory concept that we need to address we can do so.
As we've already defined this specific behavior any conflict will likely
result in the already defined behavior changing since having the same
overall concept implemented differently for "VALUES" compared to "SET"
would be undesirable​. If we end up changing that whether we
"doubled-down" by implementing "SET" seems immaterial.

The question, then, is whether there is any behavior that needs to be
uniquely defined for SET that doesn't already come into play when using
VALUES or SELECT? I cannot think of any but given the somewhat clandestine
nature of your first example maybe you know of others.

David J.

David J.

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

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