JDBC not truncating Postgres table on pyspark-CodePudding

I'm using the following code to truncate a table before inserting data on it.

df.write \
    .option("driver", "org.postgresql:postgresql:42.2.16") \
    .option("truncate", True) \
    .jdbc(url=pgsql_connection, table="service", mode='append', properties=properties_postgres)

Although, it is not working. The table still with old data. I'm using append since I don't want to the DB drop and create a new table everytime.

I've tried .option("truncate", "true") but not worked too.

I got no error messages. How can i solve this problem using .option to truncate my table.

CodePudding user response：

You need to use overwrite mode

df.write \
    .option("driver", "org.postgresql:postgresql:42.2.16") \
    .option("truncate", True) \
    .jdbc(url=pgsql_connection, table="service", mode='overwrite', properties=properties_postgres)

As given in documentation

https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html

truncate: true -> When SaveMode.Overwrite is enabled, this option causes Spark to truncate an existing table instead of dropping and recreating it.