> -----Original Message----- > From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] > Sent: Mittwoch, 8. Juli 2015 15:44 > To: Marc Mamin > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Problem with ALTER TYPE, Indexes and cast > > Marc Mamin <M.Mamin@xxxxxxxxxxxx> writes: > > Now I have generated queries that include cast information in order > to ensure that the indexes get used. > > > e.g.: WHERE month1.foo = cast('XY' as character(2)) > > > with mixed type, this should become something like: > > > SELECT ... FROM month1 > > WHERE month1.foo = cast('XY' as character(2)) UNION ALL SELECT... > > FROM month2 WHERE month2.foo = cast('XY' as varchar) > > > which is quite complicated to resolve in our "query builder > framework" > > > There seems to be no way to have dynamic casting, something like: > > > WHERE month2.foo = cast('XY' as 'month2.foo'::regtype) > > > Is there a way for it ? > > If the comparison values are always string literals, then you should > just drop the casts altogether, ie > > WHERE month2.foo = 'XY' > > In this sort of situation the literal's type is preferentially resolved > as being the same as whatever it's being compared to. I had to dig a bit to find out why I was using the cast. My issue is that I first clean the literal at some places with text returning functions. The index won't get used when comparing to text: create temp table idtest (c character(8)); insert into idtest select cast(s as character(8)) from generate_series(1,39999)s; create index idtest_c on idtest(c); analyze idtest; explain analyze select * from idtest where c = substring (trim('1234567890abc') for 8) Seq Scan on idtest (cost=0.00..816.99 rows=200 width=9) (actual time=20.302..20.302 rows=0 loops=1) Filter: ((c)::text = '12345678'::text) I can easily get rid of the cast while preprocessing the literal before injecting it in the query though. regards, Marc Mamin > 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
