pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Cast INTEGER to BIT confusion
6+ messages / 5 participants
[nested] [flat]

* Cast INTEGER to BIT confusion
@ 2023-08-15 08:49 [Quipsy] Markus Karg <karg@quipsy.de>
  2023-08-15 11:48 ` Re: Cast INTEGER to BIT confusion Erik Wienhold <ewie@ewie.name>
  2023-08-16 01:02 ` Re: Cast INTEGER to BIT confusion Greg Sabino Mullane <htamfids@gmail.com>
  2023-08-17 12:57 ` Re: Cast INTEGER to BIT confusion rob stone <floriparob@tpg.com.au>
  0 siblings, 3 replies; 6+ messages in thread

From: [Quipsy] Markus Karg @ 2023-08-15 08:49 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Cast INTEGER to BIT confusion
  2023-08-15 08:49 Cast INTEGER to BIT confusion [Quipsy] Markus Karg <karg@quipsy.de>
@ 2023-08-15 11:48 ` Erik Wienhold <ewie@ewie.name>
  2023-08-17 13:26   ` Re: Cast INTEGER to BIT confusion David G. Johnston <david.g.johnston@gmail.com>
  2 siblings, 1 reply; 6+ messages in thread

From: Erik Wienhold @ 2023-08-15 11:48 UTC (permalink / raw)
  To: [Quipsy] Markus Karg <karg@quipsy.de>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> 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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Cast INTEGER to BIT confusion
  2023-08-15 08:49 Cast INTEGER to BIT confusion [Quipsy] Markus Karg <karg@quipsy.de>
  2023-08-15 11:48 ` Re: Cast INTEGER to BIT confusion Erik Wienhold <ewie@ewie.name>
@ 2023-08-17 13:26   ` David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 6+ messages in thread

From: David G. Johnston @ 2023-08-17 13:26 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; +Cc: [Quipsy] Markus Karg <karg@quipsy.de>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Tuesday, August 15, 2023, Erik Wienhold <ewie@ewie.name> wrote:

> > 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:


The table using the system bit type already exists and thus the oid for the
data type of the stored catalog column is that of the system bit type.
Creating a user bit domaim is going to have absolutely zero impact on this
situation.

If you really want to make this work and are willing to risk and deal with
side-effects of manual catalog updates you could maybe make the existing
cast implicit.

David J.

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Cast INTEGER to BIT confusion
  2023-08-15 08:49 Cast INTEGER to BIT confusion [Quipsy] Markus Karg <karg@quipsy.de>
@ 2023-08-16 01:02 ` Greg Sabino Mullane <htamfids@gmail.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Greg Sabino Mullane @ 2023-08-16 01:02 UTC (permalink / raw)
  To: [Quipsy] Markus Karg <karg@quipsy.de>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Tue, Aug 15, 2023 at 4:49 AM [Quipsy] Markus Karg <karg@quipsy.de> wrote:

>
> 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!
>

That is quite the requirement! I hope that the rest of the application is
more flexible, or you might be fighting an uphill battle for a long time.

However, if the application will drive forward even after an error, you
could pre-create the table "T" the way you want (esp. "c INT"), let the
application's "CREATE TABLE" call fail, and then the subsequent insert(s)
will succeed. Other than that, there is not much that can be easily done,
as the error that is thrown happens pretty early in the process.

Cheers,
Greg

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Cast INTEGER to BIT confusion
  2023-08-15 08:49 Cast INTEGER to BIT confusion [Quipsy] Markus Karg <karg@quipsy.de>
@ 2023-08-17 12:57 ` rob stone <floriparob@tpg.com.au>
  2023-08-17 13:14   ` Re: Cast INTEGER to BIT confusion Erik Wienhold <ewie@ewie.name>
  2 siblings, 1 reply; 6+ messages in thread

From: rob stone @ 2023-08-17 12:57 UTC (permalink / raw)
  To: [Quipsy] Markus Karg <karg@quipsy.de>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Cast INTEGER to BIT confusion
  2023-08-15 08:49 Cast INTEGER to BIT confusion [Quipsy] Markus Karg <karg@quipsy.de>
  2023-08-17 12:57 ` Re: Cast INTEGER to BIT confusion rob stone <floriparob@tpg.com.au>
@ 2023-08-17 13:14   ` Erik Wienhold <ewie@ewie.name>
  0 siblings, 0 replies; 6+ messages in thread

From: Erik Wienhold @ 2023-08-17 13:14 UTC (permalink / raw)
  To: rob stone <floriparob@tpg.com.au>; [Quipsy] Markus Karg <karg@quipsy.de>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> 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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2023-08-17 13:26 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-15 08:49 Cast INTEGER to BIT confusion [Quipsy] Markus Karg <karg@quipsy.de>
2023-08-15 11:48 ` Erik Wienhold <ewie@ewie.name>
2023-08-17 13:26   ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-16 01:02 ` Greg Sabino Mullane <htamfids@gmail.com>
2023-08-17 12:57 ` rob stone <floriparob@tpg.com.au>
2023-08-17 13:14   ` Erik Wienhold <ewie@ewie.name>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox