#Case Insensitive Uniqueness

12 messages · Page 1 of 1 (latest)

median falcon
#

Is there a way to have Case Insensitive Uniqueness? In combination with PostgreSQL

potent bluff
#

You need to enable the postgres extension type citext and use that as the type of the column. (it's buult in, you just have to enable it with CREATE EXTENSION citext;)

median falcon
#

Aha. I have confirmed that the citext extension is installed:

oid extname extversion
104274 citext 1.6

I am trying to change this:

  @Column()
  name?: string;

to

  @Column({ type: 'citext' })
  name?: string;

The name column does not contain any null values, but it is complaining about having null values.

query failed: ALTER TABLE "creative" ADD "name" citext NOT NULL
error: error: column "name" of relation "creative" contains null values
  ****************************************************
    at Socket.emit (node:events:520:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 144,
  severity: 'ERROR',
  code: '23502',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'creative',
  column: 'name',
  dataType: undefined,
  constraint: undefined,
  file: 'tablecmds.c',
  line: '5849',
  routine: 'ATRewriteTable'
}
potent bluff
#

The name column does not contain any null values
that's about the already existing data in the database. Some records have null in that column

median falcon
# median falcon

As shown here, the existing column is already set to NOT NULL, it's impossible to have a null value there

potent bluff
#

Hm, right, I missed that. Just to be sure, does the error happen with an empty table?

median falcon
potent bluff
#

Without some reproducible example, I have no idea. But I'm sure you'll solve it

median falcon
#

Tried commenting it out, so it seems that is not the case

median falcon