Trailing comma support in SELECT statements

[Bogdan Pilch]

Hi,
I have created a small patch to postgres source (in particular the
psql part of it) that accepts trailing comma at the end of list in
SELECT statement.

The idea is to be able to say both (with the same result):
SELECT a, b, c from t;
SELECT a, b, c, from t;

Attached you can find a patch containing regression test (incorporated
into the serial_schedule).
My patch is relative to origin/REL9_4_STABLE branch as that is the one
I started from.

My plea is to have this change merged into the main stream so that it
becomes available in upcoming releases.

This modification does not require any interaction with user.
It does not create any backward compatibility issues.
Not does it have any performance impact.

regards
bogdan

[Tom Lane]

Post by Bogdan Pilch
I have created a small patch to postgres source (in particular the
psql part of it) that accepts trailing comma at the end of list in
SELECT statement.

This doesn't seem to me to be a remarkably good idea. What's the
difference between this and accepting random misspellings of SELECT,
allowing mismatched parentheses in expressions, etc etc? It's important
in a computer language to be able to catch typos.

If we were going to be lax about trailing commas, the SELECT list
would hardly be the only candidate, or even the first candidate,
for being lax that way. But I don't want to go there.

regards, tom lane

[Bruce Momjian]

Post by Bogdan Pilch
Hi,
I have created a small patch to postgres source (in particular the
psql part of it) that accepts trailing comma at the end of list in
SELECT statement.
SELECT a, b, c from t;
SELECT a, b, c, from t;
Attached you can find a patch containing regression test (incorporated
into the serial_schedule).
My patch is relative to origin/REL9_4_STABLE branch as that is the one
I started from.
My plea is to have this change merged into the main stream so that it
becomes available in upcoming releases.
This modification does not require any interaction with user.
It does not create any backward compatibility issues.

Interesting --- I know some languages allow trailing delimiters, like
Perl and Javascript. Could this mask query errors? Does any other
database accept this? Seems this would need to be done in many other
places, like UPDATE, but let's first decide if we want this.

FYI, it is usually better to discuss a feature before showing a patch.
--
Bruce Momjian <***@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

[Andrew Dunstan]

Post by Bruce Momjian

Post by Bogdan Pilch
Hi,
I have created a small patch to postgres source (in particular the
psql part of it) that accepts trailing comma at the end of list in
SELECT statement.
SELECT a, b, c from t;
SELECT a, b, c, from t;
Attached you can find a patch containing regression test (incorporated
into the serial_schedule).
My patch is relative to origin/REL9_4_STABLE branch as that is the one
I started from.
My plea is to have this change merged into the main stream so that it
becomes available in upcoming releases.
This modification does not require any interaction with user.
It does not create any backward compatibility issues.

Interesting --- I know some languages allow trailing delimiters, like
Perl and Javascript. Could this mask query errors? Does any other
database accept this? Seems this would need to be done in many other
places, like UPDATE, but let's first decide if we want this.
FYI, it is usually better to discuss a feature before showing a patch.

Javascript might accept it, but it's not valid JSON.

The case for doing it is that then you can easily comment out any entry
at all in a select list:

select
foo as f1,
bar as f2,
-- baz as f3,
from blurfl


cheers

andrew

[David G Johnston]

Post by Andrew Dunstan

Post by Bruce Momjian

Post by Bogdan Pilch
Hi,
I have created a small patch to postgres source (in particular the
psql part of it) that accepts trailing comma at the end of list in
SELECT statement.
SELECT a, b, c from t;
SELECT a, b, c, from t;
Attached you can find a patch containing regression test (incorporated
into the serial_schedule).
My patch is relative to origin/REL9_4_STABLE branch as that is the one
I started from.
My plea is to have this change merged into the main stream so that it
becomes available in upcoming releases.
This modification does not require any interaction with user.
It does not create any backward compatibility issues.

Interesting --- I know some languages allow trailing delimiters, like
Perl and Javascript. Could this mask query errors? Does any other
database accept this? Seems this would need to be done in many other
places, like UPDATE, but let's first decide if we want this.
FYI, it is usually better to discuss a feature before showing a patch.

Javascript might accept it, but it's not valid JSON.
The case for doing it is that then you can easily comment out any entry
select
foo as f1,
bar as f2,
-- baz as f3,
from blurfl

Should we also allow:

SELECT
, col1
, col2
, col3
FROM ...

?

The other reason for this would be to build dynamic SQL more easily via a
loop.

Barring arguments showing danger allowing I don't see a reason to reject
this; let people decide whether they want to utilize it on stylistic or
compatibility grounds.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Trailing-comma-support-in-SELECT-statements-tp5821613p5821694.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[Jim Nasby]

Post by Bogdan Pilch
SELECT
, col1
, col2
, col3
FROM ...
?

I would say yes, if we're going to do this. I don't see it being any worse than trailing commas.

If we are going to do this, we need to do it EVERYWHERE.

FWIW, the way I normally "work around" this problem is:

SELECT
blah
, foo
, bar
, baz

In my experience, it's quite uncommon to mess with the first item in the list, which mostly eliminates the issue. A missing leading comma is also MUCH easier to spot than a missing trailing comma.

[David G Johnston]

Post by Jim Nasby

Post by Bogdan Pilch
SELECT
, col1
, col2
, col3
FROM ...
?

I would say yes, if we're going to do this. I don't see it being any worse
than trailing commas.
If we are going to do this, we need to do it EVERYWHERE.
SELECT
blah
, foo
, bar
, baz
In my experience, it's quite uncommon to mess with the first item in the
list, which mostly eliminates the issue. A missing leading comma is also
MUCH easier to spot than a missing trailing comma.
--
Sent via pgsql-hackers mailing list (
)
http://www.postgresql.org/mailpref/pgsql-hackers

Post by Bogdan Pilch
SELECT
, col1
, col2
, col3
FROM ...
?

I would say yes, if we're going to do this. I don't see it being any worse
than trailing commas.
If we are going to do this, we need to do it EVERYWHERE.
SELECT
blah
, foo
, bar
, baz
In my experience, it's quite uncommon to mess with the first item in the
list, which mostly eliminates the issue. A missing leading comma is also
MUCH easier to spot than a missing trailing comma.

We might as well allow a final trailing (or initial leading) comma on a
values list at the same time:

VALUES
(...),
(...),
(...),
;


David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Trailing-comma-support-in-SELECT-statements-tp5821613p5823365.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[Pavel Stehule]

Post by David G Johnston

Post by Jim Nasby

Post by Bogdan Pilch
SELECT
, col1
, col2
, col3
FROM ...
?

I would say yes, if we're going to do this. I don't see it being any

worse

Post by Jim Nasby
than trailing commas.
If we are going to do this, we need to do it EVERYWHERE.
SELECT
blah
, foo
, bar
, baz
In my experience, it's quite uncommon to mess with the first item in the
list, which mostly eliminates the issue. A missing leading comma is also
MUCH easier to spot than a missing trailing comma.

do you know, so this feature is a proprietary and it is not based on
ANSI/SQL? Any user, that use this feature and will to port to other
database will hate it.

Regards

Pavel

Post by David G Johnston

Post by Jim Nasby
--
Sent via pgsql-hackers mailing list (
)
http://www.postgresql.org/mailpref/pgsql-hackers

Post by Bogdan Pilch
SELECT
, col1
, col2
, col3
FROM ...
?

I would say yes, if we're going to do this. I don't see it being any

worse

Post by Jim Nasby
than trailing commas.
If we are going to do this, we need to do it EVERYWHERE.
SELECT
blah
, foo
, bar
, baz
In my experience, it's quite uncommon to mess with the first item in the
list, which mostly eliminates the issue. A missing leading comma is also
MUCH easier to spot than a missing trailing comma.

We might as well allow a final trailing (or initial leading) comma on a
VALUES
(...),
(...),
(...),
;
David J.
--
http://postgresql.1045698.n5.nabble.com/Trailing-comma-support-in-SELECT-statements-tp5821613p5823365.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-hackers

[David Johnston]

Post by Pavel Stehule
​
​

Post by David G Johnston
We might as well allow a final trailing (or initial leading) comma on a
VALUES
(...),
(...),
(...),

​
do you know, so this feature is a proprietary and it is not based on
ANSI/SQL? Any user, that use this feature and will to port to other
database will hate it.
Regards
Pavel
​

​I've got no complaint if "at the same time" means that neither behavior is
ever implemented...

David J.
​

[Jim Nasby]

Post by David G Johnston
We might as well allow a final trailing (or initial leading) comma on a

<snip>

Post by David G Johnston
do you know, so this feature is a proprietary and it is not based on ANSI/SQL? Any user, that use this feature and will to port to other database will hate it.
​I've got no complaint if "at the same time" means that neither behavior is ever implemented...

As I originally posted, if we're going to do this I think we should do it *EVERYWHERE* commas are used as delimiters, save COPY input and output. Or we should at least get close to doing it everywhere. I think the only way things could get more annoying is if we accepted extra commas in SELECT but not in CREATE TABLE (as one example).

To me completeness is more important than whether we do it or not; that said, I like the idea (as well as supporting leading extra commas).

[Tom Lane]

Post by Jim Nasby
As I originally posted, if we're going to do this I think we should do it *EVERYWHERE* commas are used as delimiters, save COPY input and output. Or we should at least get close to doing it everywhere. I think the only way things could get more annoying is if we accepted extra commas in SELECT but not in CREATE TABLE (as one example).
To me completeness is more important than whether we do it or not; that said, I like the idea (as well as supporting leading extra commas).

Yeah, exactly. Personally I'm *not* for this, but if we do it we should
do it consistently: every comma-separated list in the SQL syntax should
work the same.

regards, tom lane

[David E. Wheeler]

Post by Tom Lane
Yeah, exactly. Personally I'm *not* for this, but if we do it we should
do it consistently: every comma-separated list in the SQL syntax should
work the same.

PL/pgSQL, too, I presume.

D

[Jim Nasby]

Post by David E. Wheeler

Post by Tom Lane
Yeah, exactly. Personally I'm *not* for this, but if we do it we should
do it consistently: every comma-separated list in the SQL syntax should
work the same.

PL/pgSQL, too, I presume.

Yes.

The only case I can think of where we wouldn't want this is COPY.

BTW, this should also apply to delimiters other than commas; for example, some geometry types use ; as a delimiter between points.

I do think such a change should be made in stages, and maybe not every last one makes it into 9.5, but the intention should certainly be that we support extra delimiters *everywhere*.

[David E. Wheeler]

Post by Jim Nasby
Yes.
The only case I can think of where we wouldn't want this is COPY.
BTW, this should also apply to delimiters other than commas; for example, some geometry types use ; as a delimiter between points.

I don’t think it should apply to the internals of types, necessarily. JSON, for example, always dies on an trailing comma, so should probably stay that way. Well, maybe allow it on JSONB input, but not JSON. Though we perhaps don’t want their behaviors to diverge.

D

[Andrew Dunstan]

Post by Jim Nasby
Yes.
The only case I can think of where we wouldn't want this is COPY.
BTW, this should also apply to delimiters other than commas; for example, some geometry types use ; as a delimiter between points.

I don’t think it should apply to the internals of types, necessarily. JSON, for example, always dies on an trailing comma, so should probably stay that way. Well, maybe allow it on JSONB input, but not JSON. Though we perhaps don’t want their behaviors to diverge.

The JSON spec is quite clear on this. Leading and trailing commas are
not allowed. I would fight tooth and nail not to allow it for json (and
by implication jsonb, since they use literally the same parser - in fact
we do that precisely so their input grammars can't diverge).

cheers

andrew

[Jim Nasby]

Post by Jim Nasby
Yes.
The only case I can think of where we wouldn't want this is COPY.
BTW, this should also apply to delimiters other than commas; for example, some geometry types use ; as a delimiter between points.

I don’t think it should apply to the internals of types, necessarily. JSON, for example, always dies on an trailing comma, so should probably stay that way. Well, maybe allow it on JSONB input, but not JSON. Though we perhaps don’t want their behaviors to diverge.

The JSON spec is quite clear on this. Leading and trailing commas are not allowed. I would fight tooth and nail not to allow it for json (and by implication jsonb, since they use literally the same parser - in fact we do that precisely so their input grammars can't diverge).

+1. Data types that implement specs should follow the spec.

I was more concerned about things like polygon, but the real point (ha!) is that we need to think about the data types too. (I will say I don't think things that mandate an exact number of elements (like point, box, etc) should support extra delimiters).

[Tom Lane]

Post by Jim Nasby

The JSON spec is quite clear on this. Leading and trailing commas are not allowed. I would fight tooth and nail not to allow it for json (and by implication jsonb, since they use literally the same parser - in fact we do that precisely so their input grammars can't diverge).

+1. Data types that implement specs should follow the spec.
I was more concerned about things like polygon, but the real point (ha!) is that we need to think about the data types too. (I will say I don't think things that mandate an exact number of elements (like point, box, etc) should support extra delimiters).

I'm pretty strongly against this, as it would create cross-version hazards
for data. Having queries that depend on newer-version SQL features is
something that people are used to coping with ... but data that loads into
some versions and not others seems like a hassle we do not need to invent.

(Of course, I'm not for the feature w.r.t. SQL either. But breaking data
compatibility is just adding an entire new dimension of trouble.)

regards, tom lane

[Kevin Grittner]

Post by Pavel Stehule
do you know, so this feature is a proprietary and it is not based
on ANSI/SQL? Any user, that use this feature and will to port to
other database will hate it.

I remember that Sybase ASE allowed a trailing comma within the
parentheses of a table definition, which was handy. I checked on
SQL Fiddle and found that MS SQL Server and MySQL both allow that,
too; although Oracle does not. I'm not taking a position on
whether we should allow this in PostgreSQL, but not having it is
likely to annoy some users moving *to* PostgreSQL, while having it
is likely to annoy some users moving *away* from PostgreSQL.

None of the products I tried allowed a leading comma.

I didn't test, and have no knowledge regarding, how other products
treat extra commas elsewhere.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[Pavel Stehule]

Post by Bogdan Pilch
Hi,
I have created a small patch to postgres source (in particular the
psql part of it) that accepts trailing comma at the end of list in
SELECT statement.

It is ANSI/SQL ?

Why we should to enable? We can be tolerant to this bug, but then
developers will hate us, when they will try to port to other servers.

-1 from me

Regards

Pavel

Post by Bogdan Pilch
SELECT a, b, c from t;
SELECT a, b, c, from t;
Attached you can find a patch containing regression test (incorporated
into the serial_schedule).
My patch is relative to origin/REL9_4_STABLE branch as that is the one
I started from.
My plea is to have this change merged into the main stream so that it
becomes available in upcoming releases.
This modification does not require any interaction with user.
It does not create any backward compatibility issues.
Not does it have any performance impact.
regards
bogdan
--
http://www.postgresql.org/mailpref/pgsql-hackers