Looking for "proper" escape string syntax from pg_get_constraintdef

Started by Marc Munroover 16 years ago2 messagesgeneral
Jump to latest
#1Marc Munro
marc@bloodnok.com

I am trying to recreate a domain definition from information in
pg_catalog and getting the following warnings:

WARNING: nonstandard use of \\ in a string literal
LINE 3: CHECK (((VALUE ~ '^\\d{4}$'::text) OR (VALUE ~ '^\\d{5}-\\...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 3: CHECK (((VALUE ~ '^\\d{4}$'::text) OR (VALUE ~ '^\\d{5}-\\...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.

My original domain definition is this:

create domain "public"."us_postal_code"
as "pg_catalog"."text"
CHECK (((VALUE ~ E'^\\d{4}$'::text) OR (VALUE ~ E'^\\d{5}-\\d{4}$'::text)));

but retrieving the constraint definition using pg_get_constraintdef(oid)
gives me this:

CHECK (((VALUE ~ '^\\d{4}$'::text) OR (VALUE ~ '^\\d{5}-\\d{4}$'::text)))

How can I get the constraint definition with proper escaping from the
catalog? Is there another function I should be using?

__
Marc

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Marc Munro (#1)
Re: Looking for "proper" escape string syntax from pg_get_constraintdef

Marc Munro <marc@bloodnok.com> writes:

but retrieving the constraint definition using pg_get_constraintdef(oid)
gives me this:

CHECK (((VALUE ~ '^\\d{4}$'::text) OR (VALUE ~ '^\\d{5}-\\d{4}$'::text)))

How can I get the constraint definition with proper escaping from the
catalog? Is there another function I should be using?

pg_dump just turns off that warning. The string is correct for the
prevailing value of standard_conforming_strings; the warning is useless
noise as long as you're careful about maintaining that setting.

regards, tom lane