Copy the data frame to the postgres table using a column with default values… here is a solution to the problem.
Copy the data frame to the postgres table using a column with default values
I have the following postgreSql table stock, structured as follows, column insert_time has the default value now().
| column | pk | type |
+-------------+-----+-----------+
| id | yes | int |
| type | yes | enum |
| c_date | | date |
| qty | | int |
| insert_time | | timestamp |
I tried to reproduce the following df
| id | type | date | qty |
+-----+------+------------+------+
| 001 | CB04 | 2015-01-01 | 700 |
| 155 | AB01 | 2015-01-01 | 500 |
| 300 | AB01 | 2015-01-01 | 1500 |
I’m using psycopg to upload df to table stock
cur.copy_from(df, stock, null='', sep=',')
conn.commit()
This error occurs.
DataError: missing data for column "insert_time"
CONTEXT: COPY stock, line 1: "001,CB04,2015-01-01,700"
I’m expecting to use the psycopg copy_from function, where my postgresql table will autofill rows next to 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 |
Solution
You can specify columns like this:
cur.copy_from(df, stock, null='', sep=',', columns=('id', 'type', 'c_date', 'qty'))