I issue the following DDL:
CREATE TABLE foo
(
loopa INTEGER,
loop INTEGER
);
Then, examining the DDL in the server (using a client-side tool like DbVisualizer - sorry don't know the query to produce it directly) I see the following:
CREATE TABLE
foo
(
loopa INTEGER,
LOOP INTEGER
);
Observe the change in case for the column named loop
but not for the one named loopa
. Looking at the list of SQL keywords loop
doesn't appear to be a keyword. Why then, this selective change?
This seems to be related to a problem I am having with a trigger trying to access a column loop
in a table where I get:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: record "new" has no field "loop"
(where I get the same error regardless of whether the code in the trigger function access the field as NEW.LOOP
or NEW.loop
)
UPDATE
The upper-casing seems to be an artifact of DbVisualizer. psql
shows:
psql (9.1.13)
Type "help" for help.
foo-test=> \dS+ foo
Table "test.foo"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
loopa | integer | | plain |
loop | integer | | plain |
Has OIDs: no
Yet this still doesn't explain why the PostgreSQL engine apparently doesn't allow my trigger to access NEW.loop
. When I rename the column (and the trigger code) to loopi
everything appears to work fine. So, there is likely something deeper at work here than just a DbVisualizer glitch. I'll try to create a SSCCE later.
UPDATE 2
A SSCCE for the trigger problem has been created here.
"LOOP"
. As a result, if you do that, you then have to always quote the column.loop
orLOOP
will not work, the column has to be referred as"LOOP"
. – ypercube Sep 16 '14 at 20:34psql
to create the table, I use DbVisualizer only to view the DDL on the server side. Even if I had used DbVisualizer, the column is not "LOOP" as attested by the output ofpsql
. – Marcus Junius Brutus Sep 16 '14 at 21:25