What is the difference between text and pg_catalog.text?

Question

I have a classic 3-environments setting (dev, staging and production), each with its own RDS PostgreSQL 9.3. The client/server versions are psql (9.3.5, server 9.3.3).

In order to keep the schemas aligned, I have a periodic schema diff job, which notifies me when the schemas differ.

A few days ago, I started getting many of these diffs:

CREATE TABLE ...
...
<     title pg_catalog.text,
<     subtitle pg_catalog.text,
---
>     title text,
>     subtitle text,

The text fields in staging was somehow converted to pg_catalog.text in prod.

What is the difference between text and pg_catalog.text? How can I equate the two schemas?

Answer 1

They are the same thing in a default installation.

pg_catalog.text is the schema-qualified version of the type name text.

The search_path always contains pg_catalog automatically. Per documentation:

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly place pg_catalog at the end of your search path if you prefer to have user-defined names override built-in names.

Bold emphasis mine. If you do such a thing, Postgres will automatically start schema-qualifying names in text-representation, that would otherwise be resolved differently. That's most probably what you see. Check your current search_path with:

SHOW search_path;

Closely related:

• How can I fake inet_client_addr() for unit tests in PostgreSQL?

• How to create table inside specific schema by default in Postgres?