0

I have been trying to create a table column of type "citext" in postgres.

db=# CREATE TABLE users (
  id serial PRIMARY KEY,
  name citext NOT NULL,
  email citext NOT NULL
);

ERROR:  type "citext" does not exist
LINE 3:   name citext NOT NULL,

But getting this error. On surfing through internet, it was said to

CREATE EXTENSION citext;

I verified the creation using command,

SELECT * FROM pg_extension WHERE extname = 'citext';

It was created.

But even after this I keep on getting the error,

ERROR:  type "citext" does not exist

Does anyone knows how to resolve this?

user1221324
  • 1
  • 1
  • 2
    Does this answer your question? [Postgresql: ERROR: type "citext" does not exist](https://stackoverflow.com/questions/15981197/postgresql-error-type-citext-does-not-exist) – nbk Aug 20 '23 at 13:37
  • On surfing through internet, it was said to CREATE EXTENSION citext; I verified the creation using command, SELECT * FROM pg_extension WHERE extname = 'citext'; It was created. But even after this I keep on getting the error, ERROR: type "citext" does not exist – user1221324 Aug 20 '23 at 13:41
  • 1
    as you see in my link, for **every** database you need to run the command, that is a postgres thing – nbk Aug 20 '23 at 13:50
  • Best bet is the extension is not in your [search_path](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH). In `psql` run `show search_path;` and add the result to your question. Also do `SELECT extnamespace::regnamespace FROM pg_extension WHERE extname = 'citext';` and add the result to your question. – Adrian Klaver Aug 20 '23 at 15:18

1 Answers1

1

Here is the code you need to run to resolve the issue:

CREATE EXTENSION IF NOT EXISTS citext;

This will ensure that the citext extension is created if it doesn't exist. After running this command, you should be able to create a table column with the citext data type without encountering the "type does not exist" error.

boyle pence
  • 26
  • 1
  • I did. I verified the creation using command, `SELECT * FROM pg_extension WHERE extname = 'citext';` It was created. But even after this I keep on getting the error, `ERROR: type "citext" does not exist` How to resolve this? – user1221324 Aug 20 '23 at 15:03
  • What schema was `citext` installed, and do you have access to the schema? – Belayer Aug 21 '23 at 01:26
  • I have `set search_path to a schema` and then executed this. `CREATE EXTENSION IF NOT EXISTS citext;` Yes, I have access to the schema. – user1221324 Aug 21 '23 at 03:47