创建可存储超过10000列CSV表数据的PostgreSQL数据库

CREATE TABLE data_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    address TEXT,
    phone VARCHAR(20),
    other_data JSONB
);

import csv
import json
import psycopg2

# 数据库连接信息
DB_HOST = "localhost"
DB_NAME = "your_database"
DB_USER = "your_user"
DB_PASSWORD = "your_password"

# CSV文件路径
CSV_FILE = "your_data.csv"

# 连接到PostgreSQL数据库
conn = psycopg2.connect(host=DB_HOST, database=DB_NAME, user=DB_USER, password=DB_PASSWORD)
cur = conn.cursor()

# 读取CSV文件
with open(CSV_FILE, 'r', encoding='utf-8') as file:
    reader = csv.DictReader(file)
    for row in reader:
        # 提取常用列
        id = row['id'] # 如果 CSV 中没有 id 列，可以考虑自动生成
        name = row['name']
        email = row['email']
        address = row['address']
        phone = row['phone']

        # 提取不常用列并转换为JSON
        other_data = {k: v for k, v in row.items() if k not in ['id', 'name', 'email', 'address', 'phone']}
        other_data_json = json.dumps(other_data)

        # 插入数据到数据库
        sql = "INSERT INTO data_table (id, name, email, address, phone, other_data) VALUES (%s, %s, %s, %s, %s, %s)"
        cur.execute(sql, (id, name, email, address, phone, other_data_json))

# 提交事务并关闭连接
conn.commit()
cur.close()
conn.close()

print("Data imported successfully!")

SELECT name, other_data ->> 'attribute1' AS attribute1
FROM data_table
WHERE id = 1;

CREATE INDEX data_table_other_data_idx ON data_table USING GIN (other_data jsonb_path_ops);