pg Atom Feed

Description
Socket-level interface to the PostgreSQL database
Latest
pg-0.60.0.20250928.144633.tar (.sig), 2025-Sep-28, 720 KiB
Maintainer
Eric Marsden <eric.marsden@risk-engineering.org>
Website
https://github.com/emarsden/pg-el
Browse ELPA's repository
CGit or Gitweb
Badge

To install this package from Emacs, use package-install or list-packages.

Full description

pg.el -- Emacs Lisp socket-level interface to the PostgreSQL RDBMS

License: GPL v3 Latest tagged version MELPA NonGNU ELPA test-pgv16 Documentation build

This library lets you access the PostgreSQL 🐘 database management system from Emacs, using its network-level frontend/backend ā€œwireā€ protocol. The module is capable of automatic type coercions from a range of SQL types to the equivalent Emacs Lisp type.

šŸ“– You may be interested in the user manual.

This is a developer-oriented library, which won’t be useful to end users. If you’re looking for a browsing/editing interface to PostgreSQL in Emacs, you may be interested in PGmacs.

This library has support for:

  • SCRAM-SHA-256 authentication (the default authentication method since PostgreSQL version 14), as well as MD5 and password authentication. There is currently no support for GSSAPI authentication.

  • Encrypted (TLS) connections with the PostgreSQL database, if your Emacs has been built with GnuTLS support. This includes support for authentication using client certificates.

  • Prepared statements using PostgreSQL’s extended query protocol, to avoid SQL injection attacks.

  • The PostgreSQL COPY protocol to copy preformatted data from an Emacs buffer to PostgreSQL, or to dump a PostgreSQL table or query result to an Emacs buffer in CSV or TSV format.

  • Asynchronous handling of LISTEN/NOTIFY notification messages from PostgreSQL, allowing the implementation of publish-subscribe architectures (PostgreSQL as an ā€œevent brokerā€ or ā€œmessage busā€ and Emacs as event publisher and consumer).

  • Parsing various PostgreSQL types including integers, floats, array types, numerical ranges, JSON and JSONB objects into their native Emacs Lisp equivalents. The parsing support is user-extensible. Support for the HSTORE, pgvector, PostGIS, BM25 extensions.

  • Connections over TCP or (on Unix machines) a local Unix socket.

Tested PostgreSQL versions: The code has been tested with versions 18.0, 17.6, 16.5, 15.4, 13.8, 11.17, and 10.22 on Linux. It is also tested via GitHub actions on MacOS and Windows. This library also works, more or less, against other ā€œPostgreSQL-compatibleā€ databases. There are four main points where this compatibility may be problematic:

  • Compatibility with the PostgreSQL wire protocol. This is the most basic form of compatibility.

  • Compatibility with the PostgreSQL flavour of SQL, such as row expressions, non-standard functions such as CHR, data types such as BIT, VARBIT, JSON and JSONB, user-defined ENUMS and so on, functionality such as LISTEN. Some databases that claim to be ā€œPostgres compatibleā€ don’t even support foreign keys, views, triggers, sequences, tablespaces and temporary tables (looking at you, Amazon Aurora DSQL).

  • Implementation of the system tables that are used by certain pg-el functions, to retrieve the list of tables in a database, the list of types, and so on.

  • Establishing encrypted TLS connections to hosted services. Most PostgreSQL client libraries (in particular the official client library libpq) use OpenSSL for TLS support, whereas Emacs uses GnuTLS, and you may encounter incompatibilities.

The following PostgreSQL-compatible databases or extensions have been tested:

  • Neon ā€œserverless PostgreSQLā€ works perfectly. This is a commercially hosted service using a new storage engine for PostgreSQL, that they make available under the Apache licence. Last tested 2025-08.

  • ParadeDB version 0.13.1 works perfectly (it's really a PostgreSQL extension rather than a distinct database implementation).

  • IvorySQL works perfectly (this Apache licensed fork of PostgreSQL adds some features for compatibility with Oracle). Last tested 2025-08 with version 4.5.

  • The Timescale DB extension for time series data, source available but non open source. This works perfectly (last tested 2025-09 with version 2.21).

  • The CitusDB extension for sharding PostgreSQL over multiple hosts (AGPLv3 licence). Works perfectly (last tested 2025-07 with Citus version 13.0).

  • The OrioleDB extension, which adds a new storage engine designed for better multithreading and solid state storage, works perfectly. Last tested 2025-07 with version beta12.

  • The Microsoft DocumentDB extension for MongoDB-like queries (MIT licensed). Works perfectly. Note that this is not the same product as Amazon DocumentDB. Last tested 2025-09 with the FerretDB distribution 2.3.1.

  • The Hydra Columnar extension for column-oriented storage and parallel queries (Apache license). Works perfectly (last tested 2025-05 with v1.1.2).

  • The AgensGraph extension for transactional graph processing (Apache 2 license) works perfectly. Last tested 2025-07 with version 2.15.

  • The PgBouncer connection pooler for PostgreSQL (open source, ISC licensed). Works fine (last tested 2025-09 with version 1.24 in the default session pooling mode).

  • The Odyssey connection pooler from Yandex (BSD license) works perfectly with pg-el (last tested 2025-08 with version 1.4.0 in session pooling mode).

  • The PgDog sharding connection pooler for PostgreSQL (AGPLv3 licensed). We encounter some errors when using the extended query protocol: unnamed prepared statements and prepared statments named __pgdog_N are reported not to exist. The pooler also disconnects the client when the client-encoding is switched to LATIN1 (last tested 2025-08).

  • The PgCat sharding connection pooler for PostgreSQL (MIT license). Mostly works but sometimes runs into read timeouts (last tested 2025-08 with v0.2.5).

  • Google AlloyDB Omni is a proprietary fork of PostgreSQL with Google-developed extensions, including a columnar storage extension, adaptive autovacuum, and an index advisor. It works perfectly with pg-el as of 2025-08 (version that reports itself as "15.7").

  • PolarDB for PostgreSQL is free software (Apache 2 licence) developed by Alibaba Cloud, also available as a commercial hosted service with a proprietary distributed storage architecture. It works perfectly with pg-el (last tested 2025-09 with version 15.14).

  • openGauss is an open source (Mulan PSL v2) fork of PostgreSQL maintained by Huawei. It works fine with pg-el (last tested 2025-09 with version 7.0.0-RC2).

  • Xata ā€œserverless PostgreSQLā€ has many limitations including lack of support for CREATE DATABASE, CREATE COLLATION, for XML processing, for temporary tables, for cursors, for EXPLAIN, for CREATE EXTENSION, for DROP FUNCTION, for functions such as pg_notify.

  • The YugabyteDB distributed database (Apache licence). Mostly working though the pg_sequences table is not implemented so certain tests fail. YugabyteDB does not have full compatibility with PostgreSQL SQL, and for example GENERATED ALWAYS AS columns are not supported, and LISTEN and NOTIFY are not supported. It does support certain extensions such as pgvector, however. Last tested on 2025-09 against version 2.25.

  • The RisingWave event streaming database (Apache license) is mostly working. It does not support GENERATED ALWAYS AS IDENTITY or SERIAL columns, nor VACUUM ANALYZE. Last tested 2025-09 with v2.6.0.

  • The CrateDB distributed database (Apache licence). CrateDB does not support rows (e.g. SELECT (1,2)), does not support the time, varbit, bytea, jsonb and hstore types, does not handle a query which only contains an SQL comment, does not handle various PostgreSQL functions such as factorial, does not return a correct type OID for text columns in rows returned from a prepared statement, doesn't support Unicode identifiers, doesn't support the COPY protocol, doesn't support TRUNCATE TABLE. It works with these limitations with pg-el (last tested 2025-09 with version 5.10.12).

  • The CockroachDB distributed database (source-available but non-free software licence). Note that this database does not implement the large object functionality, and its interpretation of SQL occasionally differs from that of PostgreSQL. Currently fails with an internal error on the SQL generated by our query for pg-table-owner, and fails on the boolean vector syntax b'1001000'. Works with these limitations with pg-el (last tested 2025-09 with CockroachDB CCL v25.3).

  • The YDB by Yandex distributed database (Apache licence). Has very limited PostgreSQL compatibility. For example, an empty query string leads to a hung connection, and the bit type is returned as a string with the wrong oid. Last tested 2025-05 with version 23-4.

  • The Materialize operational database (a proprietary differential dataflow database) has many limitations in its PostgreSQL compatibility: no support for primary keys, unique constraints, check constraints, for the bit type for example. It works with these limitations with pg-el (last tested 2025-09 with Materialize v0.158).

  • The CedarDB database spun off from the Umbra research database developed at the University of Munich is fairly PostgreSQL compatible and works well with pg-el. Last tested 2025-09 with CedarDB version v2025-09-24.

  • The QuestDB time series database (Apache licensed) has very limited PostgreSQL support, and does not support the integer type for example. Last tested 2025-09 with version 9.0.3.

  • The proprietary Yellowbrick distributed database does not implement SERIAL columns, nor datatypes such as text, bit and timetz, nor collation, nor enums, nor functions such as gen_random_uuid, nor large objects. It has limited support for the UTF8 encoding, and its implementation of the numeric type is buggy. It works with these limitations with pg-el (last tested 2025-08 with version 7.4.0 of the YellowBrick community edition).

  • Google Spanner proprietary distributed database: tested with the Spanner emulator (that reports itself as PostgreSQL 14.1) and the PGAdapter library that enables support for the PostgreSQL wire protocol. Spanner has very limited PostgreSQL compatibility, for example refusing to create tables that do not have a primary key. It does not recognize basic PostgreSQL types such as INT2 and TIMESTAMP. It also does not for example support the CHR and MD5 functions, row expressions, and WHERE clauses without a FROM clause.

  • The Vertica distributed database (a propriety column-oriented database targeting analytics workloads). Its PostgreSQL compatibility is limited: it does not support certain datatypes such as int2, int4 and text, the parsing of timetz strings is not compatible with PostgreSQL, the serialization of arrays is not PostgreSQL-compatible. Last tested 2025-07 with the community edition, version 25.3.

  • YottaDB Octo, which is built on the YottaDB key-value store (which is historically based on the MUMPS programming language). GNU AGPL v3 licence. There are many limitations in the PostgreSQL compatibility: no user metainformation, no cursors, no server-side prepared statements, no support for various types including arrays, JSON, UUID, vectors, tsvector, numeric ranges, geometric types. It works with these limitations with pg-el (last tested 2025-07 with YottaDB 2.0.2).

  • The GreptimeDB time series database (Apache license) implements quite a lot of the PostgreSQL wire protocol, but the names it uses for types in the pg_catalog.pg_types table are not the same as those used by PostgreSQL (e.g. Int64 instead of int8), so our parsing machinery does not work. This database also has more restrictions on the use of identifiers than PostgreSQL (for example, id is not accepted as a column name, nor are identifiers containing Unicode characters). Last tested v0.17 in 2025-09.

  • Hosted PostgreSQL services that have been tested: as of 2025-06 render.com is running a Debian build of PostgreSQL 16.8 and works fine (requires TLS connection), as of 2024-12 Railway.app is running a Debian build of PostgreSQL 16.4, and works fine; Aiven.io is running a Red Hat build of PostgreSQL 16.4 on Linux/Aarch64 and works fine. TheNile is running a modified version of PostgreSQL 15, and has several limitations (for example, comments on tables and comments don't work, you can't create functions or procedures).

  • Untested but likely to work: Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL, Amazon Aurora, CrunchyData Warehouse. You may however encounter difficulties with TLS connections, as noted above. Reports on success or problems encountered with these databases are welcome.

PostgreSQL variants that don't work with pg-el:

  • The ClickHouse database, whose PostgreSQL support is too limited. As of version 25.8 in 2025-09, there is no implementation of the pg_types system table, no support for basic PostgreSQL-flavoured SQL commands such as SET, no support for the extended query mechanism.

  • The ReadySet cache does not work in a satisfactory manner: it generate spurious errors such as invalid binary data value when using the extended query protocol and is unable to parse certain timestamps (last tested 2025-08).

Tested Emacs versions: mostly tested with versions 31 pre-release, 30.1 and 29.4. Emacs versions older than 26.1 will not work against a recent PostgreSQL version (whose default configuration requires SCRAM-SHA-256 authentication), because they don’t include the GnuTLS support which we use to calculate HMACs. They may however work against a database set up to allow unauthenticated local connections. Emacs versions older than 28.1 (from April 2022) will not be able to use the extended query protocol (prepared statements), because they don’t have the necessary bindat functionality. It should however be easy to update the installed version of bindat.el for these older versions.

[!TIP] Emacs 31 (in pre-release) has support for disabling the Nagle algorithm on TCP network connections (TCP_NODELAY). This leads to far better performance for PostgreSQL connections, in particular on Unix platforms. This performance difference does not apply when you connect to PostgreSQL over a local Unix socket connection.

You may be interested in an alternative library emacs-libpq that enables access to PostgreSQL from Emacs by binding to the libpq library.

Installation

Install via the NonGNU ELPA package archive by running the command

M-x package-install RET pg

Alternatively, install via the MELPA package archive by including the following in your Emacs initialization file (.emacs.el or init.el):

(require 'package)
(add-to-list 'package-archives '("melpa" . "https://melpa.org/packages/") t)

then saying

 M-x package-install RET pg

Alternatively, you can install the library from the latest GitHub revision using:

 (unless (package-installed-p 'pg)
    (package-vc-install "https://github.com/emarsden/pg-el" nil nil 'pg))

You can later update to the latest version with M-x package-vc-upgrade RET pg RET.

Acknowledgements

Thanks to Eric Ludlam for discovering a bug in the date parsing routines, to Hartmut Pilch and Yoshio Katayama for adding multibyte support, and to Doug McNaught and Pavel Janik for bug fixes.

Old versions

pg-0.60.0.20250921.132950.tar.lz2025-Sep-21 136 KiB
pg-0.59.0.20250831.93432.tar.lz2025-Aug-31 134 KiB
pg-0.58.0.20250827.115343.tar.lz2025-Aug-27 133 KiB
pg-0.58.0.20250823.202123.tar.lz2025-Aug-24 132 KiB
pg-0.58.0.20250813.134611.tar.lz2025-Aug-13 130 KiB
pg-0.57.0.20250730.130322.tar.lz2025-Jul-30 127 KiB
pg-0.56.0.20250720.94306.tar.lz2025-Jul-20 124 KiB
pg-0.55.0.20250718.91654.tar.lz2025-Jul-18 124 KiB
pg-0.55.0.20250715.93720.tar.lz2025-Jul-15 124 KiB
pg-0.55.0.20250629.134638.tar.lz2025-Jul-14 124 KiB

News

Changelog

[0.61] - Unreleased

  • Add support for providing a password for authentication as a function, rather than as a string. This allows for integration with the auth-source functionality in Emacs, and helps to reduce the length of time where passwords remain present in RAM. Patch from @Kaylebor.

[0.60] - 2025-09-21

  • Add support for version 3.2 of the wire protocol, introduced in PostgreSQL v18. The only change with respect to the previously supported version 3.0 is the length of the key used to authenticate requests to cancel an ongoing query. As for libpq, we default to using version 3.0, because several PostgreSQL variants do not support version 3.2 and have not yet implemented the protocol version downgrade functionality that is designed into the protocol.

    Version 3.2 of the protocol can be selected by passing (3 . 2) as the value for the :protocol-version argument to pg-connect-plist and pg-connect/direct-tls, or by using a protocol_version URL parameter to pg-connect/uri.

  • New function to establish PostgreSQL connections pg-connect-plist. This function is similar to pg-connect, but takes keyword arguments instead of optional arguments. Function pg-connect is deprecated. Similarly, the new macro with-pg-connection-plist should be used instead of with-pg-connection in new code.

  • Recently introduced function pg-connect/direct-tls has been deprecated; use the :direct-tls option to pg-connect-plist instead.

  • Add detection code and workarounds for the PostgreSQL variants OpenGauss (by Huawei) and pgsqlite.

  • Add parsing support for arrays of time- and date-related objects.

  • The input and output buffers used for communication with PostgreSQL are now trimmed when they become too large, with only the most recent data retained. The number of octets to retain for each buffer can be customized using the variable pg-connection-buffer-octets.

[0.59] - 2025-08-31

  • Add detection code and workarounds for the Yellowbrick PostgreSQL variant.

  • Add support for parsing an options parameter in a connection string or connection URI, or for parsing the contents of the PGOPTIONS environment variable (as per the libpq behaviour). This is an alternative to using elisp code on pg-new-connection-hook.

  • Fixes for serializing bpchar values when they are used by PostgreSQL to represent CHAR(N) fields (bug reported by @Tekki).

  • Change the mechanism used to send messages to PostgreSQL: instead of sending data incrementally we accumulate data in a per-connection output buffer and send it in a chunk when pg-flush is called. This should reduce the number of small fragmented network packets exchanged with the PostgreSQL backend, and should improve performance.

[0.58] - 2025-08-13

  • Add serialization support for the numeric data type, for arguments to prepared statements.

  • Add serialization support for homogeneous arrays when using the extended query protocol. Types such as text[] (known as _text in the pg_type system table), int4[], float4[], float8[] can now be passed as function arguments.

  • Fix parsing of the bpchar type, which PostgreSQL uses on the wire to represent CHARACTER(N) objects. These objects would previously be truncated to their first character, but are now parsed as a full length string if the length is greater than 1 character, and still as an Emacs Lisp character when the length is equal to 1. The returned string includes trailing padding spaces if the value inserted is less long than the field width. Bug reported by @Tekki.

  • Add serialization support for the _varchar data type.

  • Add parsing and serialization support for the _uuid data type.

  • Add workarounds and detection code for the CedarDB PostgreSQL variant.

  • New error classes pg-duplicate-table and pg-duplicate-column, subclasses of ... ...