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... ________________________________ From: David G. Johnston <david.g.johns...@gmail.com> 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? On Tue, Oct 3, 2017 at 6:13 AM, Euler Taveira <eu...@timbira.com.br<mailto:eu...@timbira.com.br>> wrote: 2017-10-02 22:30 GMT-03:00 dan d <dano...@hotmail.com<mailto:dano...@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]. That's a bit unconvincing since there are words on that table that are not marked as being reserved in any of the three SQL standard columns. Its user-error that an error message clearly indicating "system column name" prompted a search for reserved words instead of "system columns". The main distinction here is that "cmax" is a valid identifier expect when used as a table column name (you can, for instance, name columns of CTEs cmax) due to it already being in use. Given the clarity of the error message and the fact that this is first I've seen the particular complaint in many years of monitoring these lists, I'd say trying to add system columns to the listing of reserved words in the interest of "comprehensiveness" doesn't outweigh the complexity that would be introduced explaining how it differs from other reserved words. I wouldn't be opposed to adding a sentence and cross-reference link to the system columns page and pointing out that specific column names are also already reserved for system use. David J.
