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-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]
on behalf of James Harper [james.har...@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
