Re: Problem with a "complex" upsert

On Thu, 21 Jun 2018 at 15:46, Mario de Frutos Dieguez
<mariodefrutos(at)gmail(dot)com> wrote:
> In this case is just one column but in other queries I'm updating
> multiple columns thats why I set the ROW thing

Hmm. The documentation isn't explicit that that's valid syntax. Choices are
({expression|DEFAULT} [,...] |
( sub-SELECT )

and although the docs do say row constructors are valid as sub-SELECT
for comparisons I'm not sure that it fits here.

It does seem like it's trying to work anyway but my guess is that
"Table has type character varying". doesn't mean it's trying to use
the geoid value, but rather that it's implying that the ROW() (which
returns an anonymous type) is the same type as the target table of the
main INSERT query (rather than the type of the column in brackets).
Whether it counts as a bug or not (given that it's not explicitly
defined as allowed syntax) is probably an esoteric argument.

Are you using the ROW() syntax just because it's easier to build the
query programmatically? Otherwise I can't see why you wouldn't just
use SET col1=EXCLUDED.col1, col2=EXCLUDED.col2 [, ....]

Does
SET (b01003001) = (SELECT b01003001 FROM EXCLUDED)
work?

Geoff