union of types in a different category
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?
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.
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.
Thanks
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I prefer the explicit approach used by Postgres - MYSQL is simpler, but I'd say simplistic in this area. While it can automate the cating of tpes/catories of variable, it doesn't always do it the way I want - so I need to be explicit anyway.
In your second use case, which fails - do you want numerics cast to strings or vice versa? It can make difference, so to get what you want rather than otherwise, I prefer to be explicit. in either Postgres or MySQL.
Interestingly - & i'm curious as to why"
SELECT '1' UNION SELECT 2;
?column?
----------
1
2
(2 rows)
SELECT '1' UNION SELECT 1;
?column?
----------
1
(1 row)
I didn't think UNION did an explicit "distinct" - if that is what is happening?
Brent Wood
Brent Wood
Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529
________________________________________
From: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org] on behalf of James Harper [james.harper@bendigoit.com.au]
Sent: Sunday, February 23, 2014 11:52 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] union of types in a different category
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?
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.
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.
Thanks
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Please consider the environment before printing this email.
NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Interestingly - & i'm curious as to why"
SELECT '1' UNION SELECT 2;
?column?
----------
1
2
(2 rows)SELECT '1' UNION SELECT 1;
?column?
----------
1
(1 row)I didn't think UNION did an explicit "distinct" - if that is what is happening?
UNION removes duplicates. UNION ALL does not.
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I prefer the explicit approach used by Postgres - MYSQL is simpler, but I'd say
simplistic in this area. While it can automate the cating of tpes/catories of
variable, it doesn't always do it the way I want - so I need to be explicit
anyway.In your second use case, which fails - do you want numerics cast to strings or
vice versa? It can make difference, so to get what you want rather than
otherwise, I prefer to be explicit. in either Postgres or MySQL.
Without anything explicit, I would want them cast to text (eg in the direction of the implicit cast for the types involved). The problem is that I don't necessarily have control of the queries - they were written for MSSQL.
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
James Harper <james.harper@bendigoit.com.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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;
^
I did try it, but probably only in the latter form through a query translator I'm working on, and also SELECT 'X' UNION SELECT 1, hoping that they would cast implicitly to a string.
Sorry for the confusion and thanks for the response!
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general