============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Andrew Pimlott
Your email address : [EMAIL PROTECTED]
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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
