PostgreSQL: サブセットのテーブルをペアレントのテーブルにコピー

テーブル定義

サブセットのテーブル

city=# \d cities
                        Table "public.cities"
   Column   |         Type          | Collation | Nullable | Default 
------------+-----------------------+-----------+----------+---------
 id         | character varying(10) |           | not null | 
 name       | text                  |           |          | 
 population | integer               |           |          | 
 date_mod   | date                  |           |          | 
Indexes:
    "cities_pkey" PRIMARY KEY, btree (id)

ペアレントのテーブル

city=# \d cities_ex
                      Table "public.cities_ex"
   Column   |         Type          | Collation | Nullable | Default 
------------+-----------------------+-----------+----------+---------
 id         | character varying(10) |           | not null | 
 name       | text                  |           | not null | 
 population | integer               |           |          | 
 area       | double precision      |           |          | 
 date_mod   | date                  |           |          | 
Indexes:
    "cities_ex_pkey" PRIMARY KEY, btree (id)

プログラム

table_copy.py

#! /usr/bin/python
#
#	python/table_copy.py
#
#				Jun/03/2024
#
# --------------------------------------------------------
import os
import sys
import json
import psycopg2
from dotenv import load_dotenv
#
# --------------------------------------------------------
def table_copy_proc(cursor_aa,array_aa,table_src,table_target):
	sql_str = "insert into " + table_target
	portion_bb = ""
	for it in range(len(array_aa)):
		portion_bb += array_aa[it]
		if it != (len(array_aa) -1):
			portion_bb += ","
	portion_aa = "(" + portion_bb + ")"
#
	sql_str += portion_aa
	sql_str += " select " + portion_bb + " from "
	sql_str += table_src
	print(portion_aa) 
	print(sql_str)
#
	try:
		cursor_aa.execute(sql_str)
	except Exception as ee:
		sys.stderr.write("*** error *** in cursor_aa.execute ***\n")
		sys.stderr.write(str(ee) + "\n")
#
# --------------------------------------------------------
def get_columns_proc(cursor_aa,table_src):
	sql_str = "select column_name from information_schema.columns where table_name = '" + table_src + "'"
	cursor_aa.execute(sql_str)
	rows = cursor_aa.fetchall()
	array_aa = []
	for row in rows:
		array_aa.append(row[0])
#
	return array_aa
# --------------------------------------------------------
sys.stderr.write ("*** 開始 ***\n")
table_src = sys.argv[1]
table_target = sys.argv[2]
sys.stderr.write ("%s\n" % table_src)
sys.stderr.write ("%s\n" % table_target)
#
dotenv_path = '.env'
load_dotenv(dotenv_path)
USER=os.environ.get("user")
PASSWORD=os.environ.get("password")
DATA_BASE=os.environ.get("data_base")
#
dsn="dbname=" + DATA_BASE + " user=" + USER + " password=" + PASSWORD
conn = psycopg2.connect(dsn)
cursor_aa = conn.cursor()
#
array_aa = get_columns_proc(cursor_aa,table_src)
print(array_aa)

table_copy_proc(cursor_aa,array_aa,table_src,table_target)
#
conn.commit ()
cursor_aa.close ()
conn.close ()
#
sys.stderr.write ("*** 終了 ***\n")
#
# --------------------------------------------------------

.env

user='****'
password='*****'
data_base='city'

実行コマンド

./table_copy.py cities cities_ex

コラムの　Not null を外す方法

ALTER TABLE cities_ex ALTER COLUMN name DROP NOT NULL;