3

In Postgresql, when I try to run ALTER TYPE x ADD VALUE y; on a custom datatype I get an error stating PG::Error: ERROR: must be owner of type x.

I know this can be solved by running ALTER TYPE x OWNER TO <database owner> but what I'm curious about is how I can check the current owner of this specific datatype. \dT+ x does not give me information about the current owner.

How can I extract more information about custom data types, including the current owner?

5

Up until 9.4

You can go and ask the system catalogs (pg_type, to be precise):

SELECT rolname 
  FROM pg_type t 
  JOIN pg_authid r ON typowner = r.oid 
 WHERE typname = 'bla';

 usename 
─────────
 dezso

From PostgreSQL 9.5 on

From this version on, there is a new object identifier type called regrole, and it makes the query a bit simpler:

SELECT typowner::regrole 
  FROM pg_type t 
 WHERE typname = 'bla';

But there is more to it - the new 9.5 version of psql already displays the owner when using \dT+:

      Schema       | Name | Internal name | Size | Elements |  Owner   | ...
-------------------+------+---------------+------+----------+----------+ ...
 zel_api_r14_00_09 | bla  | bla           | 4    | a        | postgres | ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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