CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
halfwarp
halfwarp

Reputation: 1790

Failed to create citext column with psycopg2

As the tile says I'm trying to create a table with one column as citext.

I have manually created the extension on the database.

I can create a table with success directly from PSQL:

CREATE TABLE test (nickname citext PRIMARY KEY, full_name TEXT NOT NULL);

The above SQL works.

However, whenever I try to execute the same SQL with a psycopg2 connection, it fails with:

type "citext" does not exist
LINE 1: CREATE TABLE test (nickname citext PRIMARY KEY, full_nam...

Any ideas?

EDIT:

Output of \dT citext:

      List of data types
 Schema |  Name  | Description
--------+--------+-------------
 public | citext |
(1 row)

EDIT 2:

Here's the manual creation process from PSQL:

mydb=> \d
No relations found.
mydb=> CREATE TABLE test (nickname citext PRIMARY KEY, full_name TEXT NOT NULL);
CREATE TABLE
mydb=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+--------
 sentry | test | table | sentry
(1 row)

mydb=> \d test
      Table "sentry.test"
  Column   |  Type  | Modifiers
-----------+--------+-----------
 nickname  | citext | not null
 full_name | text   | not null
Indexes:
    "test_pkey" PRIMARY KEY, btree (nickname)

And here's the python script:

import psycopg2

try:
    conn = psycopg2.connect(database = "mydb", user = "sentry", password = "...", host = "...", port = "5432")
except Exception as err:
    print(err)

cur = conn.cursor()
try:
    cur.execute("SET search_path TO sentry")
    cur.execute("CREATE TABLE test (nickname citext PRIMARY KEY, full_name TEXT NOT NULL);")
except Exception as err:
    print(err)

Upvotes: 1

Views: 1103

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247545

There are two possibilities:

  • You created the extension in a schema that is not on your search_path in the psycopg2 connection.

  • You are connecting to different databases (perhaps you created the extension in postgres).

Upvotes: 2

Related Questions