sql increment value if exists otherwise insert with value 1

Question

Given following schema:

document(id, path);
term(id, description);
term_document(id_term,id_document,frequency);

Given a document path and term description, if I don't have a record of the term and the document in the table term_documento I want to insert into the table term_document with frequency = 1, otherwise, I want to just increment the frequency value.

What I came up so far is this:

insert into term_document(id_term, id_document, frequency)
select term.id, document.id, 1
from term, document where term.description='to' and document.path='/home/rogger/Projetos/projeto-ori-ufu/src/main/resources/d1.txt'

which satisties the case I dont have a record in term_document, but I don't know how to increment to satisfy both.

This is the reoccuring topic of sequences per id. If you have concurrent write access, the best answer is: don't. See http://stackoverflow.com/a/24918964/939860 — Erwin Brandstetter, May 07 '17 at 18:57
good point @ErwinBrandstetter, however is a very simple application and I wont have concurrent access to my database. — Rogger Fernandes, May 07 '17 at 19:16

score 1 · Accepted Answer · answered May 07 '17 at 18:53

Assuming you have a unique constraint on term_document(id_term, id_document), you can use on conflict clause in your insert:

insert into term_document(id_term, id_document, frequency)
select t.id, d.id, 1
from term t
cross join document d
where t.description = 'to'
    and d.path = '/home/rogger/Projetos/projeto-ori-ufu/src/main/resources/d1.txt'
on conflict (id_term, id_document) do update set frequency = frequency + 1;

I used cross join as you did (only in modern syntax). If there is actually a relation between the two table, you'd need to join them on those columns instead.

Note that INSERT...ON CONFLICT is only available in Postgres 9.5+. — Dmitri Goldring, May 07 '17 at 21:32

sql increment value if exists otherwise insert with value 1

1 Answers1