CODEWITHSUNDEEP
postgresqlpostgresql-9.3search-pathpostgresql-extensions
Michael Brown
Michael Brown

Reputation: 1741

Additional module citext is installed, but type "citext" not found?

I'm trying to run an inline query on my database - which has the citext extension installed (using CREATE EXTENSION) - and yet the executed query keeps throwing this error when calling a function:

type "citext" does not exist 

DO
LANGUAGE plpgsql
$$
DECLARE
  _id INT;
BEGIN
  SELECT * FROM "dbo"."MyFunction"(_id, 'some value'::citext);
END;
$$;

If I omit the ::citext cast, it says:

function dbo.MyFunction(integer, unknown) does not exist.
You might need to add explicit type casts.

The citext extension is added, is part of the schema and works with other queries. This keeps coming up randomly - what causes it?

EDIT: The installed extensions:

extname   | nspname
----------+-----------
plpgsql   | pg_catalog
citext    | public
uuid-ossp | public

Search path:

show search_path;
search_path
-----------
dbo

Upvotes: 2

Views: 3838

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658302

As suspected, the extension schema is missing from the search_path. Read up on how to set the schema search path in this related answer:

It seems like your client sets search_path = dbo on connection, which seems to be misconfigured. dbo is something we see a lot for SQL Server (used to be the default schema here or still is?), very untypical for Postgres. Not sure how you got there.

One alternative would be to install extensions into the dbo schema as well:

You can even move (most) extensions to a different schema:

ALTER EXTENSION citext SET SCHEMA dbo;

But I would advice to install extensions to a dedicated schema and include it in the search_path.

Leave plpgsql alone in any case. It's installed by default and should should stay in pg_catalog.

One way or another, clean up the mess with varying search_path settings.

As for the second question: that's guided by the rules of Function Type Resolution. The call cannot be resolved, because citext does not have an implicit cast to text.

Related

Upvotes: 2

Related Questions