How to insert multiple rows in one query with some fields default for some rows?

[CJCombrink]

Hi,

I am trying to insert multiple rows at a time and some rows should use the default value for specific columns.

I am using insert_into with calls for values.add for each row to add but now handling the fields that should be default is not as easy.

        auto query = sqlpp::postgresql::insert_into(t_table)
                         .columns(t_table.id,
                                  t_table.name,
                                  t_table.description)
                         .returning(t_table.id);

        for (const auto& row: rows)
        {
            query.values.add(t_table.id = row.id? row.id: sqlpp::default_value, // << This line does not compile 
                             t_table.name       = row.name,
                             t_table.description= row.description);
        }

I am aware of dynamic_insert_into and use it in many places, but not sure if it supports adding multiple rows as above.

I am happy with any one of the following answers:

1. Telling me it is possible in sqlpp11 and giving some guidance on achieving this
2. Telling me it is not possible but it is possible in sqlpp23
3. Telling me it is neither possible in sqlp11 nor sqlpp23 and putting it onto a "feature request" list for sqlpp23

[rbock]

Thanks for the question!

Let's start with this:

        for (const auto& row: rows)
        {
            query.values.add(t_table.id = sqlpp::default_value, // << This is supported, assuming id has a default
                             t_table.name       = row.name,
                             t_table.description= row.description);
        }

What fails is row.id? row.id: sqlpp::default_value, because row.id and sqlpp::default_value are incompatible.

Internally, the library translates both into a specialization of sqlpp::insert_value:

 sqlpp::insert_value_t<std::decay<decltype(table.id)>::type>{sqlpp::default_value}

That would work, but is not exactly beautiful :-)

Feel free to create a feature request for sqlpp23 to make this easier.

[MeanSquaredError]

IMHO the PostgreSQL connector really needs support for the PostgreSQL-specific COPY command. It supports programs/local file on a server/STDIN as the source/destination. The most useful option is the third (STDIN). because in this case STDIN means the database connection, so after issuing the command you write/read the data to/from the connection.

In my tests with the taopq library which supports COPY (IIRC the library has a separate object, "table writer" for that purpose), the speed difference between multiple loops making INSERTs and a COPY command was more than 10x in favour of COPY.

I've always wanted support for COPY in the PostgreSQL connector, but it's been low on my priority list since in my code I don't have to INSERT/SELECT large batches of data too often. BTW looking at the documentation it seems that PostgreSQL has added the WHERE clause to the COPY command (I think that a few years ago it didn't have WHERE or I just didn't notice it), so essentially now it can also be used as a rather limited but fast form of SELECT.

I am pretty sure that it is possible to perform a COPY command via direct access to the underlying native connection which can be accessed by calling

::PGconn* sqlpp::posgresql::connection::native_handle();

So if performance is required, then it is possible to achieve that in sqlpp11 at the cost of writing some low-level code.

But yeah, it would be great to have builtin support for COPY in sqlpp11 (and sqlpp23).