Postgre SQL ERROR:there is no unique or exclusion constraint matching the ON CONFLICT specification

chimchim

发布于 2023-10-17 08:54:12

5520

发布于 2023-10-17 08:54:12

一、问题

使用datax从hive推送数据到pg时报错

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification Call getNextException to see other errors in the batch.

二、定位问题

从报错内容很明显能看出来是主键约束出了问题，检查一下我们的主键设置

三、删除主键约束

ALTER TABLE public.table_name  DROP CONSTRAINT table_name_pkey;

四、增加主键约束

ALTER TABLE public.table_name  add primary key (id,col1);

五、检查datax自定义json配置

{
	"job": {
		"setting": {
			"speed": {
				"channel": 1
			},
        "errorLimit": {
            "record": 0,
            "percentage": 0.02
            }
		},
		"content": [{
			"reader": {
                    "name": "hdfsreader",
                    "parameter": {
                        "path": "/user/hive/warehouse/db_name.db/table_name",
                        "defaultFS": "hdfs://ip:host",
                        "column": [
                               {
                                "index": 0,
                                "type": "string"
                               },
                               {
                                "index": 1,
                                "type": "string"
                               },
                               {
                                "index": 2,
                                "type": "string"
                               }
                        ],
                        "fileType": "orc",
                        "encoding": "UTF-8",
                        "fieldDelimiter": "\t"
                    }

                },
			"writer": {
                    "name": "postgresqlwriter",
                    "parameter": {
                        "print": true,
                        "encoding": "UTF-8",
                        "username": "u",
                        "password": "p",
                        "column": [
                        "id",
						"col1",
                        "col2"
                    ],
                    "connection": [
                        {
                            "jdbcUrl": "jdbc:postgresql://ip:host/db_name",
                            "table": ["public.table_name"]
                        }
                    ],
                    "writeMode": "update (id,col1)"
					}
                }
            }
        ]
    }
}

ps:我本次的报错原因是datax的主键约束跟pg表结构主键约束不一致导致的555