4

I am trying to setup a fresh database on a new system but I got this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) collation "NOCASE" for encoding "UTF8" does not exist
LINE 4:  email VARCHAR(120) COLLATE "NOCASE" NOT NULL, 
                            ^

[SQL: 
CREATE TABLE users (
    id SERIAL NOT NULL, 
    email VARCHAR(120) COLLATE "NOCASE" NOT NULL,
    password VARCHAR(128) NOT NULL
)
]

I haven't changed anything but this works in 2 different computer and one of them runs Postgresql 10. I run this query using SqlAlchemy upgrade just for info.

I created database using this:

CREATE DATABASE db
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'tr_TR.utf8'
    LC_CTYPE = 'tr_TR.utf8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;
jarlh
  • 42,561
  • 8
  • 45
  • 63
maydin
  • 195
  • 1
  • 3
  • 15
  • Maybe someone [created](https://www.postgresql.org/docs/current/sql-createcollation.html) that collation manually on those servers where it works. –  Nov 27 '19 at 09:25
  • I installed postgres on both machines (Linux and Windows Server 2008). I didn't make additional configuration on both. Collation in Windows machine (Postgres 10) is Turkish_Turkey.1254, but it just come as default. – maydin Nov 27 '19 at 09:30
  • 2
    Well, `"NOCASE"` is not a built-in collation. If that statements works on one installation, then that collation **must** have been created manually. –  Nov 27 '19 at 09:31
  • 3
    The [source](https://github.com/sqlalchemy/sqlalchemy/blob/985da4c506133bb5d4f09697fd0a4b504ba834f3/test/requirements.py#L1252) suggests that your SqlAlchemy client is using the SQLite dialect, not the Postgres dialect. Check your configuration. – Nick Barnes Nov 27 '19 at 09:52
  • I checked the other system and there is nothing related to 'nocase' in Postgres. There can be more than one user using same email address. Interesting that SqlAlchemy didn't warn me before, or I just missed. But still it created tables without problem. – maydin Nov 27 '19 at 15:01

0 Answers0