Postgresql 9.1: ERROR: type "citext" does not exist

Question

I am trying to execute following query through PgAdmin utility. ``` CREATE TABLE svcr."EventLogs" ("eventId" BIGINT NOT NULL, "eventTime" TIMESTAMP WITH TIME ZONE NOT NULL, "userid" CITEXT, "realmid" CITEXT NOT NULL, "onUserid" CITEXT, "description" TEXT, CONSTRAINT eventlogs_pkey PRIMARY KEY ("eventId")); ``` And I get following error - ``` ERROR: type "citext" does not exist SQL state: 42704 Character: 120 ``` However, following query runs fine - ``` CREATE TABLE svcr."CategoryMap" ("category" INT NOT NULL, "userData" INT NOT NULL); ``` What is wrong with the first query?

Accepted Answer

what version of pg are you using? in < 8.4, citext can be installed as an add-on:

http://pgxn.org/dist/citext/

in >= 8.4 it should be available in core.

there are also some upgrade notes for 9.1.2 here:

http://www.postgresql.org/docs/9.1/static/release-9-1-2.html

you may need to load the citext extension:

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA ext;

Postgresql 9.1: ERROR: type "citext" does not exist

Solution

Problem