Thread: \ escapes in check constraint strings?

I see the following behaivor on 7.4.3 and 7.4 stable from about a week ago:
bruno=> create table test ();
CREATE TABLE
bruno=> alter table test add constraint test2 check('\\' = '');
ALTER TABLE
bruno=> \d test
    Table "public.test"
 Column | Type | Modifiers
--------+------+-----------
Check constraints:
    "test2" CHECK ('\\'::text = ''::text)

bruno=> select '\\';
 ?column?
----------
 \
(1 row)

I didn't see \\ left as \\ in at least some 7.3 versions.

I think this is a bug, but maybe there are different escape rules for
strings in check constraints.

In the real case that this derives from I was getting a different number
of backslashes than I expected in a regular expression.

On Fri, Jul 16, 2004 at 16:49:25 -0500,
  Bruno Wolff III <bruno@wolff.to> wrote:
> I see the following behaivor on 7.4.3 and 7.4 stable from about a week ago:
> bruno=> create table test ();
> CREATE TABLE
> bruno=> alter table test add constraint test2 check('\\' = '');
> ALTER TABLE
> bruno=> \d test
>     Table "public.test"
>  Column | Type | Modifiers
> --------+------+-----------
> Check constraints:
>     "test2" CHECK ('\\'::text = ''::text)
>
> bruno=> select '\\';
>  ?column?
> ----------
>  \
> (1 row)
>
> I didn't see \\ left as \\ in at least some 7.3 versions.
>
> I think this is a bug, but maybe there are different escape rules for
> strings in check constraints.
>
> In the real case that this derives from I was getting a different number
> of backslashes than I expected in a regular expression.

I think I have figured out what was going on. It looks like [\] in 7.3
regular expressions would try to match a \, but in 7.4 the ] was escaped
resulting in an invalid regular expression. So the real problem was
that in 7.3 I was using something like '[\\]' when I should have been
using '[\\\\]'.
While I was trying to figure out what was going on I was confused by
the \d output putting 2 \s where there was only one. Presumably this
is so the output could be used to recreate the constraint. This seems
reasonable, but it this particular case confused me.

Bruno Wolff III wrote:
> I see the following behaivor on 7.4.3 and 7.4 stable from about a
> week ago: bruno=> create table test ();
> CREATE TABLE
> bruno=> alter table test add constraint test2 check('\\' = '');
> ALTER TABLE
> bruno=> \d test
>     Table "public.test"
>  Column | Type | Modifiers
> --------+------+-----------
> Check constraints:
>     "test2" CHECK ('\\'::text = ''::text)

It prints the SQL source code for the check constraint.  There is no
bug, AFAICS.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/