Skip to content
Skills Library
Data & analytics · Tool integration

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.

Licence
UPL-1.0
Last repository change
26.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
1
Deprecation

No signals detected. Repository changed on 26.10.2026, without references to retired models or APIs.

Licence

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

psql
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

  1. When to use
  2. When NOT to use
  3. Read
  4. Write
  5. Pushdown SQL
  6. Gotchas
  7. References
Original SKILL.md headings · 131 lines, 517 words
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.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.