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?
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 old_schema.some_table SET SCHEMA new_schema;
without having to check or know any oid.
The OID can be found in the system catalog
The simple way to get it: cast to the object identifier type
regnamespace while connected to the same DB. (Then cast to
bigint for display.):
Or, to avoid exceptions (requires Postgres 9.5+):
- How to get the schema name of a table of type regclass in PostgreSQL?
- How to check if a table exists in a given schema
But for your case consider a_horse's advice first. (Don't mess with the system catalogs!)
You can run this query to get OID.
SELECT oid FROM pg_class WHERE relname = 'tbl_name' AND relkind = 'r';