I am writing a script in which I need to parse the name of a table (in regclass). The parsing (with parse_ident()) works so far. However, the script fails when the table is in the public schema because PostgreSQL (10.3) automatically removes the schema name.

For example, if a table tt is in a non-public schema ex, the text value of the regclass is the same as the original:

=> select 'ex.tt'::regclass::text;

When it's in public, the schema name is lost:

=> select 'public.tt'::regclass::text;

Is there an way to disable this behavior, or to convert to text without losing the schema name?


This is not related to the schema public per se - which is just another schema with no special powers except that it's created by default - and included in the search_path by default.

The current search_path is behind this. The text representation of a regclass value builds on it. The table name is only schema-qualified if the schema is not the first match in the search_path anyway. The manual:

The regclass input converter handles the table lookup according to the schema path setting, and so it does the “right thing” automatically. Similarly, casting a table's OID to regclass is handy for symbolic display of a numeric OID.

You could force Postgres to print the schema name by setting an empty search_path (locally):

SET LOCAL search_path = '';
SELECT 'public.tt'::regclass::text;

But that forces you to schema-qualify everything for the rest of the transaction or until you set the search_path again. Well, most everything, pg_catalog and pg_temp are special. See:

Or you avoid the special cast and the search_path and get the schema name from pg_class directly:

SELECT relnamespace::regnamespace::text
FROM   pg_catalog.pg_class
WHERE  oid = 'public.tt'::regclass;

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.