PostgreSQL Bulk Loader failed to enter copy state

[despainj83]

Hi,

I'm trying to utilize the new PostgreSQL bulk loader to write to our Greenplum database. Our current versions are: PostgreSQL 8.3.23 (Greenplum Database 5.26.0). I'm using the ANSI x64 PostgreSQL ODBC driver version 9.06.05.00.

Here is the error text from the DB:

,"ERROR","42601","syntax error at or near ""(""",,,,,,"COPY ""aaw_alteryx"".""jdd_test_bulk3"" from STDIN (DELIMITER ',', FORMAT csv, QUOTE '""', NULL '', ENCODING 'UTF8');",48,,"scan.l",982,

It doesn't seem to like the parentheses in the syntax that Alteryx is sending to the server.  Our database people think it may be PostgreSQL version issue.

Anyone have ideas on how to resolve this? 

Thanks,

John Despain

[joshuaburkhow]

Hey @despainj83 

Don't know enough yet about PostgreSQL but that error code seems to be tied to dynamic vs plain queries here: https://bobcares.com/blog/postgresql-error-42601/#:~:text=PostgreSQL%20error%2042601%20also%20occurs....

The same point made here: 

https://stackoverflow.com/questions/16291944/postgresql-sql-state-42601-syntax-error

Hope this helps!

[HenrietteH · Accepted solution]

Hi @despainj83 

Your Database team is correct, this is a Postgres version issue. 

I dug through their documentation and it looks like they changed the syntax for the COPY command slightly and they added parameters. The ENCODING parameter we have in our commend wasn't added until version 9.1 of postgres. 

8.3 documentation

9.1 documentation

I'm not sure what you can do besides upgrade..... 

[ntobon]

Release Notes:  https://www.postgresql.org/docs/release/9.1.0/

E.76.3.5.1. COPY

• Add ENCODING option to COPY TO/FROM (Hitoshi Harada, Itagaki Takahiro)

  This allows the encoding of the COPY file to be specified separately from client encoding.

• Add bidirectional COPY protocol support (Fujii Masao)

  This is currently only used by streaming replication.

[PIRAI]

Welcome to Pirai Infotech, where innovation converges with transformation. As a premier digital solutions company, we specialize in shaping the future of businesses through cutting-edge technologies and strategic insights. Our Google Cloud Computing Services, designed to accelerate your journey towards digital innovation, enable you to swiftly prototype and launch your product idea. This allows you to gather valuable feedback and iterate efficiently. With  Pirai Infotech as your trusted partner, you can confidently leverage Google Cloud Computing Services to navigate the complexities of digital transformation and unlock new opportunities for growth. Discover the possibilities with Pirai Infotech – your trusted partner for innovative and sustainable digital transformation.