Re: union of types in a different category

James Harper <james(dot)harper(at)bendigoit(dot)com(dot)au> writes:
> According to clause 3 on http://www.postgresql.org/docs/9.3/static/typeconv-union-case.html regarding union type matching:
> 3. If the non-unknown inputs are not all of the same type category, fail.

> So a query "SELECT 1 UNION SELECT 1.1" works because 1 and 1.1 are of the same category, and one type has an implicit cast to the other, but the query "SELECT '1' UNION SELECT 2" fails because '1' is a string literal and 2 is a number and so they are different categories. Right?

Did you try it?

postgres=# SELECT '1' UNION SELECT 2;
?column?
----------
1
2
(2 rows)

Now, if I'd done this it would fail:

postgres=# SELECT '1'::text UNION SELECT 2;
ERROR: UNION types text and integer cannot be matched
LINE 1: SELECT '1'::text UNION SELECT 2;
^

In the former case, though, an undecorated quoted literal is initially
taken as being of type "unknown", and then when it's matched to the
integer 2 in the other UNION arm, the integer type wins. Further:

postgres=# SELECT '1.1' UNION SELECT 2;
ERROR: invalid input syntax for integer: "1.1"
LINE 1: SELECT '1.1' UNION SELECT 2;
^

You don't magically get numeric on the basis of what's inside the quotes.

> Is this an artificial limitation of postgres or is there an underlying technical reason for this behaviour? For my purposes it would be better if the restriction was removed and that the union would work as long as there was an implicit cast that allowed conversion of all fields to the same type.

Generally speaking, we discourage implicit cross-type-category casts,
so I'm not sure that what you're asking for is different from the
current policy. There certainly is no implicit coercion between
text and integer, so your example isn't making a case for changing
things like that.

> MSSQL doesn't have this restriction and I'd prefer if I didn't have to rewrite these queries (or create a complete set of mssql compatible types in the same category) when porting applications.

We don't put a lot of stock in duplicating other vendors' SQL
implementations, because none of them have anywhere near as much
datatype extensibility as Postgres has. So they can get away with
unprincipled^H^H^H special-case kluges a lot more easily than we can.

regards, tom lane