PostgreSQL

SereneDB can work with a running PostgreSQL database directly — attach it and read and write its tables alongside your local data in the same SQL, with no export or copy step.

Attach a database

ATTACH adds the PostgreSQL database to the catalog. The connection string is a list of {key}={value} arguments, and TYPE postgres tells SereneDB to open it as PostgreSQL:

Query

ATTACH 'host=localhost port=5432 dbname=mydb user=postgres' AS pg (TYPE postgres);

Attachments are per-session

Attachment definitions are not persisted — after a restart or a new connection, run ATTACH again to make the database available.

Once attached, its tables are referenced as catalog.schema.table and queried like any local table:

Query

SELECT id, name, signups FROM pg.app.users ORDER BY id;

Result

 id | name  | signups----+-------+---------  1 | alice |      12  2 | bob   |       7  3 | carol |      23

The first argument is a PostgreSQL connection string. The common arguments are:

Name	Description	Default
host	Host to connect to	localhost
port	Port number	5432
user	PostgreSQL user name	OS user name
password	PostgreSQL password
dbname	Database name	user name

Pass READ_ONLY after the type — (TYPE postgres, READ_ONLY) — to open the database for reading only.

Scan a single table

To read one table without attaching the whole database, use postgres_scan(connection_string, schema, table):

Query

-- Read a single table without attaching the whole databaseSELECT id, name FROM postgres_scan('host=localhost port=5432 dbname=mydb', 'app', 'users');

Result

 id | name----+-------  1 | alice  2 | bob  3 | carol

Write to the attached database

An attached database is read-write by default. INSERT, UPDATE, DELETE and CREATE TABLE work just like local tables — the changes are written straight back to PostgreSQL:

Query

-- An attached database is read-write: write to it like a local tableINSERT INTO pg.app.users VALUES (4, 'dave', 5);

Detach

Close the connection when you are done:

Query

DETACH pg;

See also

• ATTACH AND DETACH — full reference for attaching PostgreSQL and other databases