psycopg.errors.SyntaxError: syntax error at or near "ON" with execute

Question

I'm using psycopg3 and PostgreSQL 14. When I run a copy or exec function, and include a ON argument, it gives me the error psycopg.errors.SyntaxError: syntax error at or near "ON"

I have tried two different functions, both resulting in the same error.

 cur.execute("""
  CREATE TABLE temp_mls AS TABLE mls_properties WITH NO DATA
  ON COMMIT DROP
   """)

and

cur.copy("""COPY mls_properties (fips_code") FROM STDIN
            ON CONFLICT DO NOTHING
                """)

I don't think ON CONFLICT DO NOTHING is valid in COPY - see docs. — snakecharmerb
– snakecharmerb, Commented Sep 21, 2022 at 16:12
@snakecharmerb That's what I also thought, but have the exact same error on execute. — Hannan Rhodes
– Hannan Rhodes, Commented Sep 21, 2022 at 17:27
Because ON.. is not valid syntax for the PostgreSQL copy command. Nothing to do with psycopg. — snakecharmerb
– snakecharmerb, Commented Sep 21, 2022 at 17:33
Good point, also seems ON is invalid for create table as well. Thanks for your help and time. — Hannan Rhodes
– Hannan Rhodes, Commented Sep 21, 2022 at 17:42

jian · Accepted Answer · 2022-09-21 18:19:07Z

1

No "CONFLICT" word in https://www.postgresql.org/docs/current/sql-copy.html.

Obviously, ON COMMIT DROP should warp in an transaction. However it only apply to temp table. Quote from manual:

ON COMMIT
The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:
PRESERVE ROWS
No special action is taken at the ends of transactions. This is the default behavior.
DELETE ROWS
All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit.
DROP
The temporary table will be dropped at the end of the current transaction block.

So the following will work:

BEGIN;
CREATE temp TABLE temp_mls ON COMMIT DROP AS table test WITH NO DATA;
COMMIT;

answered Sep 21, 2022 at 18:19

jian

5,0231 gold badge29 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

psycopg.errors.SyntaxError: syntax error at or near "ON" with execute

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related