1

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.

  • 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
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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