> On 17/08/2023 09:31 CEST [Quipsy] Markus Karg <karg@xxxxxxxxx> wrote: > > 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. > > -----Ursprüngliche Nachricht----- > Von: Erik Wienhold <ewie@xxxxxxxxx> > Gesendet: Dienstag, 15. August 2023 16:28 > An: [Quipsy] Markus Karg <karg@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx > Betreff: Re: AW: Cast INTEGER to BIT confusion > > > On 15/08/2023 14:02 CEST [Quipsy] Markus Karg <karg@xxxxxxxxx> 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 <ewie@xxxxxxxxx> > > Gesendet: Dienstag, 15. August 2023 13:48 > > An: [Quipsy] Markus Karg <karg@xxxxxxxxx>; > > pgsql-general@xxxxxxxxxxxxxxxxxxxx > > 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 It looks like Postgres resolves standard SQL types without considering the search path. This could be a feature (perhaps mandated by the SQL standard?) and not a bug if the rationale here is to avoid shadowing of standard types with custom types so that type bit, for example, always behaves as the standard bit type. In the following script I create several domains in schema xxx: bit and float match standard SQL type names, domain foo does not conflict with any built-in type name, and inet is a built-in type but not a standard type. BEGIN; CREATE SCHEMA xxx; GRANT USAGE ON SCHEMA xxx TO current_user; CREATE DOMAIN xxx.bit AS int; CREATE DOMAIN xxx.float AS int; CREATE DOMAIN xxx.foo AS int; CREATE DOMAIN xxx.inet AS int; CREATE DOMAIN pg_catalog.foo AS int; \dD *.(bit|float|foo|inet) SET LOCAL search_path = xxx, pg_catalog; SELECT current_schemas(true); CREATE TABLE public.t (f1 bit, f2 float, f3 inet, f4 foo); SET LOCAL search_path = ''; \d public.t ROLLBACK; We see that table t is created with the standard SQL types instead of our custom domains. Only xxx.inet and xxx.foo are resolved according to the search path. BEGIN CREATE SCHEMA GRANT CREATE DOMAIN CREATE DOMAIN CREATE DOMAIN CREATE DOMAIN CREATE DOMAIN List of domains Schema | Name | Type | Collation | Nullable | Default | Check ------------+-------+---------+-----------+----------+---------+------- pg_catalog | foo | integer | | | | xxx | bit | integer | | | | xxx | float | integer | | | | xxx | foo | integer | | | | xxx | inet | integer | | | | (4 rows) SET current_schemas ------------------ {xxx,pg_catalog} (1 row) CREATE TABLE SET Table "public.t" Column | Type | Collation | Nullable | Default --------+------------------+-----------+----------+--------- f1 | bit(1) | | | f2 | double precision | | | f3 | xxx.inet | | | f4 | xxx.foo | | | ROLLBACK -- Erik
