PgSQL

alter table table_name alter column column_name new_type

CREATE TABLE public.t2 (
    id serial primary key,
    name character varying(40) NOT NULL,
    author character varying(40) NOT NULL,
    comment character varying(40) NOT NULL,
    content character varying(40) NOT NULL,
    isbn character varying(40) NOT NULL,
    object character varying(40) NOT NULL,
    summary character varying(40) NOT NULL,
    topic character varying(40) NOT NULL
);

pgsql auto increment as serial

pg_dump show create table statment

pg_dump -U user_name -h host_name -st table_name db_name
pg_dump -U fred -h localhost -st t1 db


pg_dump -U fred -h localhost -st t1 db;
Password: 
--
-- PostgreSQL database dump
--

-- Dumped from database version 15.4 (Ubuntu 15.4-0ubuntu0.23.04.1)
-- Dumped by pg_dump version 15.4 (Ubuntu 15.4-0ubuntu0.23.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    id bigint NOT NULL,
    name character varying(40) NOT NULL
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- Name: t1_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.t1_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.t1_id_seq OWNER TO postgres;

--
-- Name: t1_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.t1_id_seq OWNED BY public.t1.id;


--
-- Name: t1 id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.t1 ALTER COLUMN id SET DEFAULT nextval('public.t1_id_seq'::regclass);


--
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.t1
    ADD CONSTRAINT t1_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

 

//alter serial as bigserial drop serial column at first then add the new column with bigserial 

ALTER TABLE t2 DROP COLUMN id; ALTER TABLE t2 ADD COLUMN id bigserial NOT NULL PRIMARY KEY;

 

 

show create table

//pg_dump  -st table_name db_name;
pg_dump -st t1 db
--
-- PostgreSQL database dump
--

-- Dumped from database version 15.4 (Ubuntu 15.4-0ubuntu0.23.04.1)
-- Dumped by pg_dump version 15.4 (Ubuntu 15.4-0ubuntu0.23.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    id bigint NOT NULL,
    name character varying(40) NOT NULL
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- Name: t1_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.t1_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.t1_id_seq OWNER TO postgres;

--
-- Name: t1_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.t1_id_seq OWNED BY public.t1.id;


--
-- Name: t1 id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.t1 ALTER COLUMN id SET DEFAULT nextval('public.t1_id_seq'::regclass);


--
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.t1
    ADD CONSTRAINT t1_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

 

 

//create table t2

 CREATE TABLE public.t2 (
    id serial primary key,
    name character varying(40) NOT NULL,
    author character varying(40) NOT NULL,
    comment character varying(40) NOT NULL,
    content character varying(40) NOT NULL,
    isbn character varying(40) NOT NULL,
    object character varying(40) NOT NULL,
    summary character varying(40) NOT NULL,
    topic character varying(40) NOT NULL
);

//c++ insert data into pgsql

#include <iostream>
#include <algorithm>
#include <chrono>
#include <ctime>
#include <fstream>
#include <hashtable.h>
#include <iomanip>
#include <memory>
#include <mutex>
#include <queue>
#include <random>
#include <sstream>
#include <set>
#include <thread>
#include <time.h>
#include <uuid/uuid.h>
#include <vector>
#include <pqxx/pqxx>

std::string get_time_now(bool is_exact = true)
{
    auto 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)
    {
        auto seconds = std::chrono::duration_cast<std::chrono::seconds>(now.time_since_epoch());
        auto mills = std::chrono::duration_cast<std::chrono::milliseconds>(now.time_since_epoch());
        auto micros = std::chrono::duration_cast<std::chrono::microseconds>(now.time_since_epoch());
        auto nanos = std::chrono::duration_cast<std::chrono::nanoseconds>(now.time_since_epoch());
        ss << "_";
        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();
}

char *uuid_value = (char *)malloc(40);
char *get_uuid_value()
{
    uuid_t new_uuid;
    uuid_generate(new_uuid);
    uuid_unparse(new_uuid, uuid_value);
    return uuid_value;
} 

void insert_into_pg_table()
{
    try
    {
        std::uint64_t num = 0;
        int loops = 0;
        std::stringstream ss;
        std::string insert_sql;
        for (int interval = 0; interval < 10000; interval++)
        {
            pqxx::connection conn("dbname=dbname_value user=user_name password=password_value");
            pqxx::work trans(conn); 
            ss = std::stringstream();
            ss << "insert into t2(name,author,comment,content,isbn,object,summary,topic) values ";
            for (int i = 0; i < 1000000; i++)
            {
                ss << "('" << get_uuid_value() << "','" << get_uuid_value() << "','" << get_uuid_value() << "','"
                   << get_uuid_value() << "','" << get_uuid_value() << "','" << get_uuid_value() << "','"
                   << get_uuid_value() << "','" << get_uuid_value() << "'),";
                ++num;
            }
            insert_sql = ss.str();
            int last_comma_idx = insert_sql.find_last_of(',');
            insert_sql = insert_sql.substr(0, last_comma_idx);
            pqxx::result res = trans.exec(insert_sql);              
            trans.commit();
            std::cout << "Num:" << num << ",loops:" << ++loops << std::endl;
        }
    }
    catch (const std::exception &e)
    {
        std::cerr << e.what() << '\n';
    }
}

int main(int args, char **argv)
{
    // thread_detach_for_seconds(atoi(argv[1]));
    insert_into_pg_table();
    std::cout << get_time_now() << ", finished in " << __LINE__ << " of " << __FUNCTION__ << std::endl;
}

 

 

g++-13 -g -std=c++23 main.cpp -luuid -lpthread -lpqxx -o h2;

./h2;