Minh Techie Posts - Answer Overflow

Minh Techie

•Created by Minh Techie on 10/16/2024 in #help

applying migrations... PostgresError: column "x" cannot be cast automatically to type x

when I modify a column from type text to enum

export const channelSubscriptionsTable = pgTable("channel_subscriptions", {
  ...
  subscription_type: text("subscription_type"),
});

export const channelSubscriptionsTable = pgTable("channel_subscriptions", {
  ...
  subscription_type: text("subscription_type"),
});

to this

export const subscriptionTypeEnum = pgEnum("subscription_type", [
  "free",
  "paid",
]);
export const channelSubscriptionsTable = pgTable("channel_subscriptions", {
  ...
  subscription_type: subscriptionTypeEnum("subscription_type")
});

export const subscriptionTypeEnum = pgEnum("subscription_type", [
  "free",
  "paid",
]);
export const channelSubscriptionsTable = pgTable("channel_subscriptions", {
  ...
  subscription_type: subscriptionTypeEnum("subscription_type")
});

I got this error

 severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42804',
  hint: 'You might need to specify "USING subscription_type::subscription_type".',
  file: 'tablecmds.c',
  line: '12310',
  routine: 'ATPrepAlterColumnType'

 severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42804',
  hint: 'You might need to specify "USING subscription_type::subscription_type".',
  file: 'tablecmds.c',
  line: '12310',
  routine: 'ATPrepAlterColumnType'

How do I fix this?

1 replies

DTDrizzle Team

•Created by Minh Techie on 9/18/2024 in #help

drizzle-kit generate doesn't run custom sql

In addition to my schema.ts file to define tables, I also have functions_and_triggers.ts to create functions and triggers for Supabase

export const customAccessTokenHook = async () => {
  await db.execute(sql`
    CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
    RETURNS jsonb
    LANGUAGE plpgsql
    AS $$
    BEGIN
       // do stuff here
    END;
    $$;
  `);
};

export const customAccessTokenHook = async () => {
  await db.execute(sql`
    CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
    RETURNS jsonb
    LANGUAGE plpgsql
    AS $$
    BEGIN
       // do stuff here
    END;
    $$;
  `);
};

However when I run drizzle-kit generate it only runs the table creation and not the other file Here's my drizzle.config.ts

export default defineConfig({
  dialect: 'postgresql',
  schemaFilter: ['public'],
  schema: './src/lib/db/schema/*',
  out: './supabase/migrations',
  dbCredentials: {
    url: process.env.DATABASE_URL || 'postgresql://postgres:[email protected]:54322/postgres'
  }
});

export default defineConfig({
  dialect: 'postgresql',
  schemaFilter: ['public'],
  schema: './src/lib/db/schema/*',
  out: './supabase/migrations',
  dbCredentials: {
    url: process.env.DATABASE_URL || 'postgresql://postgres:[email protected]:54322/postgres'
  }
});

4 replies

Gaming

Programming