non-standard string literals

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


Your name        :    Andrew Pimlott
Your email address    :       pimlott@idiomtech.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.