Migrating from Oracle: One word about pg_type

After spending pretty much the entire week on freeing a customer from the chains of Oracle's license policy, I found an interesting issue, which made me smile: When the Oracle code was built 15 years ago or so, somebody created a table called “pg_type” on the Oracle side as part of the application. With only Oracle in mind, this is absolutely fine. However, now, 15 years and thousands of installations later, this thing has to be ported to PostgreSQL without renaming existing tables. Remember, there are thousands of installations in the field and most of them have no remote connection or are simple in scary locations (Iraq, Afghanistan, etc.).

So, here is some pg_type related trickery, which might be entertaining or maybe even enlightening:

The first observation is that a table called “pg_type” can easily be created on the PostgreSQL side.

Let us see, what happens, when we try to read the table:

Well, this does not seem to be what we wanted. The reason is: In PostgreSQL there is a system table carrying the same name. It stores information about data types and all that. Logically system tables are always taken into consideration first, so in our case we see more than just an empty new table.

To solve  that the table has to be explicitly prefixed:

Without prefixing, even d is empty:

Playing with search_path

In PostgreSQL there is a thing called search_path, which allows you to tell the system, what to look for where. The interesting part is that you can actually add the pg_catalog schema to the search_path to control things:

Adding pg_catalog after public elegantly solves the problem.

If you want to stay away from trickery, just renaming the table still is an option …

In case you need any assistance, please feel free to contact us.
 

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.