Is there any way to cast postgresql 9.3 data type

Question

This question already has an answer here:

• Is there any technique to do in postgresql 9.3 so that it start returning 1 and 0 instead of “t” and “f” for boolean type 2 answers

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.

Answer 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

Answer 2

As explained in this related answer:
Generate series of dates - using date type as input
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.

As you can see in 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

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, so you'd have to UPDATE directly.

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.