CODEWITHSUNDEEP
postgresqlpostgresql-9.3
Krishna
Krishna

Reputation: 835

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

Actually i am migrating my ms sql server into postgresql 9.3 and we were using int data type to store 1 and 0 for boolean values but in postgresql9.3 it is not possible. what did i get when i did a little research on datatype of postgresql:

In Postgresql:-
For Integer type datatype:-

insert into ask(aint) values(1)         working
insert into ask(aint) values('1')       working

insert into ask(aint) values(true)      not working

select * from ask where aint=1;     working
select * from ask where aint='1';   working
select * from ask where aint=true;  working


*For smallint type datatype:-

insert into ask(asmall) values(1);      working
insert into ask(asmall) values('1');    working
insert into ask(asmall) values(true);   working

select * from ask where asmall = 1      working
select * from ask where asmall = '1'    working
select * from ask where asmall = true   not working

so if i want to use smallint then the problem that i am getting is , i can not compare any boolean field and if i use integer then i can not insert any boolean value eventhough i can compare booelan field. And

i can not use boolean data type as it return "t" for true and "f" for false so what i want is?

  1. is there any way to compare smallint with boolean?
  2. Or Is there any way to insert boolean values in integer type data type.
  3. Or can we do something so that boolean type start returning 1 for true and 0 for false.

So can somebody please help me? it's emergency! thanks

Upvotes: 3

Views: 823

Answers (2)

Tomas Greif
Tomas Greif

Reputation: 22633

By default, there is no such conversion - implicit conversion is by default used only for data types from similar groups (e.g. integer and bigint).

Fortunately, you can always add new implicit cast if you really need to do so.

First, we will create a new function to convert smallint to boolean. To do this, we will use existing C function bool_int4.

CREATE OR REPLACE FUNCTION int2(boolean)
  RETURNS smallint AS
'bool_int4'
  LANGUAGE internal IMMUTABLE STRICT
  COST 1;

Now we can create new implicit cast from boolean to smallint:

CREATE CAST (boolean AS smallint) WITH FUNCTION int2(boolean) as implicit;

And finally, we are ready to compare smallint and boolean directly:

select 1::smallint=true

PostgreSQL documentation warns everyone that bad things can happen when you use implicit casting unwisely:

It is wise to be conservative about marking casts as implicit. An overabundance of implicit casting paths can cause PostgreSQL to choose surprising interpretations of commands, or to be unable to resolve commands at all because there are multiple possible interpretations. A good rule of thumb is to make a cast implicitly invokable only for information-preserving transformations between types in the same general type category.

You can get list of casts for specified types from pg_catalog, from table pg_cast:

select 
  castsource::regtype,
  casttarget::regtype,
  castcontext
from 
  pg_catalog.pg_cast
where
  castsource::regtype in ('boolean','smallint','integer') and
  casttarget::regtype in ('boolean','smallint','integer')
order by
  castsource::regtype,
  casttarget::regtype;

This will return (9.1.9):

castsource;casttarget;castcontext;
boolean;integer;e
smallint;integer;i
integer;boolean;e
integer;smallint;a

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

if the column if of the boolean type then cast it to integer to compare to integer:

select the_boolean_column::int = 1;

to insert a boolean value as smallint cast it to integer fisrt

insert into t (integer_column) values (boolean_value::int::smallint)

to make the boolean_column always return integer create a view

create view v as
select boolean_column::int as integer_column
from t

When you query a boolean column it does not return 't' or 'f'. It returns a boolean value, true or false or null. The string representation of the value is up to the client, hence the 't'.

Upvotes: 2

Related Questions