Extension created for public schema not available within database

Short answer

You are actually using (at least) two databases. You are installing CITEXT in the wrong one.

Longer answer

In the first database (whose name is not shown), you install CITEXT. This database is probably the postgres database, which is created by default when you install PostgreSQL.

Then you use the \c command and you switch to another database (sensordata). You need to create your extension in this database.

That is, you should do:

\c sensordata 
CREATE EXTENSION citext;
\dx

If you need to use the citext module in more databases, you need to install it in each one. The extensions on the other databases don't matter. Databases do not interfere with each other.

Every PostgreSQL cluster (=database installation) has (by default) one database named postgres. If you use psql, it is the one to which you connect "by default". You don't actually need to have a postgres database. I guess the postgresdatabase is where you installed the CITEXT extension.

Within every PostgreSQL database, there is, by default, one public schema.


PostgreSQL does not work like (for instance) MySQL, where you can acces several databases at once through a single connection (provided you have a user with the right permissions).

In PostgreSQL you access one database through a single connection. Within a database, you can access as many schemas as needed. The role that schemas play in PostgreSQL is nearly the same as the one played by a database in MySQL.

See also:

  • Relationship between catalog, schema, user, and database instance
  • What's the difference between a catalog and a schema in a relational database?