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 1qVv3G-001aCl-Px for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 14:29:43 +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 1qVv2H-00EmIR-Go for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 14:28:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVv1i-00Ehxb-1P for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 14:28:06 +0000 Received: from mout-u-107.mailbox.org ([2001:67c:2050:101:465::107]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVv1d-000H1P-Uh for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 14:28:04 +0000 Received: from smtp2.mailbox.org (smtp2.mailbox.org [IPv6:2001:67c:2050:b231:465::2]) (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-107.mailbox.org (Postfix) with ESMTPS id 4RQDBf10MKz9scZ; Tue, 15 Aug 2023 16:27:54 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1692109674; 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=g6SgHCkqbdneFDMqPeFzh5rNQeAh/4MlNe8PBxmJ9bs=; b=SPxwDMehnIB4Cf36a3F46BuV2DOyMDaM+xZA4OoHnuaqLXI7NDXgLUWw36gEkUMSiXfh7f JWUFhVOCyj70Blf75a+tgq9vV3egbzQsqSa85Exh7pkd/Alc0KQUi91qMmUkJ8sDAhBXzP MR9yYArBK+y/3B1xamk54yAJJQwututQtf8vX5dd2aZk02xQ2umXeM2bAu7GrOfaN55XSW Q3oNguZOTsZk/UE61n2BHYIsH6QdjlfGQ3LuJ9x8L5rme8vl3pdeOB/YD/gyCsnp+ftUS0 TfVuBsbzH5o7Kog3jOSs5CC52P6qZbutRZaEEwJZbwOMsN7zAeJ8H/wBAqSziA== Date: Tue, 15 Aug 2023 16:27:53 +0200 (CEST) From: Erik Wienhold To: "[Quipsy] Markus Karg" , "pgsql-general@lists.postgresql.org" Message-ID: <400164688.228966.1692109673749@office.mailbox.org> In-Reply-To: <84bd46dba5ec4097ba9663f46a1e5754@quipsy.de> References: <533487381.212078.1692100096551@office.mailbox.org> <84bd46dba5ec4097ba9663f46a1e5754@quipsy.de> Subject: Re: AW: Cast INTEGER to BIT confusion MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4RQDBf10MKz9scZ List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > 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=C3=BCngliche Nachricht----- > Von: Erik Wienhold =20 > 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 an= d 1 > and don't use bit string functions. The search path must be changed so t= hat > domain bit overrides pg_catalog.bit: >=20 > =09=3D# CREATE SCHEMA xxx; > =09=3D# CREATE DOMAIN xxx.bit AS int; > =09=3D# SET search_path =3D xxx, pg_catalog; > =09=3D# CREATE TABLE t (c bit); > =09=3D# INSERT INTO t VALUES (1); > =09INSERT 0 1 > > But I would do that only if the third-party code cannot be tweaked becaus= e > the custom domain could be confusing. It's also prone to errors as it re= lies > on a specific search path order. Also make sure that regular users canno= t > create objects in schema xxx that would override objects in pg_catalog. Hmm, I thought that Postgres resolves all types through the search path, bu= t 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 have= n't tested the search path feature before posting.) Neither [1] or [2] mention that special (?) case or if there's a distinctio= n between built-in types and user-defined types. The USAGE privilege is requ= ired 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-SEARC= H-PATH -- Erik