topic Re: column "id" is of type uuid but expression is of type character varying. in Get Started Discussions

column "id" is of type uuid but expression is of type character varying.

kp12 — Tue, 26 Sep 2023 11:56:32 GMT

Hello,

I'm trying to write to Azure PostgreSQL flexible database from Azure Databricks, using PostgreSQL connector in Databricks Runtime in 12.2LTS.

I'm using df.write.format("postgresql").save() to write to PostgreSQL database, but getting the following error:

column "id" is of type uuid but expression is of type character varying.

df has an id column that contains a guid, but is of string type in dataframe and of type uuid in PG database.

As there is no guid or uuid type in Databricks, whet is the best way to insert into an uuid type column?

Thanks,

Kalyani

Re: column "id" is of type uuid but expression is of type character varying.

Priyanka_Biswas — Mon, 02 Oct 2023 23:02:58 GMT

Hi @kp12 The error you are encountering is because the PostgreSQL UUID data type is not directly compatible with the string type in Spark DataFrame. You can try casting the string to binary type before writing to the database. Here is how you can do it:

python
from pyspark.sql.functions import expr

df = df.withColumn("id", expr("uuid(id)"))

df.write.format("jdbc").option("url", "jdbc:postgresql://<hostname>:<port>/<database>").option("dbtable", "<table>").option("user", "<username>").option("password", "<password>").save()

In the above code, replace <hostname>, <port>, <database>, <table>, <username>, and <password> with your actual PostgreSQL database details. Please note that you need to use "jdbc" as the format in the write method, not "postgresql". The "uuid()" function in the expr method is used to convert the string type to binary type which is compatible with the UUID type in PostgreSQL.

Re: column "id" is of type uuid but expression is of type character varying.

noname123 — Mon, 25 Mar 2024 13:37:44 GMT

Had same problem. You need to add option "stringtype": "unspecified".

Example:

df.write.format("postgresql").mode("overwrite").option("truncate", "true").option("stringtype", "unspecified").option("dbtable", table).option("host", host).option("database", database).option("user", username).option("password", password).save()

Re: column "id" is of type uuid but expression is of type character varying.

noname123 — Mon, 25 Mar 2024 13:42:48 GMT

This is completely wrong. Answer looks like chat gpt generated. Chat gpt always provide answers with jdbc connector type. Author is asking about PostgreSQL connector which is better than jdbc.

uuid(id) function don't accept arguments. You can do uuid() and it will generate random guid which will be of type string in dataframe. You provided function is just what chat gpt made up.

Re: column "id" is of type uuid but expression is of type character varying.

Student-Learn — Tue, 16 Apr 2024 02:43:12 GMT

Yes, this stack overflow was my reference too and adding below option made load go with no error on UUID data type in postgres column

.option(stringtype, "unspecified")

https://stackoverflow.com/questions/40973909/how-to-write-into-postgresql-hstore-using-spark-dataset/40979336#40979336