Обсуждение: 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 castto the other, but the query "SELECT '1' UNION SELECT 2" fails because '1' is a string literal and 2 is a number and sothey are different categories. Right? Is this an artificial limitation of postgres or is there an underlying technical reason for this behaviour? For my purposesit would be better if the restriction was removed and that the union would work as long as there was an implicitcast 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 ofmssql compatible types in the same category) when porting applications. Thanks James
> > 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
> > 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
I prefer the explicit approach used by Postgres - MYSQL is simpler, but I'd say simplistic in this area. While it can
automatethe 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
toget 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
castto the other, but the query "SELECT '1' UNION SELECT 2" fails because '1' is a string literal and 2 is a number and
sothey are different categories. Right?
Is this an artificial limitation of postgres or is there an underlying technical reason for this behaviour? For my
purposesit would be better if the restriction was removed and that the union would work as long as there was an
implicitcast 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
ofmssql 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.
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 castto the other, but the query "SELECT '1' UNION SELECT 2" fails because '1' is a string literal and 2 is a number and sothey 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 purposesit would be better if the restriction was removed and that the union would work as long as there was an implicitcast 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 setof 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
> 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 SELECT1, hoping that they would cast implicitly to a string. Sorry for the confusion and thanks for the response! James