Обсуждение: [DOCS] Doc BUG and/or Change Request: Include Postgres's system columns inreserved words in docs, Chnage to previx sytem colums with 2 underscores?
Vitals:
Postgres 9.6 on Windows 7
by way of NpgSql C# library (v4.0.30319)
DOC BUG:
I'm curious if PostgreSQL's own special columns should be included on the "Reserved Words" list. This might be obvious to those familiar with the inner working of Postgres, for us that are more application level developers this may not be as obvious.. Easy to know the core sql tokens are reserved such as "select, min, like, from, and others..." are all reserved. The issue was encountered when trying to create a table with cmax as a column name. Following the instructions on filing bugs I searched the main site and eventually found it is documented but not clearly. Found that cmax is a system column reserved by Postgres. At first when i got the error specifically this one:
https://www.postgresql.org/docs/9.6/static/sql-keywords-appendix.html
Change Request:
Got me thinking what the convention use to be in C starting out 30 years ago, should work good in Postgres as well? Most all C compilers would prefix variables or items within system struct that were considered system/reserved with an underscores or two. This worked great before C++ which brought classes and namespaces to assist in scoping. This practice worked well to prevent name collisions between users code the compiler itself should work nicely in Postgres too. Finally if this convention is adapted there could be one generic note in the docs saying "User columns should never being with an underscore." This convention worked well for C before C++ exists, should work good for Postgres too?
Thanks for your consideration.
dan d:)
2017-10-02 22:30 GMT-03:00 dan d <dano2k3@hotmail.com>: > Npgsql.PostgresException (0x80004005): 42701: column name "cmax" conflicts > with a system column name > > My first step was to google "Postgres Reserved Words" will eventually get > you to Appendix C in the doc but not mention about any system columns, url > for help page: > > https://www.postgresql.org/docs/9.6/static/sql-keywords-appendix.html > The title is clear: *SQL* Key Words. It is not *PostgreSQL* Key Words. BTW, system columns are documented [1]. > Got me thinking what the convention use to be in C starting out 30 years > ago, should work good in Postgres as well? Most all C compilers would > prefix variables or items within system struct that were considered > system/reserved with an underscores or two. This worked great before C++ > which brought classes and namespaces to assist in scoping. This practice > worked well to prevent name collisions between users code the compiler > itself should work nicely in Postgres too. Finally if this convention is > adapted there could be one generic note in the docs saying "User columns > should never being with an underscore." This convention worked well for C > before C++ exists, should work good for Postgres too? > This boat was already shipped a long time ago. You can't change that without breaking tons of applications. [1] https://www.postgresql.org/docs/9.6/static/ddl-system-columns.html -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
2017-10-02 22:30 GMT-03:00 dan d <dano2k3@hotmail.com>:
> Npgsql.PostgresException (0x80004005): 42701: column name "cmax" conflicts
> with a system column name
>
> My first step was to google "Postgres Reserved Words" will eventually get
> you to Appendix C in the doc but not mention about any system columns, url
> for help page:
>
> https://www.postgresql.org/docs/9.6/static/sql-keywords- appendix.html
>
The title is clear: *SQL* Key Words. It is not *PostgreSQL* Key Words.
BTW, system columns are documented [1].
There is one other issue that I forgot to mention, my program is taking existing databases and converting and loading them into a new Postgres DB, strange that one of my first test database actually had cmax as a column, unfortunately i'm not at liberty to go back and tell my customer you need to change your database columns... I'd argue we can be assured SQL reserved words won't be used for the obvious reason, but not Postgres's system columns, because of a short sighted design flaw. Hard for me to go back and tell a MSSQL or Oracle DBA use you need to change your database and stop using Postgres's reserved column names... That isn't gonna happen, and I would argue they have a valid point Postgres shouldn't impose any additional restrictions on reserved words besides what SQL imposes, especially when it would be easily for Postges just as compilers did to prefix system required column/variables with underscores.
The fix is really simply prefix system reserved column names with one or two underscores. Simple clean prevents the issue between user code and database code...
Sent: Tuesday, October 3, 2017 7:25 AM
To: Euler Taveira
Cc: dan d; pgsql-docs@postgresql.org
Subject: Re: [DOCS] Doc BUG and/or Change Request: Include Postgres's system columns in reserved words in docs, Chnage to previx sytem colums with 2 underscores?
2017-10-02 22:30 GMT-03:00 dan d <dano2k3@hotmail.com>:
> Npgsql.PostgresException (0x80004005): 42701: column name "cmax" conflicts
> with a system column name
>
> My first step was to google "Postgres Reserved Words" will eventually get
> you to Appendix C in the doc but not mention about any system columns, url
> for help page:
>
> https://www.postgresql.org/docs/9.6/static/sql-keywords- appendix.html
>
The title is clear: *SQL* Key Words. It is not *PostgreSQL* Key Words.
BTW, system columns are documented [1].
The fix is really simply prefix system reserved column names with one or two underscores. Simple clean prevents the issue between user code and database code...