Backend for the persistent library using postgresql.

Version on this page:
LTS Haskell 22.30:
Stackage Nightly 2024-07-21:
Latest on Hackage:

See all snapshots persistent-postgresql appears in

MIT licensed by Felipe Lessa, Michael Snoyman
Maintained by Michael Snoyman
This version can be pinned in stack with:persistent-postgresql-,3686

Module documentation for

Based on the postgresql-simple package


Changelog for persistent-postgresql

  • #1341
    • Add SqlBackendHooks to allow for instrumentation of queries.
  • #1327
    • Update backend to support new StatementCache interface

  • #1349
    • Add BackendCompatible (RawPostgresql b) (RawPostgresql b) instance.

  • #1351
    • Support aeson-2.0 in test suite.

  • #1331
    • Fixes a bug where upsertWhere would fail on a database table with MigrationOnly fields.

  • #1316
    • Expose some internals in the new Database.Persist.Postgresql.Internal module. This gives access to the P newtype, which is used for de-serializing PersistValues from postgresql-simple code.

  • #1305
    • Add RawPostgresql wrapper, which exposes the underlying Postgres connection used to construct a SqlBackend.

  • #1290
    • Fix the code path for adding references to previously defined columns.

  • Actually release the SafeTORemove fix

  • #1225
    • Support persistent- making SQlBackend internal

  • #1235
    • upsertWhere and upsertManyWhere only worked in cases where a Primary key was defined on a record, and no other uniqueness constraints. They have been fixed to only work with records that have a single Uniqueness constraint defined.

  • Added upsertWhere and upsertManyWhere to persistent-postgresql. #1222.

  • Decomposed HaskellName into ConstraintNameHS, EntityNameHS, FieldNameHS. Decomposed DBName into ConstraintNameDB, EntityNameDB, FieldNameDB respectively. #1174
  • Fix XML conversion #1192

  • Foreign Key improvements [#1121]
    • It is now supported to refer to a table with an auto generated Primary Kay
    • It is now supported to refer to non-primary fields, using the keyword References
  • Implement interval support. #1053
  • #1060
    • The QuasiQuoter now supports OnDelete and OnUpdate cascade options.
  • Handle foreign key constraint names over 63 characters. See #996 for details.
  • Fix a bug in upsertSql query which had not been discovered previously because the query wasn’t actually used. #856
  • #1072 Refactored test/JSONTest.hs to use hspec
    • added runConn_ to run a db connection and return result
    • Renamed db to runConnAssert in test/PgInit.hs for clarity
    • Ran test/ArrayAggTest.hs (which was previously written but not being run)
  • Remove unnecessary deriving of Typeable #1114
  • Add support for configuring the number of stripes and idle timeout for connection pools #1098
    • PostgresConf has two new fields to configure these values.
      • Its FromJSON instance will default stripes to 1 and idle timeout to 600 seconds
      • If you’re constructing a PostgresConf manually, this is a breaking change
    • Add createPostgresqlPoolWithConf and withPostgresqlPoolWithConf, which take a PostgresConf for the new configuration.

  • Fix issue with multiple foreign keys on single column. #1010

  • Compatibility with latest persistent-template for test suite #1002


  • Added support for the constraint= attribute to the Postgresql backend. #979


  • Added question mark operators ((?.), (?|.), (?&.)) to Database.Persist.Postgresql.JSON #863
  • Changes to certain types:
    • PersistValue: added PersistArray data constructor
    • Filter: Changed the filterValue :: Either a [a] to filterValue :: FilterValue


  • Add openSimpleConnWithVersion function. #883


  • Added support for SQL isolation levels to via SqlBackend. [#812]
  • Fix 832: repsertMany now matches mapM_ (uncurry repsert) and is atomic.


Added module Database.Persist.Postgresql.JSON #793

  • PersistField and PersistFieldSql instances for Data.Aeson.Value
  • Filter operators (@>.) and (<@.) to filter on JSON values

  • Added a more detailed error message when a numeric column’s scale and precision can’t be parsed. #781


  • Implemented connPutManySql to utilize batched putMany. #770


  • Switch from MonadBaseControl to MonadUnliftIO


  • Added new function migrateEnableExtension, to enable Postgres extensions in migrations.

  • Because text and varchar are synonyms in Postgresql, don’t attempt to migrate between them. #762

  • Fix bug where, if a custom column width was set, the field would be migrated every time #742


  • Expose new functions: withPostgresqlPoolWithVersion, withPostgresqlConnWithVersion and createPostgresqlPoolModifiedWithVersion.


  • Match changes in persistent
  • Clean up warnings


  • Atomic upsert support for postgreSQL backend


  • changes for read/write typeclass split


  • Postgresql primary key is Int4, not Int8 #519

  • Allow postgresql-simple 0.5

Query pg_catalog instead of information_schema for metadata. This helps with permission issues as reported in issue #501


  • Fix treatment of NULLs inside arrays. For example, now you can use array_agg on a nullable column.

  • New derived instances for PostgresConf: Read, Data and Typeable.

  • New mockMigration function. Works like printMigration but doesn’t need a database connection.

  • Fix typo on error message of the FromJSON instance of PostgresConf.


  • Optimize the insertMany function to insert all rows and retrieve their keys in one SQL query. #407


  • Postgresql exceptions #353

Migrations for custom primary keys

Support foreign key references to composite primary keys #389


  • Allow timestamp value in database to be serialized (presumes UTC timezone) Yesod #391


  • Treat unknown extension types as PersistDbSpecific values #385


Documentation typo fix


Added FromJSON instance for PostgresConf.