I ve used the same syntax I ve found from 5 different places Supabase #help

I've used the same syntax I've found from 5 differ...

JonWasTaken

04/19/2022, 3:55 AM

I've used the same syntax I've found from 5 different places, so I'm not sure why this would be happening, but running the following SQL results in

db error: ERROR: malformed array literal: ""

Here's the SQL, anyone know why it would be causing this error?

Copy code

ALTER TABLE props ADD COLUMN textSearch tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(name, "")), "A") ||
  setweight(to_tsvector('english', coalesce(terms,"")), "B")
) STORED;

CREATE INDEX props_textsearch_idx ON props USING GIN (textSearch);

Needle

04/19/2022, 3:55 AM

Hello @JonWasTaken! This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``. Pinging @User so that they see this as well! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates. Want to change the title? Use the

/title

command! We have solved your problem? Click the button below to archive it.

garyaustin

04/19/2022, 4:01 AM

try single quote ' instead of double "

Needle

04/19/2022, 4:01 AM

JonWasTaken (2022-04-19)

JonWasTaken

04/19/2022, 4:05 AM

Apologies, I pasted a version I was using for debugging - the actual query uses single quotes:

Copy code

ALTER TABLE props ADD COLUMN textSearch tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(terms,'')), 'B')
) STORED;

JonWasTaken

04/19/2022, 4:05 AM

Full response from that:

Copy code

Error: P3006

Migration `20220419004549_add_full_text_search_to_prop` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: malformed array literal: ""
DETAIL: Array value must start with "{" or dimension information.
   0: sql_migration_connector::validate_migrations
             at migration-engine\connectors\sql-migration-connector\src\lib.rs:271
   1: migration_core::state::DevDiagnostic
             at migration-engine\core\src\state.rs:248

garyaustin

04/19/2022, 4:05 AM

are you using prisma?

JonWasTaken

04/19/2022, 4:07 AM

Yes, this is what happens when I run

npx prisma migrate dev

with that sql in the migration.sql file, but I tried using the supabase console directly and received the same error

garyaustin

04/19/2022, 4:11 AM

Yeah, I'm not even going to try on Prisma stuff. You should in your question either here or in SQL highlight you are using Prisma. Hopefully someone using it has a better idea. I saw shadow database, I guessed... Prisma in general seems to work with Supabase, but issues come up with table/column names having upper case, double " being required, etc. Supabase itself is pure Postgres and any sql code you find should work. Sorry for backing out.

JonWasTaken

04/19/2022, 4:14 AM

Wasn't aware that prisma complicate the sql error, that's interesting, thanks for the heads up

Previous Next