HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Find current owner of custom data type?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
ownertypecustomcurrentfinddata

Problem

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 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?

Solution

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 | ...

Code Snippets

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

 usename 
─────────
 dezso
SELECT typowner::regrole 
  FROM pg_type t 
 WHERE typname = 'bla';
Schema       | Name | Internal name | Size | Elements |  Owner   | ...
-------------------+------+---------------+------+----------+----------+ ...
 zel_api_r14_00_09 | bla  | bla           | 4    | a        | postgres | ...

Context

StackExchange Database Administrators Q#126207, answer score: 6

Revisions (0)

No revisions yet.