Why does my psycopg2 insert fail, but the subquery works?

Asked Apr 06 '18 at 14:56

Active Apr 06 '18 at 16:22

Viewed 335 times

When I execute the following python code against my Redshift database the insert fails, even though the subquery operates correctly. I'm pretty sure this is some simple concept I'm missing here, but I don't know what it is. Any suggestions on how to make this work?

con_f = psycopg2.connect(dbname=dbname, host=host, port=port, user=user, password=password)
cur_f = con_f.cursor()

move_qry = 'insert into schema.' + target_table + ' (SELECT * from ' + staging_tbl +  ');'
print(move_qry)
cur_f.execute(move_qry)
print(cur_f.query)
print(cur_f.statusmessage)

insert into schema.tbl_final (SELECT * from staging.tbl_final_tmp);
insert into schema.tbl_final (SELECT * from staging.tbl_final_tmp);
INSERT 0 6388714

edited Apr 06 '18 at 16:22

asked Apr 06 '18 at 14:56

Thom Rogers

1,385
2
20
33

What do you mean by "the insert fails"? – John Rotenstein Apr 07 '18 at 11:57
Nothing gets written to schema.tbl_final – Thom Rogers Apr 07 '18 at 18:02
If you run `SELECT * from staging.tbl_final_tmp` before the INSERT, does it show anything? What happens if you run the same commands from an SQL client instead of Python? – John Rotenstein Apr 07 '18 at 21:46
When running the same commands in SQL client dBeaver, the insert succeeds – Thom Rogers Apr 09 '18 at 13:07
Does this fix your issue? [Python psycopg2 not inserting into postgresql table](https://stackoverflow.com/q/18068901/174777) – John Rotenstein Apr 09 '18 at 21:37

Why does my psycopg2 insert fail, but the subquery works?

0 Answers0