non-standard string literals

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Andrew Pimlott
Your email address : pimlott(at)idiomtech(dot)com

System Configuration
---------------------
Architecture (example: Intel Pentium) :

Operating System (example: Linux 2.0.26 ELF) :

PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.1.3

Compiler used (example: gcc 2.95.2) :

Please enter a FULL description of your problem:
------------------------------------------------

As documented at
http://www.ca.postgresql.org/users-lounge/docs/7.1/user/sql-syntax.html#SQL-SYNTAX-CONSTANTS
Postgres supports some non-standard extensions to string literals.
One of the reasons I love Postgres is for its support of standard
SQL, and this violation is an uncharacteristic annoyance.

Normally, this isn't an issue, because when making SQL calls from
programs, I use placeholders instead of string literals. However, I
have queries like:

select * from t where c like ? escape '\'

(because even with placeholders, you have to escape "LIKE"
metacharacters) which works as expected on SQL Server and Oracle.
For Postgres, I need

select * from t where c like ? escape '\\'

Or, I can use a placeholder for the literal backslash, but ... ugh.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Enter in psql:

create table t (c varchar(10));

insert into t values ('hello');

select * from t where c like 'h%' escape '\'; -- FAILS

select * from t where c like 'h%' escape '\\'; -- WORKS

Or in Perl DBI:

...
$sth = $dbh->prepare(<<EOF);
select * from t where c like 'h%' escape '\\'
EOF
$sth->execute; # (\\ is one character above) FAILS

$sth = $dbh->prepare(<<EOF);
select * from t where c like 'h%' escape ?
EOF
$sth->execute("\\"); # ("\\" is one character) WORKS

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

I don't know how this type of preference is usually controlled in
Postgres, but an option to enable strict SQL compliance would be
nice.

Thanks.