# PostgreSQL

The PostgreSQL connector (`CONNECTOR_TYPE=postgres`) translates GQL queries into
SQL and executes them against PostgreSQL. It requires a
[mapping file](https://memgraph.com/docs/memgraph-zero/memgql/quick-start#mapping-file) that maps graph patterns to
relational tables.

## 1. Start PostgreSQL

```bash
docker network create memgql-net

docker run -d --rm \
    --name postgres-dev \
    --network memgql-net \
    -p 5432:5432 \
    --env POSTGRES_PASSWORD=postgres \
    postgres:18
```

## 2. Seed data

```bash
docker exec -i postgres-dev psql -U postgres << 'SQL'
CREATE TABLE IF NOT EXISTS persons (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT
);
CREATE TABLE IF NOT EXISTS companies (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS knows (
    from_id INT REFERENCES persons(id),
    to_id INT REFERENCES persons(id)
);
CREATE TABLE IF NOT EXISTS works_at (
    person_id INT REFERENCES persons(id),
    company_id INT REFERENCES companies(id)
);

INSERT INTO persons (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25);
INSERT INTO companies (id, name) VALUES (1, 'Acme Corp');
INSERT INTO knows (from_id, to_id) VALUES (1, 2);
INSERT INTO works_at (person_id, company_id) VALUES (1, 1);
SQL
```

## 3. Start MemGQL

```bash
docker run --rm \
    --name memgql \
    --network memgql-net \
    --stop-timeout 2 \
    -p 7688:7688 \
    --env CONNECTOR_TYPE=postgres \
    --env POSTGRES_URL="host=postgres-dev user=postgres password=postgres dbname=postgres" \
    --env MAPPING_FILE=/data/mapping.json \
    --env BOLT_LISTEN_ADDR=0.0.0.0:7688 \
    -v ./mapping.json:/data/mapping.json \
    memgraph/memgql:latest
```

## 4. Connect

```bash
mgconsole --port 7688
```

## 5. Query

```gql
MATCH (p:Person) RETURN p.name, p.age;
```

```gql
MATCH (p:Person)-[:WORKS_AT]->(c:Company) RETURN p.name, c.name;
```

```gql
MATCH (a:Person)-[:KNOWS]->(b:Person) RETURN a.name, b.name;
```

For environment variables, see [Reference](https://memgraph.com/docs/memgraph-zero/memgql/reference#postgresql-postgres).

## TLS connections

For a cloud-hosted PostgreSQL, the connection **mode** goes in the connection
string as libpq's `sslmode=`, and the connector declares what a URI cannot
express — which certificates to trust:

```json
{
  "name": "pg",
  "type": "postgres",
  "connection": {
    "uri": "postgresql://user:pass@db.example.com:5432/app?sslmode=require",
    "sslRootCert": "/etc/ssl/certs/customer-ca.pem",
    "trustServerCertificate": false
  }
}
```

| Field | Description |
|-------|-------------|
| `sslmode=` (in `uri`) | libpq semantics, including the default: `disable` never attempts TLS, `prefer` uses it when it works, `require` makes it mandatory. |
| `sslRootCert` | Path to a PEM bundle of CA certificates to trust **instead of** the system roots. Omit it to use the system trust store. |
| `trustServerCertificate` | Encrypt without verifying the server's identity. |

TLS is implemented with [rustls](https://github.com/rustls/rustls), so there is
no OpenSSL or other C library to install.

`trustServerCertificate` protects against passive eavesdropping but **not**
against an active man-in-the-middle, and it logs a warning on every use. It
exists because managed instances are routinely fronted by a certificate that
doesn't match the hostname you dial; prefer `sslRootCert` where you can. The two
are mutually exclusive — a CA bundle plus "trust anything" is a contradiction
and is rejected.

Two more guardrails:

- Declaring TLS settings alongside `sslmode=disable` is **refused** rather than
  silently connecting in plaintext.
- Under `sslmode=prefer`, a failed handshake retries in plaintext and warns — so
  enabling TLS support doesn't strand a connector pointed at a server whose
  certificate you have no reason to trust. A connector that *declared*
  `sslRootCert` or `trustServerCertificate` never falls back; it named a trust
  anchor, so the failure is an error. Under `require` it is always an error.

These fields round-trip through `EXPORT SCHEMA`, so a dumped and reloaded
catalog reconnects with the same trust settings.

## JSONB columns

A `JSONB` column can be mapped as typed properties at fixed paths, or passed
through as a document whose undeclared keys stay queryable:

```json
"attributes": [
  { "name": "voltage", "column": "props", "path": "electrical.voltage", "type": "Double" },
  { "name": "props", "type": "Json" }
]
```

```gql
MATCH (c:Component) WHERE c.voltage > 100 RETURN c.sku, c.props.rohs;
```

Both forms push the extraction down to PostgreSQL, and a declared `type` makes
the comparison numeric instead of lexicographic. See
[Schema File → JSON / JSONB columns](https://memgraph.com/docs/memgraph-zero/memgql/schema-file#json--jsonb-columns).

## Supported GQL features

| Feature                                       | Postgres |
|-----------------------------------------------|----------|
| `MATCH` / `WHERE` / `RETURN`                  | ✓        |
| Pattern-level `WHERE` (`MATCH (n WHERE …)`)   | ✓        |
| Multiple `MATCH` clauses in one query         | ✓        |
| `OPTIONAL MATCH`                              | ✓        |
| `WITH` clause (chain query steps)             | ✓        |
| `WITH DISTINCT` / `WITH … ORDER BY … LIMIT N` | ✓        |
| Multiple chained `WITH` steps                 | ✓        |
| Pass a whole node through `WITH n`            | ✓        |
| Typed edge `(a)-[r:R]->(b)`                   | ✓        |
| Untyped edge `()-[]->(b)`                     | ✗        |
| `UNION` / `UNION ALL` / `UNION DISTINCT`      | ✓        |
| Quantified path `(){m,n}` (bounded)           | ✓        |
| Whole-node `RETURN n` / whole-rel `RETURN r`  | ✓        |
| Map projections `RETURN n {.a, .b}`           | ✓        |
| `IN` list membership `WHERE x IN [...]`       | ✓        |
| `STARTS WITH` / `ENDS WITH` / `CONTAINS`      | ✓        |
| `collect()` aggregate                         | ✓        |
| `count`, `sum`, `avg`, `min`, `max`           | ✓        |
| `COUNT(DISTINCT …)`                           | ✓        |
| Arithmetic `+ - * / %`                        | ✓        |
| `CASE WHEN … THEN … ELSE … END`               | ✓        |
| `COALESCE`, `NULLIF`                          | ✓        |
| `INSERT (a {…}) RETURN a.x`                   | ✓        |
| `DELETE`                                      | ✓        |
| `SET` / `REMOVE` (property update / delete)   | ✗        |
| `DETACH DELETE`                               | ✗        |
| `INTERSECT` / `EXCEPT`                        | ✗        |
| Quantified path `(){m,}` (unbounded)          | ✗        |
| Shortest-path (`ALL SHORTEST` / `SHORTEST k`) | ✗        |

## Known limitations

- **Unbounded variable-length paths** (`()-[*]->()`) are rejected. Bound the depth (`{1,5}`) or run the query against a Cypher backend.
- **`FOR x IN [...]` (UNWIND-style)** is rejected with an actionable error. Cypher backends (Memgraph, Neo4j) handle this syntax natively.
- **Path variables on variable-length patterns**: `MATCH p = (a){1,3}(b) RETURN p` is not supported. Drop the `p =` binding (or query a Cypher backend) and `RETURN` the individual nodes / edges instead.