Re: BUG #13661: Using word LIMIT - Mailing list pgsql-bugs

On Fri, Oct 2, 2015 at 4:32 AM, <kmursk@rambler.ru> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13661
> Logged by:          Danilenko Andrey
> Email address:      kmursk@rambler.ru
> PostgreSQL version: 9.4.1
> Operating system:   Windows
> Description:
>
> Word LIMIT is a clause of SQL-statement and could not be used as a name o=
f
> column.


=E2=80=8BI agree. It is, IMO, "poor practice" to use an SQL keyword as a co=
lumn
name.=E2=80=8B



> But it is possible to create table with this column-name. And for
> all that such column-name transforms in upper case. That is bug, because
> there shoud not be possibility to use name LIMIT at all with clause CREAT=
E
> TABLE. And moreover there shoud not be implicit case-transformation
>
>
=E2=80=8BI, personally, do not consider this to be a bug. It is "as designe=
d".

ref:
http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTA=
X-IDENTIFIERS
<quote>
There is a second kind of identifier: the *delimited identifier* or *quoted
identifier*. It is formed by enclosing an arbitrary sequence of characters
in double-quotes ("). A delimited identifier is always an identifier, never
a key word. So "select" could be used to refer to a column or table named
"select", whereas an unquoted select would be taken as a key word and would
therefore provoke a parse error when used where a table or column name is
expected. The example can be written with quoted identifiers like this:
</quote>=E2=80=8B


=E2=80=8BSo, it is possible to write: CREATE TABLE mytable ("select" text);=
 And
then do a SELECT "select" FROM mytable; Just like "select", you are able to
use "LIMIT"

On the same page referenced above:

<quote>
Quoting an identifier also makes it case-sensitive, whereas unquoted names
are always folded to lower case. For example, the identifiers FOO, foo, and
"foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are
different from these three and each other. (The folding of unquoted names
to lower case in PostgreSQL is incompatible with the SQL standard, which
says that unquoted names should be folded to upper case. Thus, foo should
be equivalent to "FOO" not "foo" according to the standard. If you want to
write portable applications you are advised to always quote a particular
name or never quote it.)
</quote>=E2=80=8B

=E2=80=8BThe above might be considered an enhancement request. But the acti=
ons of
PostgreSQL, for good or ill, are documented to work as you have experienced=
.

Of course, this is just a layman's (not a developer's) take on this.=E2=80=
=8B Oh,
and a transcript from PostgreSQL 9.4.4 on Fedora Linux (RedHat) x86_64:

psql (9.4.4)
Type "help" for help.

dbname=3D# create table a (limit integer);
ERROR:  syntax error at or near "limit"
LINE 1: create table a (limit integer);
                        ^
dbname=3D# create table a ("limit" integer, "LIMIT" text);
CREATE TABLE
dbname=3D# \d a
       Table "public.a"
 Column |  Type   | Modifiers
--------+---------+-----------
 limit  | integer |
 LIMIT  | text    |



--=20

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones =3D 1 Megaphone

Maranatha! <><
John McKown