Postgred pg_notify/listen only working if channel name is lower case

341
May 26, 2017, at 6:50 PM

I've read https://www.postgresql.org/docs/9.6/static/sql-notify.html and the channel name is just described as an 'identifier'.

I'm using NodeJS and pg https://www.npmjs.com/package/pg to access postgres.

If I use a lower case word in both, e.g. pg_notify('foo', ... and LISTEN foo it works. I've tested all combinations:

pg_notify  LISTEN  outcome
lower      lower   works
lower      upper   works
upper      lower   fails
upper      upper   fails

Is this a bug, or is it a logical result of being an 'identifier'? (If so, should this be documented on pg_notify page?)

Answer 1

confusion comes from quotes. single quotes are used for a string, double are used for identifier and can be skipped to none if you don't use mixed case/start from number/ or other tricks.

when you run pg_notify function you pass 'foo' as a string argument and thus use single quotes, when you run NOTIFY "Virtual" you use Virtual as channel name - an identifier, so you need to use " here.

So as you answered yourself, changing

client.query("LISTEN 'Virtual'"); 

to

client.query('LISTEN "Virtual"');

fixes an issue

https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Answer 2

You should look for an identifier definition: Identifiers and Key Words.

Unquoted identifiers are case insensitive in that sense that regardless of how they are written by a user, Postgres sees them as in lowercase. In the function pg_notify() the first argument is not an identifier but a text literal which is treated as an identifier without parsing, so it is not converted to lowercase.

Rent Charter Buses Company
READ ALSO
Function does not return the JSON result when invoked

Function does not return the JSON result when invoked

I am new to nodejs and I wrote a code that invokes an API (GET) which returns a JSON array of documents

286
Insert a date Object from json file in mongodb

Insert a date Object from json file in mongodb

I have a JSON file with some data and want to add date information and then insert the content of this JSON file in mongoDBThe JSON file looks something like this:

390
show log time every request in Express?

show log time every request in Express?

I've some problem in hereI don't know why this is happened

314