aidp-postgresql
/aidp-postgresql
Read or write PostgreSQL from an AIDP notebook via the AIDP `aidataplatform` Spark format handler. Use when the user mentions PostgreSQL, Postgres, "psql", or has a Postgres host/port to connect to. Prefer the official `POSTGRESQL` connector sample; use native Spark JDBC only as an SSL-required fallback when the built-in connector cannot express sslmode=require.
- Author
- oracle-samples ↗
- Repository
- oracle-aidp-samples ↗
- Licence
- UPL-1.0
- Last repository change
- 26.10.2026
Assessment and security
- Files in this package
- 1
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 1
No signals detected. Repository changed on 26.10.2026, without references to retired models or APIs.
UPL-1.0, otwarta.
What it does and when to use it
Read or write PostgreSQL from an AIDP notebook via the AIDP `aidataplatform` Spark format handler. Use when the user mentions PostgreSQL, Postgres, "psql", or has a Postgres host/port to connect to. Prefer the official `POSTGRESQL` connector sample; use native Spark JDBC only as an SSL-required fallback when the built-in connector cannot express sslmode=require.
Triggers from the description
Original author description
Read or write PostgreSQL from an AIDP notebook via the AIDP `aidataplatform` Spark format handler. Use when the user mentions PostgreSQL, Postgres, "psql", or has a Postgres host/port to connect to. Prefer the official `POSTGRESQL` connector sample; use native Spark JDBC only as an SSL-required fallback when the built-in connector cannot express sslmode=require.
How it works
- When to use
- When NOT to use
- Read
- Write
- Pushdown SQL
- Gotchas
- References
SKILL.md preview
---
name: aidp-postgresql
description: Read or write PostgreSQL from an AIDP notebook via the AIDP `aidataplatform` Spark format handler. Use when the user mentions PostgreSQL, Postgres, "psql", or has a Postgres host/port to connect to. Prefer the official `POSTGRESQL` connector sample; use native Spark JDBC only as an SSL-required fallback when the built-in connector cannot express sslmode=require.
allowed-tools: Read, Write, Edit, Bash
---
# `aidp-postgresql` — PostgreSQL via AIDP `aidataplatform`
## When to use
- User wants to read or write a PostgreSQL database from an AIDP notebook.
- Mentioned: "PostgreSQL", "Postgres", "psql".
## When NOT to use
- For MySQL / HeatWave → [`aidp-mysql`](../aidp-mysql/SKILL.md).
- For SQL Server → [`aidp-sqlserver`](../aidp-sqlserver/SKILL.md).
- For arbitrary JDBC-only DBs → [`aidp-jdbc-custom`](../aidp-jdbc-custom/SKILL.md).
## Read
### Option A: AIDP `aidataplatform` format (default)
Use the official AIDP PostgreSQL connector sample first.
```python
import os
from oracle_ai_data_platform_connectors.aidataplatform import (
AIDP_FORMAT, aidataplatform_options,
)
opts = aidataplatform_options(
type="POSTGRESQL",
host=os.environ["PG_HOST"],
port=int(os.environ.get("PG_PORT", "5432")),
user=os.environ["PG_USER"],
password=os.environ["PG_PASSWORD"],
schema=os.environ.get("PG_SCHEMA", "public"),
table=os.environ["PG_TABLE"],
)
df = spark.read.format(AIDP_FORMAT).options(**opts).load()
df.show()
```
## Write
```python
opts = aidataplatform_options(
type="POSTGRESQL",
host=os.environ["PG_HOST"],
port=int(os.environ.get("PG_PORT", "5432")),
user=os.environ["PG_USER"],
password=os.environ["PG_PASSWORD"],
schema=os.environ.get("PG_SCHEMA", "public"),
table=os.environ["PG_TARGET_TABLE"],
extra={"write.mode": "CREATE"}, # CREATE | APPEND | OVERWRITE
)
df.write.format(AIDP_FORMAT).options(**opts).save()
```
## Pushdown SQL
```python
opts = aidataplatform_options(
type="POSTGRESQL",
host=os.environ["PG_HOST"],
port=int(os.environ.get("PG_PORT", "5432")),
user=os.environ["PG_USER"],
password=os.environ["PG_PASSWORD"],
extra={
"pushdown.sql": (
f"SELECT * FROM {os.environ.get('PG_SCHEMA', 'public')}.{os.environ['PG_TABLE']} "
"WHERE updated_at >= DATE '2024-01-01'"
),
},
)
df = spark.read.format(AIDP_FORMAT).options(**opts).load()
df.show()
```
### Option B: Native Spark JDBC fallback for SSL-required endpoints
Use this only when the target PostgreSQL service rejects non-TLS connections and the AIDP connector cannot pass the required SSL option. The observed failure mode is `[PostgreSQL]connection is insecure (try using sslmode=require)`. In that case, use Spark native JDBC with URL-embedded `sslmode=require`.
The cluster may not have `org.postgresql.Driver` pre-installed, so runtime-load it the same way `aidp-jdbc-custom` does.
```python
import hashlib, os, urllib.request
from py4j.java_gateway import java_import
JAR_PATH = "/tmp/postgresql-42.7.4.jar"
JAR_SHA256 = "188976721ead8e8627eb6d8389d500dccc0c9bebd885268a3047180274a6031e" # re-pin when changing the version
if not os.path.exists(JAR_PATH):
urllib.request.urlretrieve(
"https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.4/postgresql-42.7.4.jar",
JAR_PATH,
)
if hashlib.sha256(open(JAR_PATH, "rb").read()).hexdigest() != JAR_SHA256:
os.remove(JAR_PATH)
raise RuntimeError("postgresql jar checksum mismatch")
# Register driver on driver JVM
gw = spark._sc._gateway
url = spark._jvm.java.io.File(JAR_PATH).toURI().toURL()
arr = gw.new_array(spark._jvm.java.net.URL, 1); arr[0] = url
ucl = spark._jvm.java.net.URLClassLoader(arr, spark._jvm.java.lang.ClassLoader.getSystemClassLoader())
spark._jvm.java.lang.Thread.currentThread().setContextClassLoader(ucl)
DriverCls = spark._jvm.java.lang.Class.forName("org.postgresql.Driver", True, ucl)
spark._jvm.java.sql.DriverManager.registerDriver(DriverCls.newInstance())
spark._jsc.addJar(JAR_PATH) # distribute to executors
# Now read — note sslmode=require URL-embedded
JDBC_URL = (
f"jdbc:postgresql://{os.environ['PG_HOST']}:{os.environ.get('PG_PORT','5432')}"
f"/{os.environ['PG_DB']}?sslmode=require"
)
df = (spark.read.format("jdbc")
.option("url", JDBC_URL)
.option("driver", "org.postgresql.Driver")
.option("user", os.environ["PG_USER"])
.option("password", os.environ["PG_PASSWORD"])
.option("dbtable", f"{os.environ.get('PG_SCHEMA','public')}.{os.environ['PG_TABLE']}")
.load())
df.show(5)
```
## Gotchas
- **Use `aidataplatform` first.** It is the official sample path and has its own bundled driver.
- **SSL fallback** — if the AIDP `POSTGRESQL` handler cannot pass SSL options and the server requires `sslmode=require`, use Option B with native Spark JDBC. Verified live 2026-04-27 against Neon serverless 17.8.
- **No bundled driver for native Spark JDBC** — if you use Option B, runtime-load the PostgreSQL jar and register it via `URLClassLoader` + `DriverManager.registerDriver` + `spark._jsc.addJar`.
- **Network reachability** — Postgres must be reachable from the AIDP cluster's NAT egress IP. Public-internet endpoints (Neon, Supabase, RDS public) work via the cluster's NAT path. Self-hosted Postgres in user-managed VCNs typically does NOT work — the cluster's pod CIDR has no route to user VCNs without explicit VCN peering. Smoke-test with a Python socket: `socket.create_connection((host, 5432), timeout=8)`.
- **`schema`** is the Postgres logical schema (e.g. `public`), not the database name. The database name is a separate `PG_DB` env var that goes into the JDBC URL.
- **Write modes** — `CREATE` (fail if exists), `APPEND`, `OVERWRITE`. Default is `CREATE`.
## References
- Helper: [scripts/oracle_ai_data_platform_connectors/aidataplatform.py](../../scripts/oracle_ai_data_platform_connectors/aidataplatform.py)
- Official sample: [data-engineering/ingestion/Read_Write_External_Ecosystem_Connectors/PostgreSQL.ipynb](../../../../../data-engineering/ingestion/Read_Write_External_Ecosystem_Connectors/PostgreSQL.ipynb)
Versions
The database has no other skills named aidp-postgresql.
Identical content appears in 2 other GitHub folders. We show one copy with the highest score.
Database snapshot: 13.11.2026 · package version 3563746737e6 (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 100aidp-db2
Read or write IBM Db2 from an AIDP notebook through the AIDP `aidataplatform` Spark format handler. Use when the user mentions DB2, IBM Db2, LUW, or `type=DB2`. Auth is host/port + database name + user/password. - 100aidp-sqlserver
Read or write Microsoft SQL Server from an AIDP notebook via the AIDP `aidataplatform` Spark format handler. Use when the user mentions SQL Server, MSSQL, or has a TDS host/port. Auth is host/port + database + user/password. - 100postgres-patterns
Provides PostgreSQL patterns for query optimization, schema design, indexing strategies, RLS, and security. Use when working with PostgreSQL SQL files or when the user mentions PostgreSQL, Postgres, pgvector, Supabase, or database optimization. - 100matlab-connect-databricks
Connect MATLAB to Databricks via Spark (Databricks Connect) or JDBC (Database Toolbox). Use when setting up the MATLAB Interface for Databricks, configuring authentication (OauthU2M, OauthM2M, PAT), creating Spark sessions with getDatabricksSession(), reading Unity Catalog… - 100materialize-postgres-create
Create a PostgreSQL materialization to stream Estuary collections into PostgreSQL tables. Use when setting up PostgreSQL as a destination for captured data. Use when user says "send to Postgres", "materialize to PostgreSQL", "Postgres destination", or "load into Postgres". - 100postgres
Use when user references PostgreSQL/Postgres, postgres:// or postgresql:// connection strings, asks for ad-hoc SELECT, EXPLAIN/ANALYZE plans, schema/index/lock inspection, or slow-query investigation. Read-only by default. Multi-database via PG_PROFILE.