Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVsXK-001Sj0-1N for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 11:48:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qVsXH-00DfAE-QB for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 11:48:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVsXH-00DfA5-Cy for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 11:48:31 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVsXA-000HCr-Ds for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 11:48:30 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [10.196.197.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4RQ8fT5l0Kz9sdc; Tue, 15 Aug 2023 13:48:17 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1692100097; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=NWvFrw2MjZ62++KNg2Pb+Qw60xpPNVNhZUIFAc3cimY=; b=gvKnU3SB+uYfleG0N85deV1rNbbWr/kcynXqt8jopYsGydQkGR1CLGh7qXZI52STpJEmVK lxbLofTQbAp0kt72lOR9akhqyvYSSSaDH8T141/URBZEeVkx3ZUhe5NCFcZ2cglEHISBCY ZcFb+pPCMPJhyCOckkYNa2YsVasmELrmiQnsOjxSJ12w+4WiFBqWv2y4RDO75PGlMhpwbs NEMFSUp3RYX3ABD5uiQoLuFl/RRUMiitC6xI/X/FOS0ayxJxPu7O6gtp1uUUh5vUvmDQWB AeEpON0ndbgLBYJJ3l5mimI20C4EQYAruofowuc4SvlEUi/maBF5qmlxverFuw== Date: Tue, 15 Aug 2023 13:48:16 +0200 (CEST) From: Erik Wienhold To: "[Quipsy] Markus Karg" , "pgsql-general@lists.postgresql.org" Message-ID: <533487381.212078.1692100096551@office.mailbox.org> In-Reply-To: References: Subject: Re: Cast INTEGER to BIT confusion MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > 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