杂记 2024-02-28 周三 将两个表合成一个表，列数为原两表之和，以增加表的列数

-- 将person_info和field_data合成一个表
drop table joe.combinedtable01;
CREATE TABLE joe.combinedtable01 (
id varchar(50),
identifycard varchar(50),
cellphone varchar(50),
c1 varchar(20),
b2 bigint, 
address varchar(50),
PRIMARY KEY (id)
);

insert into joe.combinedtable01(id,identifycard,cellphone,c1,b2,address)
select t1.id, t1.identifycard,t1.cellphone,t2.c1,t2.b2,t2.address
from joe.person_info t1
left join joe.field_data t2 on t1.id=t2.id

select * from joe.combinedtable01, --可以看到变成了7列，一行数据。

-- 原始表
drop table if exists person_info;
create table person_info(
 id serial not null,
 identifycard varchar(50),
 cellphone varchar(50),
 PRIMARY KEY (id)
);
insert into person_info(identifycard,cellphone) values(420684199906280328,13858765437);

drop table if exists joe.field_data;
CREATE TABLE joe.field_data (id bigint,c1 varchar(20),b2 bigint, address varchar(50));
insert into joe.field_data values(1,'user1@126.com',123,'1234 Main St');
insert into joe.field_data values(2,'user2@126.com',456,'555 Bailey Ave');
insert into joe.field_data values(3,'user2@126.com',456,'546 Colorado Ave');