Menu
I have a Postgresql table that has a column with data type JSONB.
How do I insert DataFrame to the Postgresql table via JDBC?
If I have a UDF to convert the the body column to the JSONB Postgresql data type, what is the corresponding pyspark.sql.types should I use?
Postgresql Table with a JSONB column:
CREATE TABLE dummy ( id bigint, body JSONB );
Thanks!
580
PostgreSQL offers two types for storing JSON data: json and jsonb . To implement efficient query mechanisms for these data types, PostgreSQL also provides the jsonpath data type described in Section 8.14. 7. The json and jsonb data types accept almost identical sets of values as input.
You can save any valid json value to either json or to a jsonb column. But you cannot bind it as string/ text / varchar , if you use prepared statements (use casts instead in sql, like UPDATE ... SET json_col = $1::json or bind it as unknown ).
The JSONB data type stores JSON (JavaScript Object Notation) data as a binary representation of the JSONB value, which eliminates whitespace, duplicate keys, and key ordering. JSONB supports GIN indexes.
PostgreSQL jsonb JSON data type stores the exact copy of input text in JSON. Jsonb stores the data as binary code. Basically, it stores the data in binary form which is not an ASCII/ UTF-8 string. Json preserves the original formatting like the whitespaces as well as the ordering of keys.
It turned out if I set "stringtype":"unspecified" as the properties of the JDBC, Postgres will cast automatically:
properties = {
"user": "***",
"password": "***",
"stringtype":"unspecified"
}
df.write.jdbc(url=url, table="dummy", properties=properties)
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With