Search code examples
postgresqlpysparkjdbcaws-databricks

Unable to insert data in Postgres using Jdbc

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.

Solution

  • 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.