James Harper <james.harper@xxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
