Thread: pretty_bool in pg_get_constraintdef has no effect since pg >= 9

Hello,

since postgres 9, `pg_get_constraintdef(cons.oid, pretty_bool)` does not work as expected. The result is always in
"pretty"-format(with newlines), regardless of whether `pretty_bool` is true or false. Calling
`pg_get_constraintdef(constraint_oid)`without `pretty_bool`, also returns "pretty"-format. 
 

Test with:

```
CREATE TABLE tbl (
   id int NOT NULL DEFAULT 0
  ,CHECK 
  (
    CASE
      WHEN id < 0 THEN false
      WHEN id >= 0 THEN true
    END
  )
);

SELECT cons.conname as name, pg_get_constraintdef(cons.oid, false) as src FROM pg_catalog.pg_constraint cons WHERE
cons.conrelidIN (SELECT oid from pg_class WHERE relname='tbl') AND cons.contype = 'c';
 
```

Result on postgres 8:
```
CHECK (CASE WHEN (id < 0) THEN false WHEN (id >= 0) THEN true ELSE NULL::boolean END)
```

Result on postgres >= 9:
```
CHECK (                     
CASE                        
    WHEN (id < 0) THEN false
    WHEN (id >= 0) THEN true
    ELSE NULL::boolean      
END)
```

I'm migrating from postgres 8 to 11 and a sqlalchemy script throws a warning "SAWarning: Could not parse CHECK
constrainttext" because there a no newlines expected in `re.match(r"^CHECK *\((.+)\)( NOT VALID)?$", src)`.
 

Greetings,
elim.

> On 25 Feb 2020, at 13:56, eli.mach@mailbox.org wrote:

> since postgres 9, `pg_get_constraintdef(cons.oid, pretty_bool)` does not work as expected. The result is always in
"pretty"-format(with newlines), regardless of whether `pretty_bool` is true or false. Calling
`pg_get_constraintdef(constraint_oid)`without `pretty_bool`, also returns "pretty"-format. 

This is not a bug, but a deliberate change which was made in 62e666400d back in
2013, the argument being that changes in whitespace should not affect forward
compatibility.

> I'm migrating from postgres 8 to 11 and a sqlalchemy script throws a warning "SAWarning: Could not parse CHECK
constrainttext" because there a no newlines expected in `re.match(r"^CHECK *\((.+)\)( NOT VALID)?$", src)`. 

Surely SA has been updated to work with more recent version of postgres?  I've
not used SA myself, but are you sure you are using the right version of the
tool?

cheers ./daniel

The SA-Version I use is the newest version 1.3.13. Seems that the SA regex never had to deal with newlines. I think
thatcan be easily fixed by adding a "re.DOTALL" in the SA regex. I open an SA-Issue. Thanks for your help.
 

Greetings,
elim.

> On 25 February 2020 14:30 Daniel Gustafsson <daniel@yesql.se> wrote:
> 
>  
> > On 25 Feb 2020, at 13:56, eli.mach@mailbox.org wrote:
> 
> > since postgres 9, `pg_get_constraintdef(cons.oid, pretty_bool)` does not work as expected. The result is always in
"pretty"-format(with newlines), regardless of whether `pretty_bool` is true or false. Calling
`pg_get_constraintdef(constraint_oid)`without `pretty_bool`, also returns "pretty"-format.
 
> 
> This is not a bug, but a deliberate change which was made in 62e666400d back in
> 2013, the argument being that changes in whitespace should not affect forward
> compatibility.
> 
> > I'm migrating from postgres 8 to 11 and a sqlalchemy script throws a warning "SAWarning: Could not parse CHECK
constrainttext" because there a no newlines expected in `re.match(r"^CHECK *\((.+)\)( NOT VALID)?$", src)`.
 
> 
> Surely SA has been updated to work with more recent version of postgres?  I've
> not used SA myself, but are you sure you are using the right version of the
> tool?
> 
> cheers ./daniel