In case this helps others, I was trying to have a "full text search" tsvector column be auto-generated upon every update using Postgres' GENERATED ALWAYS AS feature. Since that's not jiving with prisma, here's my current workaround using PG triggers:

Set a default empty tsvector & Gin index

model table {
  id      String                   @id
  stuff   String?
  fts     Unsupported("tsvector")? @default(dbgenerated("''::tsvector"))

  @@index([fts], type: Gin)
}

Manually add an update/insert trigger

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;

create or replace function update_table_fts() returns trigger as $$
begin
  NEW.fts := to_tsvector('english',
    NEW.id::text
    || ' ' || COALESCE(NEW."stuff", '')
  );

  RETURN NEW;
end; $$ language plpgsql security definer set search_path = public, pg_temp;

drop trigger if exists "update_fts" on "table";
create trigger "update_fts"
  before insert or update on "table"
  for each row
  execute function update_table_fts();

Pretty simple and honestly I'm fine with using this until the coming Prisma FTS improvements land.

I am facing this exact issue, is there any update on this?

@jkcorrea thank you for the workaround, although I'm not very well versed with SQL, but the for each row above does not seem ideal when there are millions of rows on the affected table, or am I wrong?

EDIT:
The for each row clause is not an issue as it will run only on "each row" of the INSERT transaction.

The easiest way to not get this behavior is to db pull the data model in the database. I get this:

datasource db {
  provider = "postgresql"
  url      = "postgres://postgres:prisma@localhost:5438/postgres"
}

model table {
  id            String                   @id
  hereBeDragons Unsupported("tsvector")? @default(dbgenerated("to_tsvector('english'::regconfig, id)"))
}

Now, the next migrate will not try to drop the default constraint.

Although an empty @default() should at least give a validation error, which would be less confusing.

The easiest way to not get this behavior is to db pull the data model in the database. I get this:

datasource db {
  provider = "postgresql"
  url      = "postgres://postgres:prisma@localhost:5438/postgres"
}

model table {
  id            String                   @id
  hereBeDragons Unsupported("tsvector")? @default(dbgenerated("to_tsvector('english'::regconfig, id)"))
}

Now, the next migrate will not try to drop the default constraint.

Although an empty @default() should at least give a validation error, which would be less confusing.

@pimeys This is what I have been doing, the problem is that when running npx prisma migrate dev --name initial-migration --create-only and look into the generated migration.sql file I see "hereBeDragons" tsvector DEFAULT to_tsvector('english'::regconfig, id), and trying to apply this will result in an error that looks something like this:

Error:
db error: ERROR: cannot use column reference in DEFAULT expression    
   0: sql_migration_connector::validate_migrations
             at migration-engine\connectors\sql-migration-connector\src\lib.rs:289
   1: migration_core::state::DevDiagnostic
             at migration-engine\core\src\state.rs:251

The workaround from @pimeys will not work here unfortunately, as the underlying SQL does not actually represent a "default" that we could create via dbgenerated(...):

"hereBeDragons" tsvector GENERATED ALWAYS AS (to_tsvector('english', id::text)) STORED,

or

prompt_ts tsvector GENERATED ALWAYS AS (to_tsvector('english'::regconfig, prompt)) STORED,

Using dbgenerated leads to this SQL instead:

"prompt_ts" tsvector DEFAULT to_tsvector('english'::regconfig, prompt),

(via Slack discussion with @zakariamofaddel: https://prisma.slack.com/archives/CA491RJH0/p1670931902950529)

I think you have 2 options how to deal with this for now. Both are not super great. Both are also untested, so let me know how it goes:

1. Option 1 is to remove the @default from the schema. When you create a migration for this table with prisma migrate dev --create-only this will not include the to_tsvector but you can edit that to include the correct SQL. Any future db pull might unfortunately then add the @default again so you need to get rid of that. Future migrations should be clena though, so you can iterate on all the other things in your database.

2. Option 2 is the opposite of that, you keep around whatever db pull put in the schema file, and then you only edit the migration you got via migrate dev --create-only. This will lead to any future migrate dev run also to create a new migration that tries to write the column with DEFAULT - which would fail. So you need to ignore that.

Please let me know if one of these options work - if not we need to dig a bit deeper.

I think this is the feature request to track support for generated columns properly: #6336

Thank you @janpio for the answer, I found this to be the cleanest solution at the moment:

• From the prod database I will remove the GENERATED ALWAYS AS (to_tsvector('english'::regconfig, <column-name>)) STORED from the columnName and leave it as a simple columnName tsvector.
• Always in prod I will then set a trigger, as @jkcorrea suggested, that will take care of setting the columnName to the correct type before every INSERT.
• Now running prisma db pull should generate columnName Unsupported("tsvector")? in my schema.prisma when running prisma db pull and always match the production database.

This should allow me to follow the prisma guide and cleanly introduce prisma migrate to the existing database.

I will update this comment if I'm successful 😄

EDIT:
This was indeed the best solution I could find and it worked flawlessly. We were able to introduce prisma migrate to an existing database with lots of production data.

Thank you @jkcorrea!!

Indeed, removing the generated column and using a trigger instead might be the actually best solution right now. Be aware that the trigger is not reflected in Prisma schema, so you need to put it into a migration file manually once or it will be missing on new deployments.

Thanks a lot for sharing this! This method works perfectly, shall stick to it until we have support for GENERATED.