Postgres ts_vector

Question

I am using Sequlize with Nodejs.

My table name is Users, and it has a Username column. I called ts vectored column userNameVector. When I try to create a column and set triggers, I keep getting the error "errorMissingColumn".

Apparently, this tells me that my column "username" does not exist, but I checked it three times and ran it.

The log from the node console looks like this:

Executing (default): ALTER TABLE "Users" ADD COLUMN "userNameVector" TSVECTOR Executing (default): UPDATE "Users" SET "userNameVector" = to_tsvector('english', userName) Executing (default): CREATE INDEX userName_search_idx ON "Users" USING gin("userNameVector"); Executing (default): CREATE TRIGGER userName_vector_update BEFORE INSERT OR UPDATE ON "Users" FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger("userNameVector", 'pg_catalog.english', userName) { [Error: column "username" does not exist] severity: 'ERROR', code: '42703', position: '62', file: 'parse_relation.c', line: '2655', routine: 'errorMissingColumn', sql: 'UPDATE "Users" SET "userNameVector" = to_tsvector(\'english\', userName)' } 

Answer 1

It looks like another case of erroneous case names CaMeL.

Postgres identifiers are by default divided into lower ones. If you use non-standard names (legal, all in lower case) and double quotes when creating, you will have to double them to the end. So:

"userName" instead of userName

on this topic:

• Identity Guide
• Prevent insertion if condition is met