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](https://www.postgresql.org/docs/14/sql-copy.html). — snakecharmerb, Sep 21 '22 at 16:12
@snakecharmerb That's what I also thought, but have the exact same error on execute. — Hannan Rhodes, Sep 21 '22 at 17:27
Because `ON..` is not valid syntax for the _PostgreSQL_ copy command. Nothing to do with psycopg. — snakecharmerb, Sep 21 '22 at 17:33
Good point, also seems ON is invalid for create table as well. Thanks for your help and time. — Hannan Rhodes, Sep 21 '22 at 17:42

score 1 · Answer 1 · answered Sep 21 '22 at 18:19

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;

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

1 Answers1