Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
The issue is with DbVisualizer, not Postgres. –  ypercube Sep 16 '14 at 20:02
    
@ypercube: I've updated the post. –  Marcus Junius Brutus Sep 16 '14 at 20:26
1  
Probably DbVis created the table with the column quoted: "LOOP". As a result, if you do that, you then have to always quote the column. loop or LOOP will not work, the column has to be referred as "LOOP". –  ypercube Sep 16 '14 at 20:34
    
@ypercube I used psql 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 of psql. –  Marcus Junius Brutus Sep 16 '14 at 21:25
1  
@ypercube: created SSCCE: dba.stackexchange.com/q/76776/34332 –  Marcus Junius Brutus Sep 16 '14 at 21:39

1 Answer 1

up vote 2 down vote accepted

The word LOOP is a reserved word in PL/pgSQL. If you want to use it as a column name, you need to quote it.

share|improve this answer
1  
You should add that the quoting is only needed within PL/pgSQL, not for plain SQL. –  a_horse_with_no_name Sep 16 '14 at 21:56
2  
I was checking for reserved words in this list: postgresql.org/docs/current/static/sql-keywords-appendix.html. Is there a list of PL/pgSQL keywords posted somewhere? Google reveals discussions and even source code: comp.nus.edu.sg/~cs3223/doxygen-postgres9.3.2/html/de/dcb/… but not a readable list. –  Marcus Junius Brutus Sep 16 '14 at 22:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.