does anyone know how to add a count column to a table that returns the count of related tables rows

Does anyone know what is the correct way to migrate to production databases?

Database Migration

Count Column

I'm struggling with a plpgsql function to create/remove 'tags'. Hoping someone can point me in the right direction.

I'm trying to upload a CSV (UTF-8) file, and I'm being met with this error. Any ideas? Even tried with a sample CSV i made from scratch, still not working.

What separators are you using?

And do you have headers in the csv (column names)?

, yes, with no special characters

That's odd 😕

yeah, just imported through tableplus, so I'm ok but not sure what the issue is @ supabase

Right

What I used to import csv was drag and dropping the file

I tried that too, but Chrome just thought I was trying to open the file in the browser 🤷‍♂️

might be a browser issue now that I mention that

no console errors

same issue in firefox. oh well, will contact support and let them know. fortunately only need to do this once.

Okay

Good luck man!

Setting up full text search https://supabase.io/docs/guides/database/full-text-search I'd like to create an index (refer Creating Indexes > Searchable columns) but I need to include data from another table Specifically: Full text search will be against my

jobs

table (this is where the index should go), and a

jobs_tags

table which is a mapping table containing all tags for each job. Should I add tags into the

fts

column in the jobs table? I don't think that would get the benefit of the auto updating index?

I moved from Firebase because of search/filter issues, one was ability to search wine titles for partial input. I thought full text search was the answer. It seems for short titles, words it is not that easy (it is more for descriptions, paragraphs). See this https://github.com/supabase/supabase/discussions/3542 for the main issue I also find myself in. Full text does not match until you have close to whole words and mainly what is in the dictionary you pick. The ts_vector columns will not "hit" on partials only full. At the moment I've dropped back to ilike %partial% until I can figure out this Trigram stuff. Just something to think about....

I have a wide range of data length so probably need to use both

It's possible to use the

similarity

extension like this:

sql
SELECT * FROM wines WHERE
  extensions.similarity(wines.description::TEXT, search_value) > 0.25

You can also use something like the distance operator (from the

pg_trgm

extension I believe):

sql
SELECT * FROM wines WHERE
  1 - (wines.description::TEXT <-> search_value) > 0.25

With enough fine tuning of the values (e.g.

0.25

), you can have something that is akin to fuzzy searching, but the specific value you should use will depend on your dataset and your own testing. If you need something more advanced to search across multiple columns in a single function,

to_tsvector

and

to_tsquery

can be combined like this:

sql
SELECT * FROM wines WHERE
  to_tsvector(wines.name || ' ' || wines.description || ' ' || wines.manufacturer || ' ' || wines.country) -- concat columns, but be sure to include a space to separate them!
  @@ to_tsquery(search_value)

In all 3 cases,

search_value

is a

TEXT

parameter passed into the function. You can of course combine any or all of the above options if you really need to using

AND

/

OR

.

Thanks for the detailed response. I'll get back to text search when I finish porting the rest of app from FB/ReactJS. I found Supabase, then in that process found Svelte from users here and am switching from React to that. My 60 year old brain finds it much easier to code and get cleaner results than with React. Luckily(?) my market is just now recovering from Covid so have time....

wow this is so good, thank you for suck amazing response!!!

Text and fuzzy search

Heya - a question about PostgreSQL Functions. 🙂 It is about the

SECURITY DEFINER SET search_path = public

thingy that I have to use when writing Functions when using Supabase, but I really do not see other tutorials or the like use.. Am I allowed to just add

extensions

to it along with public, or is there any security issues with that? I can only get pgjwt (https://github.com/michelp/pgjwt) to work with that added. 🙂 Here is the full function:

CREATE OR REPLACE FUNCTION handle_new_user() RETURNS trigger
    SECURITY DEFINER SET search_path = public, extensions
AS
$$
BEGIN
    INSERT INTO profiles (id, jwt)
    VALUES (NEW.id, (SELECT SIGN('{
      "sub": "1234567890",
      "name": "John Doe"
    }', 'secret')));

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Hey! I have an issue when I try to link my local database to my supabase project. When I run

supabase link --url 'postgresstring'

I get the error

Error: supabase_migrations.schema_migrations table conflicts with the contents of 'migrations' directory.

Any idea what I have to do in this case?

Are you trying to define a variable with SET or have i misunderstood?

Security Definer and search_path