0

I have this table

CREATE TABLE IF NOT EXISTS projects_t
(
  name     TEXT NOT NULL,
  metadata JSONB,
  content  BYTEA,
  version  TIMESTAMP WITH TIME ZONE DEFAULT now(),
  PRIMARY KEY (name, version)
);

The app accessing it doesn't know about version, only name metatdata and content

So I created a view to make it transparent + trigger for insert/update/delete

CREATE OR REPLACE VIEW projects AS
  SELECT DISTINCT ON (name)
    name,
    metadata,
    "content"
  FROM projects_t
  ORDER BY name, version DESC;


CREATE OR REPLACE FUNCTION f_insert_projects()
  RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
  INSERT INTO projects_t (name, metadata, content) VALUES (NEW.name, NEW.metadata, NEW.content);
END;
$$;

CREATE OR REPLACE FUNCTION f_update_projects()
  RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
  INSERT INTO projects_t (name, metadata, content) VALUES (NEW.name, NEW.metadata, NEW.content);
END;
$$;

CREATE OR REPLACE FUNCTION f_delete_projects()
  RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
  DELETE FROM projects_t
  WHERE name = OLD.name;
END;
$$;

CREATE TRIGGER insert_project_via_view_trig
INSTEAD OF INSERT ON projects
FOR EACH ROW EXECUTE PROCEDURE f_insert_projects();

CREATE TRIGGER update_project_via_view_trig
INSTEAD OF UPDATE ON projects
FOR EACH ROW EXECUTE PROCEDURE f_update_projects();

CREATE TRIGGER delete_project_via_view_trig
INSTEAD OF DELETE ON projects
FOR EACH ROW EXECUTE PROCEDURE f_delete_projects();

I cannot modify the SQL query ran by the app (it wouldn't be funny !). The app does an upsert :

INSERT INTO projects VALUES ('test', ('{ "last_modified_time": "' || (now() at time zone 'utc')::text || '", "last_modified_user": "' || current_user || '" }')::jsonb) ON CONFLICT (name) DO UPDATE SET content = EXCLUDED.content, metadata = EXCLUDED.metadata;

And it throw me an error 42P10, "There is no unique contraint nor exclusion constraint corresponding to the specification ON CONFLICT". Is there a way to make it work ?

  • Why are you using "DISTINCT ON (name)" when that column is UNIQUE to begin with? – CL. Jun 27 '18 at 14:27
  • I correct this. this is obviously a mistake. I'll have multiple "name". – Rémi Desgrange Jun 28 '18 at 6:00
  • Now you have no constraint, as the error message tells you. – CL. Jun 28 '18 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.