CODEWITHSUNDEEP
postgresql-9.1
NullException
NullException

Reputation: 4510

Postgresql: ERROR: type "citext" does not exist

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?

Upvotes: 112

Views: 65092

Answers (4)

NullException
NullException

Reputation: 4510

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;

Upvotes: 172

Viktor Johansson
Viktor Johansson

Reputation: 381

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.

Upvotes: 4

Ilya Petukhov
Ilya Petukhov

Reputation: 754

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

Upvotes: 21

shaunc
shaunc

Reputation: 5611

@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.

Upvotes: 34

Related Questions