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 1qWctL-003q9B-0G for pgsql-general@arkaria.postgresql.org; Thu, 17 Aug 2023 13:18:23 +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 1qWctJ-00HJ1E-FT for pgsql-general@arkaria.postgresql.org; Thu, 17 Aug 2023 13:18:21 +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 1qWctJ-00HJ14-3T for pgsql-general@lists.postgresql.org; Thu, 17 Aug 2023 13:18:21 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qWctF-000dDA-PP for pgsql-general@lists.postgresql.org; Thu, 17 Aug 2023 13:18:19 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 37HDIEk21819967; Thu, 17 Aug 2023 09:18:14 -0400 From: Tom Lane To: "[Quipsy] Markus Karg" cc: Erik Wienhold , "pgsql-general@lists.postgresql.org" Subject: Re: AW: AW: Cast INTEGER to BIT confusion In-reply-to: <015a3586e6dc40e0b1c3a4a1f3b1e44c@quipsy.de> References: <533487381.212078.1692100096551@office.mailbox.org> <84bd46dba5ec4097ba9663f46a1e5754@quipsy.de> <400164688.228966.1692109673749@office.mailbox.org> <015a3586e6dc40e0b1c3a4a1f3b1e44c@quipsy.de> Comments: In-reply-to "[Quipsy] Markus Karg" message dated "Thu, 17 Aug 2023 07:31:27 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1819965.1692278294.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 17 Aug 2023 09:18:14 -0400 Message-ID: <1819966.1692278294@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "[Quipsy] Markus Karg" writes: > I am giving up. While even `SELECT current_schemas(true)` correctly prin= ts `xxx, pg_catalog` it still uses the original bit type. This is complete= ly 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. 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. Very sad. You could leave the type alone and instead fool with the properties of the cast (see [1]). As superuser: regression=3D# create table t (f1 bit); CREATE TABLE regression=3D# 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=3D# select * from pg_cast where castsource =3D 'integer'::regty= pe and casttarget =3D 'bit'::regtype; oid | castsource | casttarget | castfunc | castcontext | castmethod = -------+------------+------------+----------+-------------+------------ 10186 | 23 | 1560 | 1683 | e | f (1 row) regression=3D# update pg_cast set castcontext =3D 'a' where castsource =3D= 'integer'::regtype and casttarget =3D 'bit'::regtype; UPDATE 1 regression=3D# select * from pg_cast where castsource =3D 'integer'::regty= pe and casttarget =3D 'bit'::regtype; oid | castsource | casttarget | castfunc | castcontext | castmethod = -------+------------+------------+----------+-------------+------------ 10186 | 23 | 1560 | 1683 | a | f (1 row) regression=3D# 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