Unable to insert data in Postgres using Jdbc

Question

I am attempting to insert data into a PostgreSQL database using PySpark with JDBC. However, during the data insertion process, it is unexpectedly attempting to recreate the table and producing the following output.

org.postgresql.util.PSQLException: ERROR: relation "account" already exists

I am trying to use the below code snippet to write the data in Postgres.

def postgres_writes(url, driver, username, password, table_name, df):
df.write \
  .format("jdbc") \
  .option("url", url) \
  .option("dbtable", table_name) \
  .option("user", username) \
  .option("password", password) \
  .option("driver", driver) \
  .mode("append") \
  .save()

I want to append the data in an existing table.

Vijay_Shinde · Accepted Answer · 2023-06-21T10:24:38.137

1

Please make sure that, you are passing appropriate jdbc url. As per Postgres documentation Documentation Link you can use below JDBC URL's forms

jdbc:postgresql:database
jdbc:postgresql:/
jdbc:postgresql://host/database
jdbc:postgresql://host/
jdbc:postgresql://host:port/database
jdbc:postgresql://host:port/

Also try to recreate the table and try to load the data again. There might be some issue at Spark session level.

edited Jun 21 '23 at 10:24

answered Jun 21 '23 at 04:43

Vijay_Shinde

1,332
2
17
38

I am using jdbc:postgresql://host:port/database format – SK ASIF ALI Jun 21 '23 at 05:00
Ok. I tried your scenario. I am able to append the data. – Vijay_Shinde Jun 21 '23 at 05:05
Is there any configuration I need to change in the DB side? – SK ASIF ALI Jun 21 '23 at 05:09
Deleting and recreating the table resolve the issue – SK ASIF ALI Jun 21 '23 at 09:55
Thank you so much. Could you please accept the answer. – Vijay_Shinde Jun 21 '23 at 10:22

Unable to insert data in Postgres using Jdbc

1 Answers1