Cast INTEGER to BIT confusion

2023-08-15 Thread [Quipsy] Markus Karg
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


AW: Cast INTEGER to BIT confusion

2023-08-15 Thread [Quipsy] Markus Karg
Thank you, Erik! Prefixing the search path in fact looks very interesting, and 
I think in this particular application it is 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 static and global for the database?

Thanks a lot!
-Markus


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

> On 15/08/2023 10:49 CEST [Quipsy] Markus Karg  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


AW: Cast INTEGER to BIT confusion

2023-08-15 Thread [Quipsy] Markus Karg
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  
Gesendet: Dienstag, 15. August 2023 13:48
An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org
Betreff: Re: Cast INTEGER to BIT confusion

> On 15/08/2023 10:49 CEST [Quipsy] Markus Karg  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


AW: PostgreSQL and GUI management

2023-08-15 Thread [Quipsy] Markus Karg
I am using pg_admin in the browser, and it works rather fine for me.
-Markus

Von: Jason Long 
Gesendet: Dienstag, 15. August 2023 17:09
An: pgsql-general@lists.postgresql.org
Betreff: PostgreSQL and GUI management

Hello,
Does PostgreSQL have a graphical environment for management or is it only 
managed through CLI?


Thank you.


AW: AW: Cast INTEGER to BIT confusion

2023-08-17 Thread [Quipsy] Markus Karg
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.
-Markus


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

> On 15/08/2023 14:02 CEST [Quipsy] Markus Karg  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 
> Gesendet: Dienstag, 15. August 2023 13:48
> An: [Quipsy] Markus Karg ; 
> 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