Copy command doesnt update the Id_sequence column when copying from csv throws error to null value in column “id” violates not-null constraint

0
votes

i have csv file and have to copy the data to the postgre table, if in my csv if i dont input data for Id and Updated_at will throw error , but it should not as id is marked as default and increment . i am doing this copy from python

Error:

2019-12-01T14:05:16.57+0530 [APP/PROC/WEB/0] OUT Error Code: 23502. Error ERROR: null value in column "id" violates not-null constraint 2019-12-01T14:05:16.57+0530 [APP/PROC/WEB/0] OUT DETAIL: Failing row contains (null, street_address, null).
2019-12-01T14:05:16.57+0530 [APP/PROC/WEB/0] OUT CONTEXT: COPY demographic_types, line 2: ",street_address,"

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 code

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))
2
pythonpostgresql
Hello, I think it tries to copy each column including ID which is null since it's not in the csv. Thus you should tell it to copy only this, this and this column. - Poutrathor
edit and format your code please. Provide part of your csv that has the problem and the exact copy command you use to update your db. UPDATA in itself, needs a id else it does not know , which entry to update. INSERT on the other hand should be fine - Patrick Artner
@PatrickArtner Artner sql = "COPY %s from STDIN WITH CSV HEADER DELIMITER ','" % table_name cur.copy_expert(sql, f) - Manoj Kasa
id, demographic_type,updated_at ,street_address, ,city, ,state_or_province, ,postal_code, ,secondary_phone, ,email_id, ,Name, ,Landline, ,landmark, ,Company, - Manoj Kasa
You should show your Python code fragment. It's hard to figure out what you are trying to do without it. Also, please make yourself familiar with question and answer formatting guide, to make your question readable. - Tometzky

2 Answers

0
votes

If I understand correctly you want to import some data from a CSV file, but allow the database to automatically fill some columns with default values (either nextval of a sequence for id column or now() for updated_at column).

For this you have to tell the copy command which columns are in the CSV file, like this:

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))
0
votes

A point of explanation. The main issue you are facing comes from a mis-understanding of what the default specification actually does. This issue arises do the expectation that supplying a default means Use this value instead of null. That is NOT the case, specifying a default says on Insert use this value if the column name is not mentioned in the column list. Thus given the following:

create table use_default ( id       serial primary key
                         , column1  text
                         , column2  text default 'Undefined'
                         ) ;

insert into use_default(column1) values('abc');                         
-- gives the default to column2 as it is NOT mentioned in the insert 

select * from use_default; 
/* results in 
id  column1  column2
1   abc      Undefined
*/

insert into use_default(column1, column2)
  values ('def','given value')
       , ('ghi', null) ;
-- gives row def with column2 as 'given value' as it is specified
-- but row ghi has column2 as NULL. again as it was specified value

select * from use_default;
/*
id  column1 column2
1   abc     Undefined
2   def     given value
3   ghi     
*/

Thus far each insert has generated the id as it has not specified. But just because it's specified auto generated (due least logically from serial definition) it still has the same processing if it is supplied. Note NULL is, sometimes unfortunately, a perfectly good value. So:

 insert into use_default(id, column1) values (0,'Special id'); 
 Works just fine and gives the id as specified;

 select * from use_default where id = 0;
/*
id  column1    column2
0   Special id Undefined
*/


insert into use_default(id, column1) values(null, 'Special2');
/*
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, Special2, Undefined).
*/

Now you would not write a statement attempting to set the PK to null, but that is essentially what the copy command is doing. As you did not specify the columns it's attempting to populate all columns using NULL rather than omitting some columns.