Thread: last comma inside "CREATE TABLE ()" statements
Is it against the SQL standard to accept a trailing comma in a table
declaration?
CREATE TABLE "currency" (
currency_id varchar(3),
rate float, <-------------------- BOOM! parse error
);
As in perl, it would make life easier to simply ignore/accept a trailing
comma on table declarations.
--
PHEDRE: Insensée, où suis-je ? et qu'ai-je dit ?
Où laissé-je égarer mes voeux et mon esprit ?
(Phèdre, J-B Racine, acte 1, scène 3)
On Sun, 22 Apr 2001, Louis-David Mitterrand wrote: > Is it against the SQL standard to accept a trailing comma in a table > declaration? Yes it is. > CREATE TABLE "currency" ( > currency_id varchar(3), > rate float, <-------------------- BOOM! parse error > ); > > As in perl, it would make life easier to simply ignore/accept a trailing > comma on table declarations. Yeah it would, wouldn't it? ;) -laex
Louis-David Mitterrand <vindex@apartia.ch> writes:
> Is it against the SQL standard to accept a trailing comma in a table
> declaration?
Yes ...
> CREATE TABLE "currency" (
> currency_id varchar(3),
> rate float, <-------------------- BOOM! parse error
> );
> As in perl, it would make life easier to simply ignore/accept a trailing
> comma on table declarations.
... however, this seems like a reasonable idea that would not introduce
any major problems. I have no objections, if someone wants to submit
a grammar patch.
regards, tom lane
Tom Lane wrote: >Louis-David Mitterrand <vindex@apartia.ch> writes: >> Is it against the SQL standard to accept a trailing comma in a table >> declaration? > >Yes ... > >> CREATE TABLE "currency" ( >> currency_id varchar(3), >> rate float, <-------------------- BOOM! parse error >> ); > >> As in perl, it would make life easier to simply ignore/accept a trailing >> comma on table declarations. > >... however, this seems like a reasonable idea that would not introduce >any major problems. I have no objections, if someone wants to submit >a grammar patch. I suppose it isn't a major problem, but enforcing strict grammar helps to show up inadvertent errors. Suppose I have a set of schema building files for a whole system; the way I do things, there may be fifty or more files, one per table. If one of these gets corrupted in editing (perhaps a line gets deleted by mistake) it would be nice to know about it through a parser error. Of course, an error may be such that the parser won't detect it, but why remove protection by gratuitously departing from the standard? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land." II Chronicles 7:14
> I suppose it isn't a major problem, but enforcing strict grammar
> helps to show up inadvertent errors. Suppose I have a set of schema
> building files for a whole system; the way I do things, there may be fifty
> or more files, one per table. If one of these gets corrupted in editing
> (perhaps a line gets deleted by mistake) it would be nice to know about it
> through a parser error. Of course, an error may be such that the parser
> won't detect it, but why remove protection by gratuitously departing from
> the standard?
I agree -- while it would be a huge problem, it's a matter of following
the rules.. I don't see any reason why we can't expect users to follow the
proper syntax rules.. I missed the first post so I don't know how the person
who posted this was actually putting the comma there -- perhaps there was a
good reason for it..
Just my $0.02 worth..
-Mitch
On Sun, Apr 22, 2001 at 07:44:46PM +0100, some SMTP stream spewed forth: > Tom Lane wrote: > >Louis-David Mitterrand <vindex@apartia.ch> writes: > >> Is it against the SQL standard to accept a trailing comma in a table > >> declaration? > > > >Yes ... > > > >> CREATE TABLE "currency" ( > >> currency_id varchar(3), > >> rate float, <-------------------- BOOM! parse error > >> ); > > > >> As in perl, it would make life easier to simply ignore/accept a trailing > >> comma on table declarations. > > > >... however, this seems like a reasonable idea that would not introduce > >any major problems. I have no objections, if someone wants to submit > >a grammar patch. I can think of no place where this would be even remotely useful. Is it really that difficult to remove a comma? If it works now for *all* users of PostgreSQL, why should we change it? gh > > I suppose it isn't a major problem, but enforcing strict grammar > helps to show up inadvertent errors. Suppose I have a set of schema > building files for a whole system; the way I do things, there may be fifty > or more files, one per table. If one of these gets corrupted in editing > (perhaps a line gets deleted by mistake) it would be nice to know about it > through a parser error. Of course, an error may be such that the parser > won't detect it, but why remove protection by gratuitously departing from > the standard? > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight http://www.lfix.co.uk/oliver > PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "If my people, which are called by my name, shall > humble themselves, and pray, and seek my face, and > turn from their wicked ways; then will I hear from > heaven, and will forgive their sin, and will heal > their land." II Chronicles 7:14 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
On Sun, Apr 22, 2001 at 02:48:36PM -0400, Mitch Vincent wrote:
> > I suppose it isn't a major problem, but enforcing strict grammar
> > helps to show up inadvertent errors. Suppose I have a set of schema
> > building files for a whole system; the way I do things, there may be fifty
> > or more files, one per table. If one of these gets corrupted in editing
> > (perhaps a line gets deleted by mistake) it would be nice to know about it
> > through a parser error. Of course, an error may be such that the parser
> > won't detect it, but why remove protection by gratuitously departing from
> > the standard?
>
> I agree -- while it would be a huge problem, it's a matter of following
> the rules.. I don't see any reason why we can't expect users to follow the
> proper syntax rules.. I missed the first post so I don't know how the person
> who posted this was actually putting the comma there -- perhaps there was a
> good reason for it..
I've run into this several times, and I agree with those who'd like to see
this added. When creating/editing a schema, I usually use this format:
CREATE TABLE t1 (
foo TEXT,
bar TEXT
);
Now let's say I wanted to add another column:
CREATE TABLE t2 (
foo TEXT,
bar TEXT,
baz TEXT
);
I would need to edit 2 lines (to add the trailing comma, and then the
next line). The same applies when deleting the last column in a table.
It would be easier, IMHO, to allow this:
CREATE TABLE t1 (
foo TEXT,
bar TEXT,
);
So that you can easily add or delete columns without needing to worry
about commas.
As for following standards, I think we should aim to provide the best
RDBMS possible. Being SQL-compliant is valuable, so we should do that.
But in areas where we can extend the standard, or break it in a minor
way a for major gain, I think the benefits outweigh the costs.
And really, how many errors is this going to prevent? AFAICT, it would
only catch the case where you've deleted the last column in a table
accidentally -- it won't catch mistakes anywhere else. This doesn't
seem to be very useful.
Of course, that's just my opinion. I might be wrong ;-)
Cheers,
Neil
--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
Violence is to dictatorship as propaganda is to democracy.
-- Noam Chomsky
On Sun, Apr 22, 2001 at 07:44:46PM +0100, Oliver Elphick wrote: > Tom Lane wrote: > >Louis-David Mitterrand <vindex@apartia.ch> writes: > >> Is it against the SQL standard to accept a trailing comma in a table > >> declaration? > > > >Yes ... > > > >> CREATE TABLE "currency" ( > >> currency_id varchar(3), > >> rate float, <-------------------- BOOM! parse error > >> ); > > > >> As in perl, it would make life easier to simply ignore/accept a trailing > >> comma on table declarations. > > > >... however, this seems like a reasonable idea that would not introduce > >any major problems. I have no objections, if someone wants to submit > >a grammar patch. > > I suppose it isn't a major problem, but enforcing strict grammar > helps to show up inadvertent errors. Suppose I have a set of schema > building files for a whole system; the way I do things, there may be fifty > or more files, one per table. If one of these gets corrupted in editing > (perhaps a line gets deleted by mistake) it would be nice to know about it > through a parser error. Of course, an error may be such that the parser > won't detect it, but why remove protection by gratuitously departing from > the standard? After giving it a thought, this seems like a reasonable objection. My comparison with perl (a trailing comma is accepted in list definitions) isn't entirely valid as perl is a programming language (doh) in which most time is spent editing. On the other hand, building a DB schema more planning and reflexion (hopefully) and less editing. Removing a trailing comma isn't going to give increase anybody's carpal tunnel syndrome. [crawls back into hole] > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight http://www.lfix.co.uk/oliver > PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "If my people, which are called by my name, shall > humble themselves, and pray, and seek my face, and > turn from their wicked ways; then will I hear from > heaven, and will forgive their sin, and will heal > their land." II Chronicles 7:14 -- Religion is something left over from the infancy of our intelligence; it will fade away as we adopt reason and science as our guidelines. -- Bertrand Russell
On Sunday 22 April 2001 10:21, you wrote: > .... however, this seems like a reasonable idea that would not introduce > any major problems. I have no objections, if someone wants to submit > a grammar patch. > > regards, tom lane Please don't. IMHO This would be an unnecessary 'extension' to postgresql that would allow scipts and a syntax that will generate errors when used on other DB's. We have moved all our projects from an Oracle 8 environment to postgress and really appreciate the flexibility, and quality without the undue complexity of running Oracle. We were able to do this fairly easily (about 1 day for half a dozen databases/applications each with about 5gig data) because our schema scripts ran without error the first time. By permitting sloppy syntax 'portability' could become 'no-so-portable' . Yes we could just make sure that we avoid sloppy syntax but it is great to have the parser say 'hey that was wrong, fix it and I won't complain again'. I'm more than happy to fix it now and know that I can use it later without hassle. Just my 2 (okay maybe 3) cents. Mike. =================== Mike Finn Tactical Executive Systems mike.finn@tacticalExecutive.com
the original request was to allow extra commas such as perl does
-- for example:
create table xyz (
f1 int4,
t1 text,
-- v1 varchar(25),
s1 serial, -- note extra trailing comma
);
select
v.fld1,
-- t.fld2,
v.fldN, -- extra comma
from
tbl1 t,
view1 v, -- extra comma
where
...
PLUSSES:
1 it's easier to cut & paste whole lines
2 it's easier to comment out any line
3 allows quicker munging of source code, saving precious
programmer time (big, Big, BIG plus, in the long run)
MINUSES:
1 requires some tweaks to postgres source code
2 nonstandard -- rubs philosophical purists the wrong way
3 increases iterations through code as extra commas are added
or removed (when lines of sql code are moved around), eating
up programmer time
IMHO:
let the purists code strict ISO if they like; we're not
recommending that the current paradigm be CHANGED to allow extra
commas, only that a new one be ADDED to allow extra commas.
regarding nonstandard -- we wouldn't ditch "create rule" or
"inherits" just because the standards folk haven't learned to
appreciate them yet, would we?
i've secretly wanted this feature for months and months, but i
didn't have the globs to ask for it. :)
can we vote? (no florida jokes, please...)
--
note:
in "CREATE TABLE" you can specify a primary key at the end of
your field defs, to accomplish just about the same effect:
create table thisway (
code char(6),
name varchar(20),
amt float8,
other int4, -- pseudo extra comma
PRIMARY KEY( code )
);
of course, this approach works only for table creation.
--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
Gah.. just put comma's at the beginning...
SELECT bleah
, blah
, otherthing
FROM arghh
, feh
, fah
WHERE ( blah in ('1'
, '2'
, '3')
OR otherthing IS TRUE
)
OR bleah IS FALSE
Oh, and indent nicer. You can remove virtually any line (except the
ones with commands in them) without any issues.
--
Rod Taylor
There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "will trillich" <will@serensoft.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, April 26, 2001 10:12 AM
Subject: Re: [GENERAL] last comma inside "CREATE TABLE ()" statements
> the original request was to allow extra commas such as perl does
> -- for example:
>
> create table xyz (
> f1 int4,
> t1 text,
> -- v1 varchar(25),
> s1 serial, -- note extra trailing comma
> );
> select
> v.fld1,
> -- t.fld2,
> v.fldN, -- extra comma
> from
> tbl1 t,
> view1 v, -- extra comma
> where
> ...
>
> PLUSSES:
>
> 1 it's easier to cut & paste whole lines
>
> 2 it's easier to comment out any line
>
> 3 allows quicker munging of source code, saving precious
> programmer time (big, Big, BIG plus, in the long run)
>
> MINUSES:
>
> 1 requires some tweaks to postgres source code
>
> 2 nonstandard -- rubs philosophical purists the wrong way
>
> 3 increases iterations through code as extra commas are added
> or removed (when lines of sql code are moved around), eating
> up programmer time
>
> IMHO:
>
> let the purists code strict ISO if they like; we're not
> recommending that the current paradigm be CHANGED to allow extra
> commas, only that a new one be ADDED to allow extra commas.
>
> regarding nonstandard -- we wouldn't ditch "create rule" or
> "inherits" just because the standards folk haven't learned to
> appreciate them yet, would we?
>
> i've secretly wanted this feature for months and months, but i
> didn't have the globs to ask for it. :)
>
> can we vote? (no florida jokes, please...)
>
> --
>
> note:
>
> in "CREATE TABLE" you can specify a primary key at the end of
> your field defs, to accomplish just about the same effect:
>
> create table thisway (
> code char(6),
> name varchar(20),
> amt float8,
> other int4, -- pseudo extra comma
>
> PRIMARY KEY( code )
> );
>
> of course, this approach works only for table creation.
>
> --
> don't visit this page. it's bad for you. take my expert word for it.
> http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html
>
> will@serensoft.com
> http://sourceforge.net/projects/newbiedoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
On Thu, Apr 26, 2001 at 10:38:42AM -0400, Rod Taylor wrote:
> Gah.. just put comma's at the beginning...
Oh, now THAT's intuitive:
. To be
, or not to be
, that is the question
. Whether 'tis nobler...
Charming. :)
> SELECT bleah
> , blah
> , otherthing
> FROM arghh
> , feh
> , fah
> WHERE ( blah in ('1'
> , '2'
> , '3')
> OR otherthing IS TRUE
> )
> OR bleah IS FALSE
>
> Oh, and indent nicer. You can remove virtually any line (except the
> ones with commands in them) without any issues.
What that does, is it transfers the location of the problem. Now
the comma is effectively in FRONT of most terms, except the
FIRST.
An alternative compromise:
select
first
,
second
,
third
,
fourth
from
alpha
,
bravo
,
charlie
;
It's odd to use a whole line just for a florkin' comma, but in vi
"2ddkkP" or "2ddjjjjP" will rearrange things nicely, while
keeping the purists at bay (not to mention any names, but You
Know Who You Are :).
I'd still prefer to ALLOW (but not DEMAND) 'empty after last
comma'. Or if you're determined to go for 'empty before first
comma':
update tbl
set
,one = something
,two = something-else
,three = fn('hgttg',42)
,four = that
;
But i hope you'll agree that this is more obtuse than we need to
be. Not to mention the "speedbump" effect it'll have on the person
who's got to look over your code next month.
This looks much nicer, imho --
update tbl
set
one = something ,
two = something-else ,
three = fn('hgttg',42) ,
four = that ,
;
After all, the comma is of no importance to the conceptual task
we're after: i don't care if there's a token separating those
assignments -- i'm interested in the fields and the values being
assigned to them. The commas are just there to help us predict
that the compiler will understand what we're after.
And it's easy to rearrange those lines in a text editor without
having to be paranoid about "Do i need to add a comma somewhere?
Should i look to see if i should take one out?"
Computers should work. People should think. "Data! Mow the lawn!"
--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
Heh.. Actually, those queries look quite good if you centre them in a
page -- Assuming all characters are the same width anyway. SELECT,
FROM, and other key words go onto the left column along with comma's,
and the relevant database columns, tables, and where clauses go on the
right. With a good naming convention I don't even have to look at the
left hand side of the query but rather just the list of entities on
the right. It also means every line has a left side and a right side.
Anyway, not that it matters much but If the loose grammar is
implemented it should be optional and off by default.
--
Rod Taylor
There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "will trillich" <will@serensoft.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, April 27, 2001 12:56 AM
Subject: Re: [GENERAL] last comma inside "CREATE TABLE ()" statements
> On Thu, Apr 26, 2001 at 10:38:42AM -0400, Rod Taylor wrote:
> > Gah.. just put comma's at the beginning...
>
> Oh, now THAT's intuitive:
>
> . To be
> , or not to be
> , that is the question
> . Whether 'tis nobler...
>
> Charming. :)
>
> > SELECT bleah
> > , blah
> > , otherthing
> > FROM arghh
> > , feh
> > , fah
> > WHERE ( blah in ('1'
> > , '2'
> > , '3')
> > OR otherthing IS TRUE
> > )
> > OR bleah IS FALSE
> >
> > Oh, and indent nicer. You can remove virtually any line (except
the
> > ones with commands in them) without any issues.
>
> What that does, is it transfers the location of the problem. Now
> the comma is effectively in FRONT of most terms, except the
> FIRST.
>
> An alternative compromise:
>
> select
> first
> ,
> second
> ,
> third
> ,
> fourth
> from
> alpha
> ,
> bravo
> ,
> charlie
> ;
>
> It's odd to use a whole line just for a florkin' comma, but in vi
> "2ddkkP" or "2ddjjjjP" will rearrange things nicely, while
> keeping the purists at bay (not to mention any names, but You
> Know Who You Are :).
>
> I'd still prefer to ALLOW (but not DEMAND) 'empty after last
> comma'. Or if you're determined to go for 'empty before first
> comma':
>
> update tbl
> set
> ,one = something
> ,two = something-else
> ,three = fn('hgttg',42)
> ,four = that
> ;
>
> But i hope you'll agree that this is more obtuse than we need to
> be. Not to mention the "speedbump" effect it'll have on the person
> who's got to look over your code next month.
>
> This looks much nicer, imho --
>
> update tbl
> set
> one = something ,
> two = something-else ,
> three = fn('hgttg',42) ,
> four = that ,
> ;
>
> After all, the comma is of no importance to the conceptual task
> we're after: i don't care if there's a token separating those
> assignments -- i'm interested in the fields and the values being
> assigned to them. The commas are just there to help us predict
> that the compiler will understand what we're after.
>
> And it's easy to rearrange those lines in a text editor without
> having to be paranoid about "Do i need to add a comma somewhere?
> Should i look to see if i should take one out?"
>
> Computers should work. People should think. "Data! Mow the lawn!"
>
> --
> don't visit this page. it's bad for you. take my expert word for it.
> http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html
>
> will@serensoft.com
> http://sourceforge.net/projects/newbiedoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
On Fri, Apr 27, 2001 at 07:40:50AM -0400, Rod Taylor wrote: > Anyway, not that it matters much but If the loose grammar is > implemented it should be optional and off by default. sure -- 100% agreed! -- will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!