0

Sorry for the somewhat simple question, still learning SQL and haven't been able to get past this one.

Essentially, I have a simple table for users to store their own recipes of varying categories. Each row has the persons id as a key, a recipe category (that will be 1 of 3 strings), and a text array that holds the recipe ids for that category:

CREATE TABLE recipes (
  profile_id TEXT NOT NULL,
  recipe_category TEXT NOT NULL,
  recipe_ids TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[] 
  PRIMARY KEY (profile_id, recipe_category ),
}

My question is: I wrote all my basic queries for the table, but I am stuck on how I'd go about doing a proper add/remove of recipe_ids. What I came up with so far is:

UPDATE recipes
SET recipe_ids=array_cat(recipe_ids, $1)
WHERE profile_id=$2 AND recipe_category=$3

Seemed simple enough to me, but what if it is the first time I am submitting a recipe, and the user doesn't have a row with that category yet, then the WHERE for category would fail. Is there a way to set category if it does not exist, while also fulfilling the WHERE for the category if it does exist? Do I have to break it into two queries. For reference, arg $1 is an array of IDs

Thank you!

floppy2029
  • 11
  • 2
  • 3
    see: [Insert, on duplicate update in PostgreSQL?](https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql) – Luuk Oct 21 '22 at 20:02
  • Never, ever use arrays to store references, if you want your queries to be simple and perform well. – Laurenz Albe Oct 22 '22 at 15:52

0 Answers0