C++ insert into tables of pgsql via libpq-fe.h and compile by g++-13

1.Install libpq-dev

sudo apt install libpq-dev
locate libpq-fe.h
/usr/include/postgresql/libpq-fe.h

 

2.create table t1

create table t1(id bigserial not null primary key,author varchar(40) not null,comment varchar(40) not null,content varchar(40) not null,header varchar(40) not null,isbn varchar(40) not null,object varchar(40) not null,summary varchar(40) not null);    

 

3.main.cpp 

// g++-13 -std=c++23 -I. main.cpp -I/usr/include/postgresql -lpq -o h1;

#include <algorithm>
#include <chrono>
#include <ctime>
#include <fstream>
#include <iomanip>
#include <iostream>
#include <map>
#include <memory>
#include <mutex>
#include <pqxx/pqxx>
#include <random>
#include <set>
#include <thread>
#include <uuid/uuid.h>
#include <vector>
#include "libpq-fe.h"

template <typename T1, typename T2>
void print_mtx_map(const std::map<T1, T2> _mp, const int &interval);

std::string get_time_now(bool is_exact = true)
{
    std::chrono::time_point<std::chrono::high_resolution_clock> now = std::chrono::high_resolution_clock::now();
    time_t raw_time = std::chrono::high_resolution_clock::to_time_t(now);
    struct tm tm_info = *localtime(&raw_time);
    std::stringstream ss;
    ss << std::put_time(&tm_info, "%Y%m%d%H%M%S");
    if (is_exact)
    {
        std::chrono::seconds seconds = std::chrono::duration_cast<std::chrono::seconds>(now.time_since_epoch());
        std::chrono::milliseconds mills = std::chrono::duration_cast<std::chrono::milliseconds>(now.time_since_epoch());
        std::chrono::microseconds micros = std::chrono::duration_cast<std::chrono::microseconds>(now.time_since_epoch());
        std::chrono::nanoseconds nanos = std::chrono::duration_cast<std::chrono::nanoseconds>(now.time_since_epoch());
        ss << std::setw(3) << std::setfill('0') << (mills.count() - seconds.count() * 1000)
           << std::setw(3) << std::setfill('0') << (micros.count() - mills.count() * 1000)
           << std::setw(3) << std::setfill('0') << (nanos.count() - micros.count() * 1000);
    }
    return ss.str();
}

std::string get_time_span(std::chrono::time_point<std::chrono::high_resolution_clock> _start_time, std::chrono::time_point<std::chrono::high_resolution_clock> _end_time)
{
    std::stringstream ss;
    ss << std::chrono::duration_cast<std::chrono::seconds>(_end_time - _start_time).count() << " seconds,"
       << std::chrono::duration_cast<std::chrono::milliseconds>(_end_time - _start_time).count() << " mills,"
       << std::chrono::duration_cast<std::chrono::microseconds>(_end_time - _start_time).count() << " micros,"
       << std::chrono::duration_cast<std::chrono::nanoseconds>(_end_time - _start_time).count() << " nanos"
       << std::endl;
    return ss.str();
}

char uuid_value[37];
uint32_t rand32()
{
    return ((rand() & 0x3) << 30) | ((rand() & 0x7fff) << 15) | (rand() & 0x7fff);
}

char *gen_uuid4()
{
    int n = snprintf(uuid_value, sizeof(uuid_value), "%08x-%04x-%04x-%04x-%04x%08x",
                     rand32(),                       // Generates a 32-bit Hex number
                     rand32() & 0xffff,              // Generates a 16-bit Hex number
                     ((rand32() & 0x0fff) | 0x4000), // Generates a 16-bit Hex number of the form 4xxx (4 indicates the UUID version)
                     (rand32() & 0x3fff) + 0x8000,   // Generates a 16-bit Hex number in the range [0x8000, 0xbfff]
                     rand32() & 0xffff, rand32());   // Generates a 48-bit Hex number
    // return n >= 0 && n < len;             // Success only when snprintf result is a positive number and the provided buffer was large enough.
    return uuid_value;
}

void insert_into_psql_batch(const int &loops)
{
    // create table t1(id bigserial not null primary key,author varchar(40) not null,comment varchar(40) not null,content varchar(40) not null,header varchar(40) not null,isbn varchar(40) not null,object varchar(40) not null,summary varchar(40) not null);
    std::uint64_t num = 0;
    srand(time(NULL));
    std::string conn_str = "host=localhost port=5432 user=fred password=Fred0001! dbname=db";
    const char *conn_char_ptr = conn_str.c_str();
    PGconn *conn = PQconnectdb(conn_char_ptr);
    PGresult *res;

    // std::cout << PQstatus(conn) << std::endl;
    if (PQstatus(conn) == CONNECTION_OK)
    {
        std::cout << "ConnStatusType:CONNECTION_OK" << std::endl;
    }
    else if (PQstatus(conn) == CONNECTION_BAD)
    {
        std::cout << "ConnStatusType:CONNECTION_BAD" << std::endl;
    }

    std::stringstream ss;
    std::chrono::time_point<std::chrono::high_resolution_clock> _start_time, _end_time;
    for (int loop = 0; loop < loops; loop++)
    {
        _start_time = std::chrono::high_resolution_clock::now();
        ss = std::stringstream();
        ss << "insert into t1(author,comment,content,header,isbn,object,summary) values ";
        for (int i = 0; i < 1000000; i++)
        {
            ++num;
            ss << "('" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','"
               << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "'),";
        }

        std::string insert_sql = ss.str();
        int last_comma_idx = insert_sql.find_last_of(",");
        insert_sql = insert_sql.substr(0, last_comma_idx);
        res = PQexec(conn, insert_sql.c_str());
        _end_time = std::chrono::high_resolution_clock::now();
        if (PGRES_COMMAND_OK == PQresultStatus(res))
        {
            std::cout << "PQresultStatus(res):PGRES_COMMAND_OK" << std::endl;
            PQclear(res);
            std::cout << "Loops:" << loop + 1 << ",num:" << num << ",time cost:" << get_time_span(_start_time, _end_time) << std::endl;
        }
    }
    PQfinish(conn);
    std::cout << get_time_now() << ",finished line:" << __LINE__ << " of " << __FUNCTION__ << std::endl;
}

int main(int args, char **argv)
{
    // -I/usr/include/postgresq
    // g++-13 -std=c++23 -I. main.cpp -I/usr/include/postgresql -lpq -o h1;
    insert_into_psql_batch(atoi(argv[1]));
    std::cout << get_time_now() << ",finished in " << __LINE__ << " of " << __FUNCTION__ << std::endl;
}

 

4.compile via g++-13

g++-13 -std=c++23 -I. main.cpp -I/usr/include/postgresql -lpq -o h1;

 

5.run ./h1

nohup ./h1 10000 >>insert_pgsql.txt |tail -f insert_pgsql.txt

 

 

6.validate insert result

select * from t1 order by id desc limit 1;

id    |                author                |               comment                |               content                |                header                |                 isbn                 |                object                |               summary                
----------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 38000000 | fa1d4171-f4b3-4224-b96a-67c998b654b4 | 8dc0dcae-66cf-4563-837a-d654c45e36a2 | 35e1f2df-0fcc-470f-92d5-88dce1de628b | f3d0408a-e733-4377-87f6-b19247412150 | 315dc0e1-66b2-4830-ac6d-391900a1004c | 12a93754-a7bc-4679-bed9-8eb69c2b5ca6 | 629a4ed0-5a0f-41b9-befd-7c7961db96b2
(1 row)

(END)

 

 

select * from t1 where id in (10000000,20000000,30000000,40000000);


    id    |                author                |               comment                |               content                |                header                |                 isbn                 |                object                |               summary                
----------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
 10000000 | 56f91915-d9f0-4ab4-8a34-9c30f5d415cb | b55f22d9-5c5e-40b6-b97d-f4d40ec7367b | 2536cd06-08bb-4b2d-bd3a-0c6f9177e10f | c8d3f5e3-37de-44ef-aa31-38f035bd31d7 | 958cf24f-d19a-4620-9bb3-232581e50acf | 2ed27ac9-2aeb-4720-9579-bb40f2e28e42 | a6dbef4d-f27d-4cb8-a19e-007b96b72fd0
 20000000 | 6909f2ef-bed7-4112-82f2-13f2f76afe5a | a4f13397-bee9-4014-afef-685b66fe44e4 | 11de440b-86c9-48cd-a6ba-982e2d486a77 | 6fba810d-6926-4e96-84b4-13cfe9159e19 | 3bc85784-c694-4468-90ac-5fcb2ff72de7 | ca0254e6-35a8-45ba-b645-2636cb89229c | 62ea1b49-a5cb-4eb9-9729-ef07e419a665
 30000000 | ee91c2c7-eceb-4d79-83e0-0b6a23a61a56 | a5101ccf-ea3b-42ef-9042-f0ae2866cddd | 94667bd1-e292-41c5-b4a2-c7e5595b2e75 | a0a1a6a9-0cf8-4a41-9021-af24240ed06b | e40d8576-e6cb-4eae-b5e1-f6c8fb4a83bb | d36d61e1-490e-4279-9135-47cbef6ef259 | 81036b8e-8bb4-4cd7-a4bc-9072c0b4dfb9
 40000000 | 359dd0c2-9ea5-4b52-84a0-cc5c26d6fc49 | d9654733-fd9a-4fec-a520-f5bd6d881b58 | 886de209-e0d6-45c6-9a8a-3779f4d56960 | 5db19b83-1753-4da9-bade-89f7c6d93050 | c7bbf309-6041-428f-81b4-bf52e4cad4de | de26c999-d32c-4996-8e33-c5afd999eb6a | a14afd75-a8ec-45b8-a641-4a9973003da0
(4 rows)

(END)