pg 
- 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
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 asBIT
,VARBIT
,JSON
andJSONB
, user-defined ENUMS and so on, functionality such asLISTEN
. 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 toLATIN1
(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, forEXPLAIN
, forCREATE EXTENSION
, forDROP FUNCTION
, for functions such aspg_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 exampleGENERATED ALWAYS AS
columns are not supported, andLISTEN
andNOTIFY
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
orSERIAL
columns, norVACUUM 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 thetime
,varbit
,bytea
,jsonb
andhstore
types, does not handle a query which only contains an SQL comment, does not handle various PostgreSQL functions such asfactorial
, 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 theCOPY
protocol, doesn't supportTRUNCATE 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 astext
,bit
andtimetz
, nor collation, nor enums, nor functions such asgen_random_uuid
, nor large objects. It has limited support for the UTF8 encoding, and its implementation of thenumeric
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 asINT2
andTIMESTAMP
. It also does not for example support theCHR
andMD5
functions, row expressions, andWHERE
clauses without aFROM
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
andtext
, the parsing oftimetz
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 ofint8
), 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 asSET
, 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.lz | 2025-Sep-21 | 136 KiB |
pg-0.59.0.20250831.93432.tar.lz | 2025-Aug-31 | 134 KiB |
pg-0.58.0.20250827.115343.tar.lz | 2025-Aug-27 | 133 KiB |
pg-0.58.0.20250823.202123.tar.lz | 2025-Aug-24 | 132 KiB |
pg-0.58.0.20250813.134611.tar.lz | 2025-Aug-13 | 130 KiB |
pg-0.57.0.20250730.130322.tar.lz | 2025-Jul-30 | 127 KiB |
pg-0.56.0.20250720.94306.tar.lz | 2025-Jul-20 | 124 KiB |
pg-0.55.0.20250718.91654.tar.lz | 2025-Jul-18 | 124 KiB |
pg-0.55.0.20250715.93720.tar.lz | 2025-Jul-15 | 124 KiB |
pg-0.55.0.20250629.134638.tar.lz | 2025-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 topg-connect-plist
andpg-connect/direct-tls
, or by using aprotocol_version
URL parameter topg-connect/uri
.New function to establish PostgreSQL connections
pg-connect-plist
. This function is similar topg-connect
, but takes keyword arguments instead of optional arguments. Functionpg-connect
is deprecated. Similarly, the new macrowith-pg-connection-plist
should be used instead ofwith-pg-connection
in new code.Recently introduced function
pg-connect/direct-tls
has been deprecated; use the:direct-tls
option topg-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 thePGOPTIONS
environment variable (as per the libpq behaviour). This is an alternative to using elisp code onpg-new-connection-hook
.Fixes for serializing
bpchar
values when they are used by PostgreSQL to representCHAR(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 thepg_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 representCHARACTER(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
andpg-duplicate-column
, subclasses of ... ...