PostgreSQL插入好友表触发唯一约束重复键错误求助

我是SQL与PostgreSQL新手，若问题较为基础还请见谅。

我创建了两张表：

CREATE TABLE    usertable (  
user_id VARCHAR PRIMARY KEY,
...);

CREATE TABLE hasFriend(
userID          VARCHAR,
friendID            VARCHAR ,
PRIMARY KEY (userID,friendID),
FOREIGN KEY (userID) REFERENCES usertable(user_id),
FOREIGN KEY (friendID) REFERENCES usertable(user_id));

我正通过Python脚本解析大型JSON文件，向usertable和hasFriend表填充数据。用户表的解析与插入功能正常完成并关闭了JSON文件，之后调用好友表插入函数，重新从JSON文件开头读取数据，函数代码如下：

def insert2FriendTable():
    with open('yelp_user.JSON','r') as f:    
        line = f.readline()
        count_line = 0

        try:
             #edited out the connect statement since it has personal info on it.
            conn = psycopg2.connect(...)
        except:
            print('Unable to connect to the database!')
        cur = conn.cursor()
        while line:
            data = json.loads(line)
            for friend in data['friends']:
                sql_str="INSERT INTO hasFriend (userID, friendID) VALUES ('" + data['user_id']  + "','" + friend + "');"
                try:
                    cur.execute(sql_str)
                except Exception as error:
                    print("Insert to hasFriend failed!", error)
                    return

运行时触发错误：

Insert to hasFriend failed! duplicate key value violates unique constraint "hasfriend_pkey"
DETAIL:  Key (userid, friendid)=(om5ZiponkpRqUNa3pVPiRg, U_sn0B-HWdTSlHNXIl_4XA) already exists.

JSON文件首行示例：

{"average_stars": 3.94, "compliment_cool": 1556, "compliment_cute": 211, "compliment_funny": 1556, "compliment_hot": 1285, "compliment_list": 101, "compliment_more": 134, "compliment_note": 1295, "compliment_photos": 162, "compliment_plain": 2134, "compliment_profile": 74, "compliment_writer": 402, "cool": 40110, "elite": [2014, 2017, 2011, 2012, 2015, 2009, 2013, 2007, 2016, 2006, 2010, 2008], "fans": 835, "friends": ["U_sn0B-HWdTSlHNXIl_4XA", "pnfVIB7UhvCQ7X2K0Q2XIw", "jVYzrVblDFSuL3GHtt8ZSA", "Z7bpqY89ZiBHXdo7UN1kiw", "8Aqr35f254lOeitNowt7ig", "zjcN27kCVeK8K2ONe9Qt4g", "8drMKNHWavs2g6uf0pLtvg", "_K2ViyfmVq6nzIitR0TIlg", "rUV1FUhji5xMjNBpcq5SXg", "yrGIgk5eaWy-eewLNv4KHQ", "3Vd_ATdvvuVVgn_YCpz8fw", "ebC_pH92K4uxyDenoXb5bg", "RJrGgtBXkpX2oEHM4hSqXg", "sdpIz4-s15T239CZ4Bd6Ag", "A0j21z2Q1HGic7jW6e9h7A", "AvC5XQAElcGAAn_Wr5auEg", "JlkHKBnHKdK8Tpls0AF5Aw", "8AG5MctcxTjP4svmUrt0yQ", "bKxdvn7KpmWjMzlmBvp-Xw", "VVMS74JyUk2h53yfC-xNsA", "-ro7OG3jjCSKnF6OJinKjg", "K7thO1n-vZ9PFYiC7nTR2w", "pRBzWnFzaCEtqhYyJ2ZTDQ", "nwESZ8e-KzXt2fKkOuRdIQ", "WNZfkL4DBspueoGSUOMAqA", "uU6fQWadr7Hx_MP0Vmy3kQ", "XiLxIJThWsE0x4d0IeSPsg", "Y9LBTbwO4g0BmdBIi0D3CA", "jGbj8fl575EIQJcfaA1FKQ", "nxWrhF_hyX0wwjrEkQX8uQ", "670k6Gr6V4VqLIKtVEmDuQ", "o5STsEtfvD1Ig0J7Z-1uxA", "x13yoEggBL0pIE7-KMnhDQ", "rCx7tb3toOJUsvdOeqYY0g", "nkN_do3fJ9xekchVC-v68A", "KzHRsFwryS7b5Fog8kkkGA", "_pBzBgtCTN9PNUPfgPDI8A", "PU5QaMADa6N_9ZoQ04ZjOw", "FkfpHzqoDRChwOYhA6NPnQ", "CaQy-zz10ajG7KkNSbXi5w", "brQ7OjB6f9nXWGk45A9A3g"], "funny": 10882, "name": "Andrea", "review_count": 2559, "useful": 83681, "user_id": "om5ZiponkpRqUNa3pVPiRg", "yelping_since": "2006-01-18"}

我完全不清楚该如何解决此问题，恳请各位提供建议，谢谢！

1. 跳过重复记录：使用INSERT ... ON CONFLICT DO NOTHING #

PostgreSQL原生支持冲突处理语法，当遇到唯一键重复时直接跳过该条插入，不会中断整个流程。同时改用参数化查询，避免SQL注入风险，还能兼容含特殊字符的ID：

sql_str = "INSERT INTO hasFriend (userID, friendID) VALUES (%s, %s) ON CONFLICT DO NOTHING;"
cur.execute(sql_str, (data['user_id'], friend))

2. 清理已有重复数据（可选） #

如果之前已经执行过插入操作，表中已有部分数据，可先清空表再重新插入（注意：此操作会删除所有现有数据）：

TRUNCATE TABLE hasFriend;

3. 修复脚本中的其他问题 #

• 缺少事务提交：执行插入后需要调用conn.commit()才能将数据持久化到数据库
• 异常处理直接终止流程：把return改为continue，跳过错误继续处理后续数据
• 无限循环问题：循环末尾需添加line = f.readline()读取下一行数据

修改后的完整函数示例：

def insert2FriendTable():
    with open('yelp_user.JSON','r') as f:    
        line = f.readline()

        try:
            conn = psycopg2.connect(...)
        except Exception as e:
            print('Unable to connect to the database!', e)
            return
        cur = conn.cursor()
        
        while line:
            try:
                data = json.loads(line)
                for friend in data['friends']:
                    sql_str = "INSERT INTO hasFriend (userID, friendID) VALUES (%s, %s) ON CONFLICT DO NOTHING;"
                    cur.execute(sql_str, (data['user_id'], friend))
                conn.commit()
            except Exception as error:
                print("Insert to hasFriend failed!", error)
                conn.rollback()
            line = f.readline()
        
        cur.close()
        conn.close()

内容的提问来源于stack exchange，提问作者Ely Meyers