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.

6
  • 1
    The issue is with DbVisualizer, not Postgres. Commented Sep 16, 2014 at 20:02
  • @ypercube: I've updated the post. Commented Sep 16, 2014 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". Commented Sep 16, 2014 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. Commented Sep 16, 2014 at 21:25
  • 1
    @ypercube: created SSCCE: dba.stackexchange.com/q/76776/34332 Commented Sep 16, 2014 at 21:39

1 Answer 1

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.

2

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.