Open AA-morganh opened 2 months ago
aah, good catch! a couple of things that come to mind you could try:
Adding public
to the search path so the constraint is found: SET search_path= public_my_migration,public;
Fully namespacing the constraint from the query:
INSERT INTO inventory(user_id, entitlement_id, quantity)
VALUES ('Some User Id', 'Some Entitlement Id', 100)
ON CONFLICT ON CONSTRAINT public.unique_entitlement_ids_per_user
DO UPDATE SET
quantity = inventory.quantity + EXCLUDED.quantity
RETURNING *
Let us know if any of these work! I'm wondering if we could have ways of making the constraint visible without these changes
Hi @exekias
Unfortunately those aren't working for me. I'm setting the search path as a parameter to my postgres client as it connects (-c search_path=SEARCH PATH) and when I add the public schema to that path, it's not finding any of my relations anymore.
I tried fully namespacing the constraint as you have here and that's also failing, complaining that the "." is invalid syntax.
One advantage I do have is that I'm running this specific query in a larger transaction, so I can do
SET search_path=public;
MY QUERY
SET search_path=SCHEMA;
I did try using SET search_path=SCHEMA,public
in the transaction to see what would happen, and it also failed to find the constraint. I think it might be because it's finding the inventory table in the view, so it doesn't look at the public schema for the constraint even if it's on the path. Could be wrong about that though, I'm far from a postgres expert.
I have a raw sql migration that creates a unique constraint across a few columns. I want to use this constraint to enforce an upsert workflow. However, in the schema from that migration (public_MIGRATION_NAME) the constraint isn't visible. If I switch to the public schema, it is visible and working, so I can see that it's getting created correctly. Is there something I need to do to get my constraint into my migration schema's view, or should I run another migration, or do something else?
Example Migration:
This fails with "constraint unique_entitlement_ids_per_user does not exist." However, if I use the public schema instead, the query works.