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 stackoverflow.com/a/24918964/939860 — Erwin Brandstetter
– Erwin Brandstetter, Commented May 7, 2017 at 18:57
good point @ErwinBrandstetter, however is a very simple application and I wont have concurrent access to my database. — Rogger Fernandes
– Rogger Fernandes, Commented May 7, 2017 at 19:16

Gurwinder Singh · Accepted Answer · 2017-05-07 18:53:54Z

1

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.

answered May 7, 2017 at 18:53

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitri Goldring Over a year ago

Note that INSERT...ON CONFLICT is only available in Postgres 9.5+.

Collectives™ on Stack Overflow

sql increment value if exists otherwise insert with value 1

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related