0

I have created a new Schema in my Postgres db. I want to replace an OID in the pg_class table with the OID of the new Schema. But I don't know where to find the OID of the newly created Schema. Can somebody help me with the right queries?

2

Do NOT change the system catalogs manually!


If you want to move a table to a different schema, there is no need to know the oid of the table or the schema:

As documented in the manual you can easily do this through ALTER TABLE

ALTER TABLE old_schema.some_table
    SET SCHEMA new_schema;

without having to check or know any oid.

0

The OID can be found in the system catalog pg_namespace.

The simple way to get it: cast to the object identifier type regnamespace while connected to the same DB. (Then cast to oid, text or bigint for display.):

SELECT 'public'::regnamespace::oid;

Or, to avoid exceptions (requires Postgres 9.5+):

SELECT to_regnamespace('public')::oid;

See:

But for your case consider a_horse's advice first. (Don't mess with the system catalogs!)

-1

You can run this query to get OID.

SELECT oid FROM pg_class WHERE relname = 'tbl_name' AND relkind = 'r';

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.