Thread: Column COMMENTs in CREATE TABLE?

Column COMMENTs in CREATE TABLE?

From

Marko Tiikkaja

Date:

02 July 2016, 16:55:19

Hi,

Currently we have CREATE TABLE statements in a git repository that look 
roughly like this:

CREATE TABLE foo(  -- the first field  f1 int NOT NULL,  -- the second field  f2 int NOT NULL,
...
);

But the problem is that those comments don't obviously make it all the 
way to the database, so e.g.  \d+ tblname  won't show you that precious 
information.  If you want them to make it all the way to the database, 
you'd have to add COMMENT ON statements *after* the CREATE TABLE, which 
means that either column comments have to be maintained twice, or the 
CREATE TABLE statement won't have them, so you have to go back and forth 
in your text editor to see the comments.  Both solutions are suboptimal.

What I would prefer is something like this:

CREATE TABLE foo(  f1 int NOT NULL COMMENT    'the first field',  f2 int NOT NULL COMMENT    'the second field',
...
);

which would ensure the comments are both next to the field definition 
they're documenting and that they make it all the way to the database. 
I looked into the biggest products, and MySQL supports this syntax.  I 
couldn't find any similar syntax in any other product.

The downside is that this would require us to make COMMENT a fully 
reserved keyword, which would quite likely break at least one 
application out in the wild.  Another option would be to make the syntax 
something like  [ COLUMN COMMENT '...' ], but that's not exactly a 
beautiful solution either.

I still think this would be a really valuable feature if we can come up 
with a decent syntax for it.  Does anyone have any ideas?  Or does 
anybody want to shoot this proposal down right off the bat?


.m

Re: Column COMMENTs in CREATE TABLE?

From

"David G. Johnston"

Date:

02 July 2016, 17:06:54

On Sat, Jul 2, 2016 at 12:55 PM, Marko Tiikkaja <marko@joh.to> wrote:

What I would prefer is something like this:

CREATE TABLE foo(
f1 int NOT NULL COMMENT
'the first field',
f2 int NOT NULL COMMENT
'the second field',
...
);

which would ensure the comments are both next to the field definition they're documenting and that they make it all the way to the database. I looked into the biggest products, and MySQL supports this syntax. I couldn't find any similar syntax in any other product.

+1 for the idea - though restricting it to columns would not be ideal.

CREATE TABLE name

COMMENT IS

'Table Comment Here'

(

col1 serial COMMENT IS 'Place comment here'

);

David J.

Re: Column COMMENTs in CREATE TABLE?

From

Fabrízio de Royes Mello

Date:

03 July 2016, 00:32:08

Em sábado, 2 de julho de 2016, David G. Johnston <david.g.johnston@gmail.com> escreveu:

On Sat, Jul 2, 2016 at 12:55 PM, Marko Tiikkaja <marko@joh.to> wrote:

What I would prefer is something like this:

CREATE TABLE foo(
f1 int NOT NULL COMMENT
'the first field',
f2 int NOT NULL COMMENT
'the second field',
...
);

which would ensure the comments are both next to the field definition they're documenting and that they make it all the way to the database. I looked into the biggest products, and MySQL supports this syntax. I couldn't find any similar syntax in any other product.

+1 for the idea - though restricting it to columns would not be ideal.

CREATE TABLE name
COMMENT IS
'Table Comment Here'
(
col1 serial COMMENT IS 'Place comment here'
);

And what about the other CREATE statements? IMHO if we follow this path then we should add COMMENT to all CREATE statements and perhaps also to ALTER. Of course in a set of small patches to make the reviewers life easier.

Regards,

Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

>> Github: http://github.com/fabriziomello

Re: Column COMMENTs in CREATE TABLE?

From

David Fetter

Date:

03 July 2016, 02:12:27

On Sat, Jul 02, 2016 at 01:06:49PM -0400, David G. Johnston wrote:
> On Sat, Jul 2, 2016 at 12:55 PM, Marko Tiikkaja <marko@joh.to> wrote:
> 
> >
> > What I would prefer is something like this:
> >
> > CREATE TABLE foo(
> >   f1 int NOT NULL COMMENT
> >     'the first field',
> >   f2 int NOT NULL COMMENT
> >     'the second field',
> > ...
> > );
> >
> > which would ensure the comments are both next to the field definition
> > they're documenting and that they make it all the way to the database. I
> > looked into the biggest products, and MySQL supports this syntax.  I
> > couldn't find any similar syntax in any other product.
> >
> >
> +1 for the idea - though restricting it to columns would not be ideal.

+1 for adding it to all the CREATEs whose objects support COMMENT.

Might something like
   CREATE ... [WITH (COMMENT $$Big honking comment here$$)]

for the explicit CREATE cases and something like
   CREATE TABLE foo(       id SERIAL PRIMARY KEY WITH (COMMENT 'Generated primary key, best find a natural one, too'),
    t TEXT NOT NULL WITH (COMMENT 'Really?  A single-letter name?!?'),       ...   )
 

for cases where the CREATE isn't part of the syntax help alleviate the
keyword issue?

I suggested doing it this way because where there's one thing, in this
case a COMMENT, it's reasonable to expect that there will be others
and make that simpler to do.

Best,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: Column COMMENTs in CREATE TABLE?

From

"David G. Johnston"

Date:

03 July 2016, 03:34:52

On Sat, Jul 2, 2016 at 8:31 PM, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:

Em sábado, 2 de julho de 2016, David G. Johnston <david.g.johnston@gmail.com> escreveu:
On Sat, Jul 2, 2016 at 12:55 PM, Marko Tiikkaja <marko@joh.to> wrote:

What I would prefer is something like this:

CREATE TABLE foo(
f1 int NOT NULL COMMENT
'the first field',
f2 int NOT NULL COMMENT
'the second field',
...
);

which would ensure the comments are both next to the field definition they're documenting and that they make it all the way to the database. I looked into the biggest products, and MySQL supports this syntax. I couldn't find any similar syntax in any other product.

+1 for the idea - though restricting it to columns would not be ideal.

CREATE TABLE name
COMMENT IS
'Table Comment Here'
(
col1 serial COMMENT IS 'Place comment here'
);

And what about the other CREATE statements? IMHO if we follow this path then we should add COMMENT to all CREATE statements and perhaps also to ALTER. Of course in a set of small patches to make the reviewers life easier.

I should have made it clear I didn't expect TABLE to be the only object but rather was using it as an example of how we could/should do this generally for top-level objects (e.g., table) and sub-objects (e.g., column).

David J.

Re: Column COMMENTs in CREATE TABLE?

From

Tom Lane

Date:

03 July 2016, 15:42:06

David Fetter <david@fetter.org> writes:
> On Sat, Jul 02, 2016 at 01:06:49PM -0400, David G. Johnston wrote:
>> +1 for the idea - though restricting it to columns would not be ideal.

> +1 for adding it to all the CREATEs whose objects support COMMENT.

TBH, I think this is a pretty bad idea.  I can see the reasoning for
allowing COMMENT in a table column definition, but the argument for
allowing it in simpler CREATEs seems tissue-thin:
CREATE FUNCTION foo(int) RETURNS ... ;COMMENT ON FUNCTION foo(int) IS 'blah';

vs
CREATE FUNCTION foo(int) RETURNS ...WITH (COMMENT 'blah');

Not much of a keystroke savings, nor is the comment noticeably
"closer" to its object than before.  Furthermore, the code footprint
of allowing that everywhere will be enormous.  And for statements that
already use WITH for something, I'm not sure you'll be able to
shoehorn this in without any grammatical trouble, either.  (It would
certainly be embarrassing if you did thirty-five flavors of CREATE
this way and then the syntax failed to work in the thirty-sixth.)

I think we should add something to ColumnDef and call it good.
        regards, tom lane

Re: Column COMMENTs in CREATE TABLE?

From

Peter Eisentraut

Date:

05 August 2016, 14:14:28

On 7/3/16 11:41 AM, Tom Lane wrote:
> I can see the reasoning for
> allowing COMMENT in a table column definition, but the argument for
> allowing it in simpler CREATEs seems tissue-thin:
> 
>     CREATE FUNCTION foo(int) RETURNS ... ;
>     COMMENT ON FUNCTION foo(int) IS 'blah';
> 
> vs
> 
>     CREATE FUNCTION foo(int) RETURNS ...
>     WITH (COMMENT 'blah');
> 
> Not much of a keystroke savings, nor is the comment noticeably
> "closer" to its object than before.

I had actually been thinking about a similar proposal, but specifically
for CREATE FUNCTION.  But the syntax would have to put it above the
function body, not below it.  I think the CREATE FUNCTION syntax could
actually handle that.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Column COMMENTs in CREATE TABLE?

From

David Fetter

Date:

05 August 2016, 15:58:17

On Fri, Aug 05, 2016 at 10:14:21AM -0400, Peter Eisentraut wrote:
> On 7/3/16 11:41 AM, Tom Lane wrote:
> > I can see the reasoning for
> > allowing COMMENT in a table column definition, but the argument for
> > allowing it in simpler CREATEs seems tissue-thin:
> > 
> >     CREATE FUNCTION foo(int) RETURNS ... ;
> >     COMMENT ON FUNCTION foo(int) IS 'blah';
> > 
> > vs
> > 
> >     CREATE FUNCTION foo(int) RETURNS ...
> >     WITH (COMMENT 'blah');
> > 
> > Not much of a keystroke savings, nor is the comment noticeably
> > "closer" to its object than before.
> 
> I had actually been thinking about a similar proposal, but specifically
> for CREATE FUNCTION.  But the syntax would have to put it above the
> function body, not below it.  I think the CREATE FUNCTION syntax could
> actually handle that.

For what it's worth, I tend to put the function body last.  That's
just my taste, though.  Would it be hard to keep the ability to
permute the stuff after
   CREATE FUNCTION (args)   RETURNS [SETOF] type

as we have it now?

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: Column COMMENTs in CREATE TABLE?

From

Peter Eisentraut

Date:

05 August 2016, 16:10:58

On 8/5/16 11:58 AM, David Fetter wrote:
> For what it's worth, I tend to put the function body last.  That's
> just my taste, though.  Would it be hard to keep the ability to
> permute the stuff after
> 
>     CREATE FUNCTION (args)
>     RETURNS [SETOF] type
> 
> as we have it now?

I don't think anybody is suggesting to change that.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services