Re: pg_get_constraintdef() doesn't always give an equal constraint

[forgot to copy this to pgsql-bugs]

On Mon, Mar 23, 2015 at 7:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't see any simple way around that except to dump using the syntax
>         '10.1'::double precision

There is a similar problem related to NUMERIC:
  '1'::numeric is dumped as
  1::numeric
which introduces a cast as well. There's also a problem with negative
constants, because it introduces parenthesis instead of single quotes:
  '-1'::numeric
is dumped as
  (-1)::numeric

> which is ugly as sin, but perhaps we have no choice.  A longer-term
> solution might be to get the parser to interpret
>         10.1::double precision
> as a float8 literal to start with, but that seems like it could have
> unexpected side-effects?  Not sure.
>
> OTOH, you could argue that existing dump files already have the
> unquoted-literal syntax so it behooves us to try to get the parser
> to read them as they were meant.

Hmm. I'm not sure how you intend to parse that, but it seems like it
would be hard to differentiate between:
  10.1::double precision
and
  10.1::text
in the parser, so I think one side effect would be that the latter
expression would be a text literal with no cast, which is a little
strange (though not necessarily bad). I guess you could hard-code it
to recognize double precision (and other float types and aliases?).

This bug is pretty old and nobody has complained about it before.
Let's just figure out a good unambiguous representation of float and
numeric literals, and then backport it. Anyone who cares enough to fix
this issue can upgrade to the latest point release on the old version,
and then dump/reload.

For numeric, I think appending ".0" (if it's an integral value) is the
easiest, prettiest, and least-surprising. For floats, we can either
use the single-quotes and type annotation, or we can come up with
something new like appending an "f" to the value.

> A larger issue is that I have a nasty feeling that this isn't the
> only place where ruleutils.c output might be read in a way that's
> functionally equivalent to the original, but not the exact same
> parsetree.

I'm concerned about other cases as well, but fixing this seems like a
step in the right direction.

Regards,
     Jeff Davis