Check constraint on bool column with EF Core and Postgres

Question

I'm using EF Core with Postgres. I want to create a "Configuration" table with one row only, which means:

• adding bit column with default value
• adding check constraint to ensure column has that value
• adding unique constraint on that column

So I added a shadow column like this:

builder.ToTable(x => x.HasCheckConstraint("CK_Configuration_IsSingleRow", "IsSingleRow = false"));
builder.Property<bool>("IsSingleRow").HasDefaultValue(false).IsRequired();
builder.HasIndex("IsSingleRow").IsUnique();

But I get an error:

Npgsql.PostgresException: operator does not exist: boolean = integer
MessageText: operator does not exist: boolean = integer
Hint: No operator matches the given name and argument types.

I tried IsSingleRow = true, IsSingleRow = 'true', IsSingleRow = 1 and IsSingleRow = '1'.

What is the correct syntax?

Answer 1

PostgreSQL folds unquoted identifiers to lower-case; that means you need to quote the column name in your check constraint SQL:

builder.ToTable(x => x.HasCheckConstraint("CK_Configuration_IsSingleRow", "\"IsSingleRow\" = true"));

You should also be able to omit the = true, since your IsSingleRow column is already a boolean.