Copy命令不更新Id_sequence列，当从csv复制时将错误抛到“id”列中的null值违反not-null约束

Question

我有csv文件，必须将数据复制到postgre表中，如果在我的csv中如果我不输入Id和Updated_at的数据将抛出错误，但它不应该因为Id被标记为默认和增量。我正在从python复制这个副本

CREATE TABLE IF NOT EXISTS public.demographic_types (
    id bigint DEFAULT nextval('public.demographic_types_id_seq'::regclass) NOT NULL,
    demographic_type text NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);

Python代码

def load_data(conn):
    """
    Load seeded data
    """
    db = os.environ['DATABASE_URL']
    dbname = db.replace("hsdp_pg","harbinger")
    try:
        with psycopg2.connect(dbname) as conn1:

            #migrate_db(conn, dbname, mirth_pw, harbinger_pw, psql_path, init_db)
            conn1.commit()
    except psycopg2.Error as exp1:
        print(exp1)
        print ('Error Code: %s. Error %s' % (exp1.pgcode, exp1.pgerror))


    print(conn1)
    path = os.path.dirname(os.path.realpath(__file__))    
    print (os.path.join(path,"database/data/*.csv"))
    for fle in sorted(glob.glob(os.path.join(path,"database/data/*.csv"))):
        print ('>>>Migrating data %s' % fle)
        table_name = os.path.basename(fle).replace('.csv', '')

        try:
            #silent_query(conn, sql, None)
            with conn1.cursor() as cur:
                #delete data first
                print('Deleting data from table  %s' % table_name)
                cur.execute('TRUNCATE %s CASCADE' % table_name)
                print('i am done and waiting')
                conn1.commit()


                with open(fle, 'r') as f:
                    #headers = ", ".join(table_column_mapping_data[table_name])
                    print("i am here ")
                    #cur.copy_from(f, table_name, sep=',')
                    #sql = "INSERT INTO %s (ID, demographic_type, updated_at) VALUES (%s,%s,%s)" % table_name
                    #record_insert = ('1', 'phone', '')
                    #cur.execute(sql, record_insert)
                    sql = "COPY %s from STDIN WITH CSV HEADER DELIMITER ','" % table_name
                    #print(sql)
                    cur.copy_expert(sql, f)
                    conn1.commit()
        except psycopg2.Error as exp2:
            print ('Error Code: %s. Error %s' % (exp2.pgcode, exp2.pgerror))

Answer 1

如果我理解正确的话，您希望从CSV文件导入一些数据，但允许数据库自动用默认值填充一些列（id列的序列的nextval，或者updated_at列的now（））。

为此，您必须告诉copy命令CSV文件中有哪些列，如下所示：

for fle in sorted(pathlib.path(path,"database/data/").glob("*.csv")):
    logging.info('>>>Migrating data %s', fle)
    table_name = fle.stem
    try:
        with conn1.cursor() as cur:
            logging.info('Deleting data from table %s', psycopg2.extensions.quote_ident(table_name))
            cur.execute('TRUNCATE %s CASCADE' % psycopg2.extensions.quote_ident(table_name, cur))
            logging.info('i am done and waiting')

            with open(fle, 'r') as f:
                cur.copy_from(
                    f,
                    table_name,
                    sep=',',
                    columns=[
                        'demographic_type',
                        'updated_at',
                        'street_address',
                        'city',
                        'state_or_province',
                        'postal_code',
                        'secondary_phone',
                        # more columns, but without id or created_at
                    ]
                )
        conn1.commit()
    except psycopg2.Error as exp2:
        print ('Error Code: %s. Error %s' % (exp2.pgcode, exp2.pgerror))