postgresqlcreate-tablepostgresql-9.1sql-types

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

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?

Solution

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

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

    in versions >= 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;