patternsqlMinor
Find current owner of custom data type?
Viewed 0 times
ownertypecustomcurrentfinddata
Problem
In Postgresql, when I try to run
I know this can be solved by running
How can I extract more information about custom data types, including the current owner?
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 (
From PostgreSQL 9.5 on
From this version on, there is a new object identifier type called
But there is more to it - the new 9.5 version of
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
─────────
dezsoFrom 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
─────────
dezsoSELECT 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.