Обсуждение: 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 search path 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 also prone to errors as it relies on a
specific search path order.  Also make sure that regular users cannot create
objects in schema xxx that would override objects in pg_catalog.

--
Erik

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

Erik,

I just tried out your proposal on PostgreSQL 15.3 and this is the result:

ERROR:  column "c" is of type bit but expression is of type integer
LINE 5:   INSERT INTO t VALUES (1);
                                ^
HINT:  You will need to rewrite or cast the expression.

Apparently the search path is ignored?!

-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

> On 15/08/2023 13:59 CEST [Quipsy] Markus Karg <karg@quipsy.de> wrote:
>
> Is setting the search path something that has to be done for each new
> connection / each user, or is this something static and global for the
> database?

The search path is set per connection and the initial search path can be
configured on the database and/or individual roles:

1. ALTER DATABASE mydb SET search_path = ...
2. ALTER ROLE myrole SET search_path = ...
3. ALTER ROLE myrole IN DATABASE mydb SET search_path = ...

Those three statements do not affect your current connection though.  Users can
still set a different search path after connecting.

--
Erik

> On 15/08/2023 14:02 CEST [Quipsy] Markus Karg <karg@quipsy.de> wrote:
>
> I just tried out your proposal on PostgreSQL 15.3 and this is the result:
>
> ERROR:  column "c" is of type bit but expression is of type integer
> LINE 5:   INSERT INTO t VALUES (1);
>                                 ^
> HINT:  You will need to rewrite or cast the expression.
>
> Apparently the search path is ignored?!
>
> -----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
>
> You could create a custom domain if you're only interested in values 0 and 1
> and don't use bit string functions.  The search path 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 also prone to errors as it relies
> on a specific search path order.  Also make sure that regular users cannot
> create objects in schema xxx that would override objects in pg_catalog.

Hmm, I thought that Postgres resolves all types through the search path, but
apparently that is not the case for built-in types.  I never used this to
override built-in types so this is a surprise to me.  (And obviously I haven't
tested the search path feature before posting.)

Neither [1] or [2] mention that special (?) case or if there's a distinction
between built-in types and user-defined types.  The USAGE privilege is required
according to [2] but I was testing as superuser anyway.

[1] https://www.postgresql.org/docs/15/ddl-schemas.html
[2] https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-SEARCH-PATH

--
Erik

I am giving up. While even `SELECT current_schemas(true)` correctly prints `xxx, pg_catalog` it still uses the original
bittype. This is completely NOT as described in the documentation, where it is clearly told that pg_catalog only is
searchedimmediately if NOT found in the search path. It seems it is simply impossible to run this application on
PostgreSQL,and we have to stick with a different RDBMS. Very sad.
 
-Markus


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

> On 15/08/2023 14:02 CEST [Quipsy] Markus Karg <karg@quipsy.de> wrote:
>
> I just tried out your proposal on PostgreSQL 15.3 and this is the result:
>
> ERROR:  column "c" is of type bit but expression is of type integer
> LINE 5:   INSERT INTO t VALUES (1);
>                                 ^
> HINT:  You will need to rewrite or cast the expression.
>
> Apparently the search path is ignored?!
>
> -----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
>
> You could create a custom domain if you're only interested in values 0 
> and 1 and don't use bit string functions.  The search path 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 also prone to 
> errors as it relies on a specific search path order.  Also make sure 
> that regular users cannot create objects in schema xxx that would override objects in pg_catalog.

Hmm, I thought that Postgres resolves all types through the search path, but apparently that is not the case for
built-intypes.  I never used this to override built-in types so this is a surprise to me.  (And obviously I haven't
testedthe search path feature before posting.)
 

Neither [1] or [2] mention that special (?) case or if there's a distinction between built-in types and user-defined
types. The USAGE privilege is required according to [2] but I was testing as superuser anyway.
 

[1] https://www.postgresql.org/docs/15/ddl-schemas.html
[2] https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-SEARCH-PATH

--
Erik

Hello,



On Tue, 2023-08-15 at 08:49 +0000, [Quipsy] Markus Karg wrote:
>
>
>
> Hello PostgreSQL Community,
>  
> I have 25+ years of experience with some other RDBMS, but I am a
> PostgreSQL starter, so I assume the following is rather a simple
> beginner’s question…:
>  
> 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?
>  
> Thanks!
> -Markus


It works thus:-

postgres 15.4 =# CREATE TABLE T (c BIT);
CREATE TABLE
postgres 15.4 =# insert into T values (B'1');
INSERT 0 1
postgres 15.4 =# select * from t;
 c
---
 1
(1 row)

See section 9.6 in the doco.

HTH,
Rob

> On 17/08/2023 14:57 CEST rob stone <floriparob@tpg.com.au> wrote:
>
> It works thus:-
>
> postgres 15.4 =# CREATE TABLE T (c BIT);
> CREATE TABLE
> postgres 15.4 =# insert into T values (B'1');
> INSERT 0 1
> postgres 15.4 =# select * from t;
>  c
> ---
>  1
> (1 row)
>
> See section 9.6 in the doco.

But Markus wrote that he cannot change the CREATE TABLE and INSERT statement.

--
Erik

"[Quipsy] Markus Karg" <karg@quipsy.de> writes:
> I am giving up. While even `SELECT current_schemas(true)` correctly prints `xxx, pg_catalog` it still uses the
originalbit type. This is completely NOT as described in the documentation, where it is clearly told that pg_catalog
onlyis searched immediately if NOT found in the search path. 

That's probably because the grammar uses SystemTypeName (effectively
prepending "pg_catalog.") for any type that has special syntax called
out in the SQL standard.  You could get around that in various ways,
but they all involve changing the way the CREATE TABLE command is
written, because just plain "BIT" is a SQL-mandated special case.

> It seems it is simply impossible to run this application on PostgreSQL, and we have to stick with a different RDBMS.
Verysad. 

You could leave the type alone and instead fool with the properties of
the cast (see [1]).  As superuser:

regression=# create table t (f1 bit);
CREATE TABLE
regression=# insert into t values (1);
ERROR:  column "f1" is of type bit but expression is of type integer
LINE 1: insert into t values (1);
                              ^
HINT:  You will need to rewrite or cast the expression.
regression=# select * from pg_cast where castsource = 'integer'::regtype and casttarget = 'bit'::regtype;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod
-------+------------+------------+----------+-------------+------------
 10186 |         23 |       1560 |     1683 | e           | f
(1 row)

regression=# update pg_cast set castcontext = 'a' where castsource = 'integer'::regtype and casttarget =
'bit'::regtype;
UPDATE 1
regression=# select * from pg_cast where castsource = 'integer'::regtype and casttarget = 'bit'::regtype;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod
-------+------------+------------+----------+-------------+------------
 10186 |         23 |       1560 |     1683 | a           | f
(1 row)

regression=# insert into t values (1);
INSERT 0 1

The main disadvantage of this approach is you'd have to remember to
perform that UPDATE in any new database, since pg_dump wouldn't
preserve it for you.

On the whole though I think this project is a lost cause.  If you
are insisting on bug-compatibility with non-SQL-compliant details
of some other DBMS, and you can't adjust the application at all,
there is going to be some new showstopper problem biting you
every day.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/catalog-pg-cast.html

> 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

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

> On 17/08/2023 09:31 CEST [Quipsy] Markus Karg <karg@quipsy.de> wrote:
>
> I am giving up. While even `SELECT current_schemas(true)` correctly prints
> `xxx, pg_catalog` it still uses the original bit type. This is completely
> NOT as described in the documentation, where it is clearly told that
> pg_catalog only is searched immediately if NOT found in the search path.
> It seems it is simply impossible to run this application on PostgreSQL, and
> we have to stick with a different RDBMS. Very sad.
>
> -----Ursprüngliche Nachricht-----
> Von: Erik Wienhold <ewie@ewie.name>
> Gesendet: Dienstag, 15. August 2023 16:28
> An: [Quipsy] Markus Karg <karg@quipsy.de>; pgsql-general@lists.postgresql.org
> Betreff: Re: AW: Cast INTEGER to BIT confusion
>
> > On 15/08/2023 14:02 CEST [Quipsy] Markus Karg <karg@quipsy.de> wrote:
> >
> > I just tried out your proposal on PostgreSQL 15.3 and this is the result:
> >
> > ERROR:  column "c" is of type bit but expression is of type integer
> > LINE 5:   INSERT INTO t VALUES (1);
> >                                 ^
> > HINT:  You will need to rewrite or cast the expression.
> >
> > Apparently the search path is ignored?!
> >
> > -----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
> >
> > You could create a custom domain if you're only interested in values 0
> > and 1 and don't use bit string functions.  The search path 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 also prone to
> > errors as it relies on a specific search path order.  Also make sure
> > that regular users cannot create objects in schema xxx that would override objects in pg_catalog.
>
> Hmm, I thought that Postgres resolves all types through the search path, but apparently that is not the case for
built-intypes.  I never used this to override built-in types so this is a surprise to me.  (And obviously I haven't
testedthe search path feature before posting.) 
>
> Neither [1] or [2] mention that special (?) case or if there's a distinction between built-in types and user-defined
types. The USAGE privilege is required according to [2] but I was testing as superuser anyway. 
>
> [1] https://www.postgresql.org/docs/15/ddl-schemas.html
> [2] https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-SEARCH-PATH

It looks like Postgres resolves standard SQL types without considering the
search path.  This could be a feature (perhaps mandated by the SQL standard?)
and not a bug if the rationale here is to avoid shadowing of standard types
with custom types so that type bit, for example, always behaves as the standard
bit type.

In the following script I create several domains in schema xxx: bit and float
match standard SQL type names, domain foo does not conflict with any built-in
type name, and inet is a built-in type but not a standard type.

    BEGIN;

    CREATE SCHEMA xxx;
    GRANT USAGE ON SCHEMA xxx TO current_user;

    CREATE DOMAIN xxx.bit AS int;
    CREATE DOMAIN xxx.float AS int;
    CREATE DOMAIN xxx.foo AS int;
    CREATE DOMAIN xxx.inet AS int;
    CREATE DOMAIN pg_catalog.foo AS int;
    \dD *.(bit|float|foo|inet)

    SET LOCAL search_path = xxx, pg_catalog;
    SELECT current_schemas(true);

    CREATE TABLE public.t (f1 bit, f2 float, f3 inet, f4 foo);

    SET LOCAL search_path = '';
    \d public.t

    ROLLBACK;

We see that table t is created with the standard SQL types instead of our
custom domains.  Only xxx.inet and xxx.foo are resolved according to the search
path.

    BEGIN
    CREATE SCHEMA
    GRANT
    CREATE DOMAIN
    CREATE DOMAIN
    CREATE DOMAIN
    CREATE DOMAIN
    CREATE DOMAIN
                                List of domains
       Schema   | Name  |  Type   | Collation | Nullable | Default | Check
    ------------+-------+---------+-----------+----------+---------+-------
     pg_catalog | foo   | integer |           |          |         |
     xxx        | bit   | integer |           |          |         |
     xxx        | float | integer |           |          |         |
     xxx        | foo   | integer |           |          |         |
     xxx        | inet  | integer |           |          |         |
    (4 rows)

    SET
     current_schemas
    ------------------
     {xxx,pg_catalog}
    (1 row)

    CREATE TABLE
    SET
                          Table "public.t"
     Column |       Type       | Collation | Nullable | Default
    --------+------------------+-----------+----------+---------
     f1     | bit(1)           |           |          |
     f2     | double precision |           |          |
     f3     | xxx.inet         |           |          |
     f4     | xxx.foo          |           |          |

    ROLLBACK

--
Erik

> On 17/08/2023 15:18 CEST Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> That's probably because the grammar uses SystemTypeName (effectively
> prepending "pg_catalog.") for any type that has special syntax called
> out in the SQL standard.  You could get around that in various ways,
> but they all involve changing the way the CREATE TABLE command is
> written, because just plain "BIT" is a SQL-mandated special case.

The answer I was looking for.  Thanks Tom.

--
Erik