N
Neon•2y ago
absent-sapphire

NeonDbError: data type json has no default operator class for access method "btree"

Hi all, I am using Drizzle ORM to create a Postgres schema like so:
export const Locations = vendorPortalSchema.table("locations", {
id: serial("id").primaryKey(),
location: json("location")
.$type<{
street: string;
city: string;
state: string;
zip: string;
}>()
.notNull()
.unique(),
description: text("description"),
organization_id: serial("organization_id").references(() => Organizations.id),
water_cost_per_cubic_foot: integer("water_cost_per_cubic_foot"),
energy_cost_per_kwh: integer("energy_cost_per_kwh"),
labor_cost_per_hour: integer("labor_cost_per_hour"),
created_at: timestamp("created_at").defaultNow(),
});
export const Locations = vendorPortalSchema.table("locations", {
id: serial("id").primaryKey(),
location: json("location")
.$type<{
street: string;
city: string;
state: string;
zip: string;
}>()
.notNull()
.unique(),
description: text("description"),
organization_id: serial("organization_id").references(() => Organizations.id),
water_cost_per_cubic_foot: integer("water_cost_per_cubic_foot"),
energy_cost_per_kwh: integer("energy_cost_per_kwh"),
labor_cost_per_hour: integer("labor_cost_per_hour"),
created_at: timestamp("created_at").defaultNow(),
});
I am able to successfully use Drizzle kit to generate the migration file, and then when i try pushing the migration, i get the error NeonDbError: data type json has no default operator class for access method "btree"
3 Replies
like-gold
like-gold•2y ago
Hi Nick, Can you please try with jsonb instead of json? A btree can only index data type that can be sorted into a well-defined linear order. JSON is non-scalar and non-ordered, meaning that it doesn't have a natural order. JSONB however supports indexing 🙂 See: https://www.postgresql.org/docs/15/btree-intro.html and https://www.postgresql.org/docs/15/datatype-json.html
PostgreSQL Documentation
67.1. Introduction
67.1. Introduction PostgreSQL includes an implementation of the standard btree (multi-way balanced tree) index data structure. Any data type that can …
PostgreSQL Documentation
8.14. JSON Types
8.14. JSON Types 8.14.1. JSON Input and Output Syntax 8.14.2. Designing JSON Documents 8.14.3. jsonb Containment and Existence 8.14.4. jsonb Indexing …
absent-sapphire
absent-sapphireOP•2y ago
Ah yes! This fixed it! I am curious now to when would JSON work instead of JSONB, and why not just always use JSONB? It's supposed to work according to Drizzle here: https://orm.drizzle.team/docs/column-types/pg#json
Drizzle ORM - next gen TypeScript ORM
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
ratty-blush
ratty-blush•2y ago
Yanic might correct me, bnut my understanding is that your "UNIQUE" constraint requires JSONB to reliably perform a comparisonm to ensure the constraint

Did you find this page helpful?