Postgresql: ERROR: type "citext" does not exist

Question

I have read other posts, when searching, an answer to this question.

I am using PostgreSQL 9.1, and created extension 'citext' using CREATE EXTENSION citext, but when I try to create any columns of type 'citext', it throws this error

ERROR: type "citext" does not exist

I researched but did not find any concrete answers? Any idea why?

score 161 · Accepted Answer · edited Dec 26 '22 at 03:33

161

Ok figured it out. I have several databases and CREATE EXTENSION citext has to be run for each db to install the extension in that DB. You must do on psql prompt:

psql =# \c db_1
CREATE EXTENSION citext;

psql =# \c db_2
CREATE EXTENSION citext;

edited Dec 26 '22 at 03:33

starball

20,030
7
43
238

answered Apr 12 '13 at 21:46

NullException

4,232
8
24
44

2

Is there any way of making this extension globally, so that every database can use it? – Mike H-R Jan 30 '17 at 12:19
2

You can also automate this process as described in the docs https://docs.djangoproject.com/en/2.1/ref/contrib/postgres/operations/#creating-extension-using-migrations. Remember that your postgres user needs to be superuser. – tread Aug 28 '18 at 11:37
@MikeH-R [shaunc's answer can help you](https://stackoverflow.com/questions/15981197/postgresql-error-type-citext-does-not-exist#answer-45982923) – Maxim Mandrik Feb 13 '19 at 21:56
Thanks, it worked for me. For pgAdmin Users: Right-click on the Database name, open the Query Tool, and simply execute `CREATE EXTENSION citext;` Then you can create tables with citext columns in this database. – SBS Apr 21 '20 at 17:19

score 33 · Answer 2 · answered Aug 31 '17 at 13:40

33

@NullException is correct that the extension needs to be created in each database. If you want to automatically have an extension created, you can create it in the template1 database which (by default, at least) is the database used as a model for "create database", so with appropriate permissions, in psql:

\c template1
create extension citext;

Then new databases will include citext by default.

answered Aug 31 '17 at 13:40

shaunc

5,317
4
43
58

3

This answer was helpful for me in running Django unittests which automatically creates a new test database for the tests and then drops it when done. That precludes the ability to connect to the database to create the extension. – Doug Harris Jan 08 '19 at 16:15
*＋1* for keeping the site tidy. – jww Dec 14 '19 at 02:11

score 20 · Answer 3 · answered Sep 11 '19 at 15:32

To use citext, use the CITextExtension operation to setup the citext extension in PostgreSQL before the first CreateModel migration operation.

https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/fields/#citext-fields

from django.contrib.postgres.operations import CITextExtension

class Migration(migrations.Migration):
    ...

    operations = [
        CITextExtension(),
        ...
    ]

similarly to HStoreField as https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/operations/#create-postgresql-extensions

Thanks a bunch for this. I was 20 migrations deep when I implemented this. On migration 21, it works just as well as adding it into the first migration. — Luke Dupin, Sep 01 '20 at 04:39

score 4 · Answer 4 · answered Oct 20 '19 at 18:52

If you use Docker, and want to add this extension to your database,

I have done the following,

# Dockerfile
FROM postgres:11.3

# Adds the CIText Extension to our database
COPY ./compose/production/postgres/initdb_citext.sh /docker-entrypoint-initdb.d/citext.sh

And my initdb_citext.sh:

#!/bin/sh

# Adds the citext extension to database and test database
"${psql[@]}" <<- 'EOSQL'
CREATE EXTENSION IF NOT EXISTS citext;
\c template1
CREATE EXTENSION IF NOT EXISTS citext;
EOSQL

This applies the extension to test databases that django generates too.

Postgresql: ERROR: type "citext" does not exist

4 Answers4

Linked