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 --
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=db user=fred password=Fred0001!"); 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;
