AW: Cast INTEGER to BIT confusion - Mailing list pgsql-general

Thank you, Erik! Prefixing the search path in fact looks very interesting, and I think in this particular application
itis a safe (and the only) solution.
 

Is setting the search path something that has to be done for each new connection / each user, or is this something
staticand global for the database?
 

Thanks a lot!
-Markus


-----Ursprüngliche Nachricht-----
Von: Erik Wienhold <ewie@ewie.name> 
Gesendet: Dienstag, 15. August 2023 13:48
An: [Quipsy] Markus Karg <karg@quipsy.de>; pgsql-general@lists.postgresql.org
Betreff: Re: Cast INTEGER to BIT confusion

> On 15/08/2023 10:49 CEST [Quipsy] Markus Karg <karg@quipsy.de> wrote:
>
> Hello PostgreSQL Community,
>
> I like to store just a single bit but that can be either 1 or 0, so I 
> tried to do this:
>
> CREATE TABLE T (c BIT);
> INSERT INTO T VALUES (1);
> -- I MUST NOT change both lines in any way as these are part of a third-party application!
>
> Unfortunately this tells me:
>
> column "c" is of type bit but expression is of type integer
>
> So I logged in as a cluster admin and I tried this:
>
> CREATE CAST (integer AS bit) WITH INOUT AS IMPLICIT;
>
> Unfortunately that tells me:
>
> cast from type integer to type bit already exists
>
> This is confusing! Apparently PostgreSQL 15.3 comes with the needed 
> cast out-of-the-box but it does not apply it? This is confusing!
>
> What is my fault?

The built-in cast is explicit (castcontext = 'e'):

    =# SELECT * FROM pg_cast WHERE castsource = 'int'::regtype AND casttarget = 'bit'::regtype;
      oid  | castsource | casttarget | castfunc | castcontext | castmethod
    -------+------------+------------+----------+-------------+------------
     10186 |         23 |       1560 |     1683 | e           | f
    (1 row)

It's not possible to drop that cast and replace it with a custom one:

    =# DROP CAST (int AS bit);
    ERROR:  cannot drop cast from integer to bit because it is required by the database system

You could create a custom domain if you're only interested in values 0 and 1 and don't use bit string functions.  The
searchpath must be changed so that domain bit overrides pg_catalog.bit:
 

    =# CREATE SCHEMA xxx;
    =# CREATE DOMAIN xxx.bit AS int;
    =# SET search_path = xxx, pg_catalog;
    =# CREATE TABLE t (c bit);
    =# INSERT INTO t VALUES (1);
    INSERT 0 1

But I would do that only if the third-party code cannot be tweaked because the custom domain could be confusing.  It's
alsoprone to errors as it relies on a specific search path order.  Also make sure that regular users cannot create
objectsin schema xxx that would override objects in pg_catalog.
 

--
Erik