PostgreSQL Error: operator does not exist: name = integer

Question

Getting an operator mismatch error when doing a simple query. What causes this?

dev_db=# `select * from registrants where user=1;`
ERROR:  operator does not exist: name = integer
LINE 1: select * from registrants where user=1;
                                            ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Table definition:

dev_db=# \d+ registrants
                              Table "public.registrants"
    Column    |           Type           |     Modifiers      | Storage  | Description
--------------+--------------------------+--------------------+----------+-------------
 user         | integer                  | not null           | plain    |
 degree       | text                     |                    | extended |
 title        | text                     |                    | extended |
 organization | text                     |                    | extended |
 address      | text                     |                    | extended |
 city         | text                     |                    | extended |

Indexes:
    "registrants_pkey" PRIMARY KEY, btree ("user")
Foreign-key constraints:
    "registrants_country_fkey" FOREIGN KEY (country) REFERENCES countries(id)
    "registrants_user_fkey" FOREIGN KEY ("user") REFERENCES users(id)
Referenced by:
    TABLE "class_evaluations" CONSTRAINT "class_evaluations_registrant_fkey" FOREIGN KEY (registrant) REFERENCES registrants("user")

Triggers:
    archive_registrants BEFORE DELETE OR UPDATE ON registrants FOR EACH ROW EXECUTE PROCEDURE archive_reg_table()
Has OIDs: no

dsh · Accepted Answer · 2015-10-27 18:27:14Z

14

According to the manual, USER is a reserved keyword. You must quote it to avoid the syntax error.

SELECT * FROM registrants WHERE "user" = 1

PostgreSQL Reserved Keyword List

If you have time to alter the database, change the column name to one which is not a reserved keyword. This will help you avoid future headaches.

edited Oct 27, 2015 at 18:27

dsh

12.3k3 gold badges37 silver badges53 bronze badges

answered Apr 7, 2013 at 12:32

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erwin Brandstetter Over a year ago

@user2254435: Don't use reserved words as identifiers to begin with.

DevR Over a year ago

Thank you for the quick response. This is from a large existing application and not so easy to change.

Collectives™ on Stack Overflow

PostgreSQL Error: operator does not exist: name = integer

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related