2

I am using ppas9.3(oracle compatible) and i want to do the casting so that it affect at one side only. what i want is, I want a data type which can accept both integer and boolean values during insertion and comparison but didn't get any success and the main problem that is occurring is: Initially they accept these values:

In Postgresql:-
For Integer type datatype:-

insert into ask(aint) values(1,'1')         working
insert into ask(aint) values(true)      not working
select * from ask where aint=1,'1',true;    working


*For smallint type datatype:-

insert into ask(asmall) values(1,'1',true); working
select * from ask where asmall = 1,'1'      working
select * from ask where asmall = true   not working

For boolean type datatype:-

insert into ask(abool) values(1)        not working
insert into ask(abool) values(true)     working
select * from ask where abool=1,true    working

After doing internal casting means updating pg table for 'Integer' to accept 'true'(boolean) at the time of comparison the behavior of 'Integer' column totally revert and started working same as 'smallint' and the same for 'smallint' and also same for 'boolean'.

So my question is "Is there any internal casting is available in postgresql 9.3 so that it can affect only one side means either at the time of 'insertion' or at the time of 'comparison'". So if you have any such type of technique please share. thank you.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Krishna
  • 795
  • 2
  • 7
  • 24

2 Answers2

3

There are three types of cast. The registered cast for your source an target type has to be "assignment" (a) or "implicit" (i) to work in the VALUES expression of an INSERT statement. Looking at the system catalog pg_cast, the cast from boolean to integer is only defined "explicit" (e):

SELECT castsource::regtype, casttarget::regtype, castfunc::regproc, castcontext
FROM   pg_cast
WHERE  castsource = 'bool'::regtype
AND    casttarget = 'int'::regtype;

Result:

castsource  casttarget  castfunc         castcontext
boolean     integer     pg_catalog.int4  e

Related:

You would have to change castcontext to make it work - which you can do as superuser. There is no "ALTER CAST" statement for this exotic maneuver, you'd have to UPDATE directly. Like:

UPDATE pg_cast
SET    castcontext = 'a'
WHERE  castsource = 'bool'::regtype
AND    casttarget = 'int'::regtype;

However, there are good reasons for the predefined cast context of each cast. Tampering with system catalogs is not something you should do lightly. In this particular case it may unbalance decision making when Postgres has to pick a matching cast. Like for picking from a set of overloaded functions ...

Similar procedure for integer -> boolean, int2 -> boolean, boolean -> int2, etc.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • thanks but i need an internal casting that affect only one side. I have already tried these techniques but not getting what i want because these casting affect at both time(insertion & comparison) but i need only one. – Krishna Jul 08 '14 at 06:32
  • what about if i use assignment type casting from boolean to integer?. could you please reply fast. – Krishna Jul 08 '14 at 07:24
  • @Krishna: Then statements where values of the source type are (unambiguously) assigned to a field / parameter / variable of the target type are possible without adding an explicit cast. What about it? – Erwin Brandstetter Jul 08 '14 at 12:58
  • yeah, i did the same, i just use assignment casting to convert boolean into smallint and it is working. thanks to all of you. – Krishna Jul 09 '14 at 13:34
1

Not sure but why can't you use CAST operator directly like below which works fine.

insert into ask(aint) values( cast(true as int));

Sample Demo See HERE:

create table ask(aint int);

insert into ask(aint) values(1);

insert into ask(aint) values( cast(true as int));

Selection will result in: 1,1

select * from ask
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • thanks for replying quickly but as i have already mentioned above i want an internal casting which can change only the return type of boolean from 't' to 1 and 'f' to 0 and doesn't affect the insertion type of boolean. – Krishna Jul 08 '14 at 05:22
  • AFAIK, there is no such internal casting operation present. You will have to cast it explicitly. – Rahul Jul 08 '14 at 05:24