On 09/05/2014 12:04 AM, Pavel Stehule wrote: > > But some missing casts are well - I found lot performance issues based > on using wrong data types - integers, dates in text column.
Of course! That's why the default implicit casts were removed, and for good reason. I'm only talking about a narrow class of a few specific types. I think maybe a _few_ types need to be implicitly convertable from text, but that's about it. text -> jsonb text -> json text -> xml text -> hstore text -> uuid text -> (user defined enum) ... mainly because otherwise app devs need frustrating workarounds (or giving up on the PostgreSQL native types and just using 'text' columns), and because in all these cases PostgreSQL will validate the input. I've raised this before in other threads: http://www.postgresql.org/message-id/edda5c6d-77e3-4c56-b33b-277e7fb32...@hub.org http://www.postgresql.org/message-id/cactajfz8+hg_kom6qivba94kx9l3xuqz99rdushbfksb1mo...@mail.gmail.com ... even from ages ago: http://www.postgresql.org/message-id/4cfdaee0.10...@postnewspapers.com.au It's easy to object to this on type-purist grounds, but from a pragmatic real-users point of view what we currently do is outright painful, and unless we can go and fix every language binding, every query generator, every ORM, etc to handle things just how PostgreSQL expects, some compromise may be warranted. It's easy to dismiss the problem by saying "pass 'unknown' typed literals via your language binding". That even works if you're willing to jump through some hoops and are using raw JDBC. Good luck doing that via EclipseLink, Hibernate, ActiveRecord, SQLAlchemy, MyBatis, Django ORM, or any of the things people use to talk to PostgreSQL on a day to day basis though. Right now it's really painful to use some of PostgreSQL's best features without hacking around the type system by manually creating implicit casts. Another option is to work around it by completely removing the benefit of the strict casting even when it's obviously right (e.g. refusing to cast text to date) with the JDBC connection parameter stringtype=unknown . I'd like to get rid of the need for users to add possibly-buggy custom casts or bypass type checking of text types, by relaxing the casts where appropriate. Here's a partial collection of real world user complaints I've seen about this issue, in addition to the links above. http://stackoverflow.com/q/20339580/398670 http://stackoverflow.com/q/15974474/398670 http://stackoverflow.com/q/17310219/398670 http://stackoverflow.com/q/14858783/398670 Here's an example of someone working around it by passing all strings as 'unknown': http://stackoverflow.com/q/12050945/398670 A workaround someone had to do with an ETL tool: http://stackoverflow.com/q/24038287/398670 For uuid: http://stackoverflow.com/q/13346089/398670 Someone trying to handle it portably: http://stackoverflow.com/q/22242630/398670 The kind of work you need to work around PostgreSQL's strictness with enums: http://stackoverflow.com/q/7603500/398670 http://stackoverflow.com/q/851758/398670 http://stackoverflow.com/q/10898369/398670 http://stackoverflow.com/q/14884955/398670 http://stackoverflow.com/q/10898369/398670 ... and that's just what I can find in a few minutes' searching on one site. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers