PHPackages                             opsway/doctrine-dbal-postgresql - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. opsway/doctrine-dbal-postgresql

ActiveLibrary[Database & ORM](/categories/database)

opsway/doctrine-dbal-postgresql
===============================

Extensions for support Postgres in Doctrine DBAL & DQL

v2.1.0(1y ago)1621.9M—1.9%37[3 PRs](https://github.com/opsway/doctrine-dbal-postgresql/pulls)MITPHPPHP ^8.1

Since Apr 5Pushed 1y ago47 watchersCompare

[ Source](https://github.com/opsway/doctrine-dbal-postgresql)[ Packagist](https://packagist.org/packages/opsway/doctrine-dbal-postgresql)[ RSS](/packages/opsway-doctrine-dbal-postgresql/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (10)Dependencies (10)Versions (31)Used By (0)

doctrine-dbal-postgresql
========================

[](#doctrine-dbal-postgresql)

[![Latest Stable Version](https://camo.githubusercontent.com/e7bba01532902b28f1ebebb5d754532eb323df375c4cec8b60d51aa982b480dd/68747470733a2f2f706f7365722e707567782e6f72672f6f70737761792f646f637472696e652d6462616c2d706f737467726573716c2f762f737461626c65)](https://packagist.org/packages/opsway/doctrine-dbal-postgresql) [![Total Downloads](https://camo.githubusercontent.com/bb10e65c3fcb26b1e76233d296dcb00f7d33c062a049bda165289fd07aa2d3f5/68747470733a2f2f706f7365722e707567782e6f72672f6f70737761792f646f637472696e652d6462616c2d706f737467726573716c2f646f776e6c6f616473)](https://packagist.org/packages/opsway/doctrine-dbal-postgresql) [![Latest Unstable Version](https://camo.githubusercontent.com/1f0b9b44e864018e07df0ce670b41f8205c526e887c88a484cdbae06b7b9f4b7/68747470733a2f2f706f7365722e707567782e6f72672f6f70737761792f646f637472696e652d6462616c2d706f737467726573716c2f762f756e737461626c65)](https://packagist.org/packages/opsway/doctrine-dbal-postgresql)

This component allows you to manage some native [PostgreSQL](http://www.postgresql.org)data types, operators and functions with the Doctrine [DBAL](http://www.doctrine-project.org/projects/dbal.html) component.

Usage
-----

[](#usage)

Add to composer.json

```
php composer.phar require opsway/doctrine-dbal-postgresql ~0.8
```

To use the new types you should register them using the [Custom Mapping Types](https://doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#custom-mapping-types) feature.

To use the new functions you should register them using the [DQL User Defined Functions](https://www.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/dql-user-defined-functions.html) feature.

#### Custom Types

[](#custom-types)

- Array Integer (integer\[\])
- Array BigInt (bigint\[\])
- TsVector (tsvector)

#### Custom DQL functions

[](#custom-dql-functions)

- CONTAINS - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\Contains'
- CONTAINED - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\Contained'
- GET\_JSON\_FIELD - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\GetJsonField'
- GET\_JSON\_FIELD\_BY\_KEY - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\GetJsonFieldByKey'
- GET\_JSON\_OBJECT - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\GetJsonObject'
- GET\_JSON\_OBJECT\_TEXT - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\GetJsonObjectText'
- ANY\_OP - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\Any'
- ALL\_OP - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\All'
- ARR - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\Arr'
- ARR\_AGGREGATE - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\ArrayAggregate'
- ARR\_APPEND - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\ArrayAppend'
- ARR\_REPLACE - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\ArrayReplace'
- REGEXP\_REPLACE - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\RegexpReplace'
- ARR\_REMOVE - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\ArrayRemove'
- ARR\_CONTAINS - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\ArrayContains'
- TO\_TSQUERY - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\ToTsquery'
- TO\_TSVECTOR - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\ToTsvector'
- TS\_CONCAT\_OP - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\TsConcat'
- TS\_MATCH\_OP - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\TsMatch'
- UNNEST - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\Unnest'
- JSON\_AGG - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\JsonAgg'
- JSONB\_ARRAY\_ELEM\_TEXT - 'OpsWay\\Doctrine\\ORM\\Query\\AST\\Functions\\JsonbArrayElementsText'

### Custom DQL function usage

[](#custom-dql-function-usage)

For an example the CONTAINS function requires your table column in your database to be of the type `jsonb`. Otherwise PostgreSQL will not recognize the operator needed to perform this action. (@>)

- Tip: Based on the function you want to use, check if there are any specific column type requirements.

Example query:

```
$result = $this->em->createQuery(
    'SELECT l FROM Foo\Bar\Baz l WHERE CONTAINS(l.metaData, :value) = true')
    ->setParameter('value', json_encode(['foo'=>'bar']))
    ->getResult();
```

Setting the column type to `jsonb`.

```
/**
 * @var array
 *
 * @ORM\Column(type="json", nullable=true, options={"jsonb": true})
 */
private $metaData;

```

**Note:** If you want to use these DQL functions on an existing `json` field, you will have to alter its column type (running `make:migration` after adding `options={"jsonb": true}` will not be enough). This migration is an example of how you can do it:

```