Getting Started

This page gives you one working upsert and one working append against PostgreSQL. The examples use the datasource name pgstyx and assume the JAR is already on the classpath — see Install if not.

First upsert

Set writeMode=upsert and name the columns that define row identity in mergeKeys. Omitting mergeKeys in upsert mode throws IllegalArgumentException('mergekeys required for upsert mode').

Scala

df.write
  .format("pgstyx")
  .option("url", "jdbc:postgresql://localhost:5432/warehouse")
  .option("dbtable", "users")
  .option("user", "postgres")
  .option("password", "secret")
  .option("writeMode", "upsert")
  .option("mergeKeys", "user_id")
  .save()

Python

df.write \
  .format("pgstyx") \
  .option("url", "jdbc:postgresql://localhost:5432/warehouse") \
  .option("dbtable", "users") \
  .option("user", "postgres") \
  .option("password", "secret") \
  .option("writeMode", "upsert") \
  .option("mergeKeys", "user_id") \
  .save()

SQL

CREATE TEMP VIEW incoming_users USING json OPTIONS (path 'path/to/users.json');

CREATE TABLE users USING pgstyx
OPTIONS (
  url 'jdbc:postgresql://localhost:5432/warehouse',
  dbtable 'users',
  user 'postgres',
  password 'secret',
  writeMode 'upsert',
  mergeKeys 'user_id'
) AS SELECT * FROM incoming_users;

First write

url, dbtable, user, and password are required. writeMode defaults to append; it is shown below for clarity.

Scala

df.write
  .format("pgstyx")
  .option("url", "jdbc:postgresql://localhost:5432/warehouse")
  .option("dbtable", "events")
  .option("user", "postgres")
  .option("password", "secret")
  .option("writeMode", "append")
  .save()

Python

df.write \
  .format("pgstyx") \
  .option("url", "jdbc:postgresql://localhost:5432/warehouse") \
  .option("dbtable", "events") \
  .option("user", "postgres") \
  .option("password", "secret") \
  .option("writeMode", "append") \
  .save()

SQL

CREATE TEMP VIEW input_data USING json OPTIONS (path 'path/to/data.json');

CREATE TABLE events USING pgstyx
OPTIONS (
  url 'jdbc:postgresql://localhost:5432/warehouse',
  dbtable 'events',
  user 'postgres',
  password 'secret',
  writeMode 'append'
) AS SELECT * FROM input_data;

Next

• Changing how the write behaves → Configuration.
• Choosing between append, upsert, and overwrite → Write Modes.
• Source schema drifts from the target → Schema Evolution.
• Streaming workloads → Streaming.

Plan note

Composite mergeKeys, schemaEvolution beyond strict, validationMode beyond skip, and custom TLS material are paid-plan capabilities. See Pricing when a scenario requires them.