6

Problem

I tried to run \dp heroes in psql however it immediately threw the following! (heroes is the name of the table)

ERROR:  operator is not unique: unknown || "char"
LINE 16:            E' (' || polcmd || E'):'
                          ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

I googled around and found these but I am not clear what exactly is going on!
Ref: https://www.postgresql.org/message-id/[email protected]

What I have tried!

Trying to access it with schema name doesn't work as well

\dp public.heroes;

On initial read I thought that I have to cast it but I think I am wrong.

\dp heroes::"char";

Extra stuff

Btw just for info this is a Postgres instance running as a docker container.

Here is the query that it internally generates:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' END as "Type",
  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
    FROM pg_catalog.pg_attribute a
    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
  ), E'\n') AS "Column privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT polname
    || CASE WHEN NOT polpermissive THEN
       E' (RESTRICTIVE)'
       ELSE '' END
    || CASE WHEN polcmd != '*' THEN
           E' (' || polcmd || E'):'
       ELSE E':'
       END
    || CASE WHEN polqual IS NOT NULL THEN
           E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
       ELSE E''
       END
    || CASE WHEN polwithcheck IS NOT NULL THEN
           E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
       ELSE E''
       END    || CASE WHEN polroles <> '{0}' THEN
           E'\n  to: ' || pg_catalog.array_to_string(
               ARRAY(
                   SELECT rolname
                   FROM pg_catalog.pg_roles
                   WHERE oid = ANY (polroles)
                   ORDER BY 1
               ), E', ')
       ELSE E''
       END
    FROM pg_catalog.pg_policy pol
    WHERE polrelid = c.oid), E'\n')
    AS "Policies"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','p')
  AND c.relname OPERATOR(pg_catalog.~) '^(heroes)$' COLLATE pg_catalog.default
  AND n.nspname OPERATOR(pg_catalog.~) '^(public)$' COLLATE pg_catalog.default
ORDER BY 1, 2;

1 Answer 1

8

The cause of the problem is that commit 07eee5a0dc changed the type category of the internal data type "char" (which is the type of pg_policy.polcmd) from "string" to "internal". As a consequence, the type resolution rules for operators now require an explicit cast for the query behind \dp.

You get that error because you are using psql from a v14 or older with a PostgreSQL server that is v15 or newer. This kind of problem is to be expected; that's what the warning at the start of psql is about:

WARNING: psql major version 14, server major version 15.
         Some psql features might not work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.