Non negative integer column type

[laurent512]

Hi,

What is the easiest approach to have a columns with type integer for only 0 & strictly positive values ?

Thank you in advance :)

[ceasermikes002]

In Drizzle, the easiest way to enforce a column to accept only 0 and strictly positive integer values is by using int type with a CHECK constraint.

If you're using PostgreSQL, you can define it like this:

`import { integer, pgTable } from "drizzle-orm/pg-core";

export const myTable = pgTable("my_table", {
amount: integer("amount").notNull().default(0).check("amount >= 0"),
});`

[RaimundoDiaz]

Hi, I'm trying to accomplish the same thing. I tried your approach but I get the error:
Property 'check' does not exist on type 'HasDefault<NotNull<PgIntegerBuilderInitial<"amount">>>'.
And if I try to generate the migration anyway I get:
TypeError: (0 , import_pg_core.integer)(...).notNull(...).default(...).check is not a function

I managed to do it using this:

import { sql } from "drizzle-orm";
import { check, integer, pgTable } from "drizzle-orm/pg-core";


export const myTable = pgTable("my_table", {
  amount: integer("amount").notNull().default(0)
},
(table) => ([
  check(
    "amount is non-negative",
    sql`${table.amount} >= 0`
  )
]));

You can see the docs here:
Drizzle ORM/Indexes & Constraints/Check