Edit doesn't work for affiliation_id

[kjk]

In sportsdb database, affiliations_documents, when trying to update affiliation_id column, we fail with Err: pq: syntax error at or near ".".

The generated update query doesn't is invalid:

UPDATE .affiliations_documents SET affiliation_id='44' WHERE RETURNING affiliation_id, document_id;

This table probably cannot be updated at all given that it doesn't seem to have a primary key.

[thellimist]

The last query below finds every table's primary key, foreign key and unique column. Currently to fix the affiliations_documents bug I need to change the query also the frontend. I don't want to edit again so if you know the exact solution it'd be great to know instead of wasting time on discovering it.

This query is generated by postico. As you can see it does check every column. But in tables with primary key the where clause only checks whether the primary keys are the same as the row which will be edited.

tl;dr I can copy postico(may miss some special cases), I can check if every row is the same or do a different solution. Which one would be the fastest solution to implement?

UPDATE "public"."affiliations" SET "affiliation_type"='I CHANGED THIS DATA' 
WHERE ctid IN 
(SELECT ctid FROM "public"."affiliations" 
WHERE "id"='6' AND "affiliation_key"='d.afceast' 
AND "affiliation_type"='division' AND "publisher_id"='1' LIMIT 1 FOR UPDATE) 
RETURNING "id", "affiliation_key", "affiliation_type", "publisher_id";

SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,

rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc

LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name

LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name

LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name

WHERE lower(tc.constraint_type) in ('foreign key') OR lower(tc.constraint_type) in ('primary key') OR lower(tc.constraint_type) in ('unique')

[kjk]

Sorry, don't know what the solution is. One option is to not allow editing when there is no primary key, at least initially. This does look like a tricky problem.

[thellimist]

https://github.com/kjk/dbworkbench/commit/8dc2a5ee3889467525f9564b33995b37c44c7b1e