copy dataframe to postgres table with column that has defalut value

Question

I have the following postgreSql table stock, there the structure is following with column insert_time has a default value now()

|    column   |  pk |    type   |
+-------------+-----+-----------+
| id          | yes | int       |
| type        | yes | enum      |
| c_date      |     | date      |
| qty         |     | int       |
| insert_time |     | timestamp |

I was trying to copy the followning df

|  id | type |    date    | qty  |
+-----+------+------------+------+
| 001 | CB04 | 2015-01-01 |  700 |
| 155 | AB01 | 2015-01-01 |  500 |
| 300 | AB01 | 2015-01-01 | 1500 |

I was using psycopg to upload the df to the table stock

cur.copy_from(df, stock, null='', sep=',')
conn.commit()

getting this error.

DataError: missing data for column "insert_time"
CONTEXT:  COPY stock, line 1: "001,CB04,2015-01-01,700"

I was expecting with the psycopg copy_from function, my postgresql table will auto-populate the rows along side the insert time.

|  id | type |    date    | qty  |     insert_time     |
+-----+------+------------+------+---------------------+
| 001 | CB04 | 2015-01-01 |  700 | 2018-07-25 12:00:00 |
| 155 | AB01 | 2015-01-01 |  500 | 2018-07-25 12:00:00 |
| 300 | AB01 | 2015-01-01 | 1500 | 2018-07-25 12:00:00 |

Lunetta

Answer 1

You can specify columns like this:

cur.copy_from(df, stock, null='', sep=',', columns=('id', 'type', 'c_date', 'qty'))

Licence

Are you passing a dataframe or a object which has a read() method? It can be for example file or pandas or pyspark dataframe. copy_from(file, table, sep='\t', null='\\N', size=8192, columns=None). file – file-like object to read data from. It must have both read() and readline() methods. – Licence

i am passing the dataframe object, which obviously does not have any read() method – Romola

Indeed, my bad. The simplest (but not the most performant) solution using copy_from() would be to save the dataframe as csv, then open the csv for reading in python and pass the file object as the first parameter. However, pandas have df.to_sql() method. That doesn't allow specifying columns though - in this case I would suggest to preprocess the dataframe to match the table. – Licence