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

Question

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.