Migrating from Oracle: One word about pg_type

04.2016 / Category: How To / Tags: migration

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:

test=# CREATE TABLE pg_type (id int);
CREATE TABLE

1 2	test=# CREATE TABLE pg_type (id int); CREATE TABLE

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:

test=# SELECT count(*) FROM pg_type;
 count
-------
  1879
(1 row)

1

2

3

4

5

test=# SELECT count(*) FROM pg_type;

count

-------

1879

(1 row)

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:

test=# SELECT count(*) FROM public.pg_type;
 count
-------
     0
(1 row)

1

2

3

4

5

test=# SELECT count(*) FROM public.pg_type;

count

-------

0

(1 row)

Without prefixing, even d is empty:

test=# d
No relations found.

1 2	test=# d No relations found.

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:

test=# SET search_path TO public, pg_catalog;
SET

test=# SELECT count(*) FROM pg_type;
 count
-------
0
(1 row)

1

2

3

4

5

6

7

8

test=# SET search_path TO public, pg_catalog;

SET

test=# SELECT count(*) FROM pg_type;

count

-------

0

(1 row)

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 Twitter, Facebook, or LinkedIn.