Thread: regexp err msg question
Hi:
8.3.4 on linux
This query fails with this message...
mydb=# select distinct fivr from alphaview where name ~ ''^foo'';
ERROR: type "foo" does not exist
(note: those are two single quotes before the ^ and after foo, NOT double quotes)
Could someone explain the error message?
Thanks in Advance !
"Gauthier, Dave" <dave.gauthier@intel.com> writes: > mydb=# select distinct fivr from alphaview where name ~ ''^foo''; > ERROR: type "foo" does not exist > (note: those are two single quotes before the ^ and after foo, NOT double quotes) You realize of course that you've got too many quotes there. > Could someone explain the error message? I think it's parsing that as name ~ '' ^ foo '' That is, empty string literal, ^ operator, typename preceding string literal, empty string literal. The "typename 'string'" syntax wasn't one of the SQL committee's better ideas :-( regards, tom lane
Yes, someone is using too many quotes, I think in a script because the log file gets bursts of these messages. I need to foind out who's doing this. I know pg_stat_activity is the key in that endevour, but these are remote db queries, so I have to figure out how to deal with that indirection. I was curious where the reference to "type" came from in the error message. Thanks Tom. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, December 07, 2010 10:45 AM To: Gauthier, Dave Cc: pgsql-general List Subject: Re: [GENERAL] regexp err msg question "Gauthier, Dave" <dave.gauthier@intel.com> writes: > mydb=# select distinct fivr from alphaview where name ~ ''^foo''; > ERROR: type "foo" does not exist > (note: those are two single quotes before the ^ and after foo, NOT double quotes) You realize of course that you've got too many quotes there. > Could someone explain the error message? I think it's parsing that as name ~ '' ^ foo '' That is, empty string literal, ^ operator, typename preceding string literal, empty string literal. The "typename 'string'" syntax wasn't one of the SQL committee's better ideas :-( regards, tom lane