问题:

I want to do

 " on conflict (time) do update set name , description "

but I have no idea when I use stdin with csv , I don't know what name equal what? and description equal what...

table_a:

xxx.csv:

with open('xxx/xxx.csv', 'r', encoding='utf8') as f:
    sql = """
    COPY table_a FROM STDIN With CSV on conflict (time) 
    do update set name=??, description=??;
    """
    cur.copy_expert(sql, f)
    conn.commit()

回答1:

Thanks for every master's solution.

this is my solution.

sql = """
CREATE TABLE temp_h (
    time ,
    name,
    description
);
COPY temp_h FROM STDIN With CSV;

INSERT INTO table_a(time, name, description)
SELECT *
FROM temp_h ON conflict (time) 
DO update set name=EXCLUDED.name, description=EXCLUDED.description;

DROP TABLE temp_h;
"""

回答2:

In this SO post, there are two answers that -combined together- provide a nice solution for successfully using ON CONFLICT. The example below, uses ON CONFLICT DO NOTHING;:

CREATE TEMP TABLE tmp_table 
(LIKE label INCLUDING DEFAULTS)
ON COMMIT DROP;

COPY tmp_table FROM 'full/file/name/here';

INSERT INTO main_table
SELECT *
FROM tmp_table
ON CONFLICT DO NOTHING;