Question; [PostgreSQL] on_conflict with more than one column

[CJCombrink]

I have a table like this in PostgreSQL:

CREATE TABLE mytable(  
    id       INTEGER DEFAULT NULL,
    name     TEXT NOT NULL,
    value    TEXT NOT NULL,

    UNIQUE (id, name)
);

In SQL I can run the following query to insert or update the value:

INSERT INTO mytable(id,name,value)
    VALUES(1,'key2','10')
    ON CONFLICT (id,name)
    DO UPDATE
        SET value='10'

It appears that the on_conlict only supports one columns, is this correct?
If so, any suggestions on how to work around this limitation?

Related: A random related SO answer also mentions this:

You can reference this unique constraint as either ON CONFLICT (col1, col2) or as ON CONFLICT ON CONSTRAINT ux_col1_col2.

The hypothetical code to support this would be something like:

    constexpr gen::mytable table{};

    const auto query = pg::insert_into(table)
                           .set(table.id= id_,
                                table.name      = key,
                                table.value     = value)
                           .on_conflict(table.id, table.name)
                           .do_update(table.value = value);

The runtime error I get when I only add one column is:

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

And I get a compile time error when I supply no columns:

static assertion failed: conflict_target specification is required with do_update(...)

Would it be possible to inject a string into the on_conflict() perhaps?

[rbock]

Thanks for the report. That should be fixed.

@matthijs might know better, but looking at the code it seems that it would be relatively easy to allow more than one column?

I am currently a bit pre-occupied with other work. Do you want to give it a try (at least as a local workaround)? Storing zero or more columns in a tuple and then use interpret_tuple for serialization?

Alternatively, you could also hack a pseudo-column that serializes as the two columns you need (that sounds more ugly than I thought it would before writing it down :-) ).

Note that sqlpp11 does not know about constraints as of today. Choosing the correct columns is on you.

[matthijs]

I'll take a look next week.

[MeanSquaredError]

Wouldn't it make sense to tackle this problem after the support for std::optional makes it into the library? I have been peeking now and then at the std::optional branch and it seems that the changes incurred by std::optional are quite significant and it would take time to forward port the on_conflict changes if they were to be done now.