How to use PostgreSQL INSERT ON CONFLICT statement

Question

I try to add or update postgresql, after referring to some articles I get the following statement

I read this article.

I have three tables.

users

id auto increase PK
Name

articles

id auto increase PK
author_id FK Reference users

notes

id auto increase PK
author_id FK Reference users
article_id FK Reference articles

First

INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT ON CONSTRAINT author_id 
DO NOTHING;

get ERROR: constraint "author_id" for table "notes" does not exist

Second

INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT ON CONSTRAINT author_id REFERENCES 'users'
DO NOTHING;

get ERROR: syntax error at or near "REFERENCES"

Third

INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT (author_id)
DO NOTHING;

get ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

---

Guessing that the problem is because foreign key.

can sql statement add "returning id"? I need to get note id.

Answer 1

Thanks to Adrian Klaver for the suggestion, rebuilt after research table.

CREATE TABLE notes (
    id bigserial PRIMARY KEY,
    reader_id integer references users(id),
    article_id integer references articles(id),
    UNIQUE(reader_id, article_id)
)

Complete the original requirements, add return the last insert Id

INSERT INTO notes (article_id, reader_id)
    VALUES (1, 1)
    ON CONFLICT (article_id, reader_id) 
    DO Nothing
    RETURNING notes.id;