yiisoft / db-pgsql

PostgreSQL driver for Yii Database

PostgreSQL driver for Yii Database

PostgreSQL driver for Yii Database is a PostgreSQL database adapter. The package provides a database connection interface and a set of classes for interacting with a PostgreSQL database. It allows you to perform common database operations such as executing queries, building and executing INSERT, UPDATE, and DELETE statements, and working with transactions. It also provides support for PostgreSQL-specific features such as stored procedures and server-side cursors.

Support version

PHP	Pgsql Version	CI-Actions
8.0 - 8.2	9.0 - 15.0

Installation

The package could be installed via composer:

composer require yiisoft/db-pgsql

Documentation

English:

• For config connection to PostgreSQL database check Connecting PostgreSQL.
• Check the documentation to learn about usage.

Português - Brasil:

• Para configurar a conexão com o PostgreSQL leia Connecting PostgreSQL.s
• Confira a documentação para aprender como usar.

Testing:

• Internals

Support

If you need help or have a question, the Yii Forum is a good place for that. You may also check out other Yii Community Resources.

Support the project

Follow updates

License

The Yii DataBase PostgreSQL Extension is free software. It is released under the terms of the BSD License. Please see LICENSE for more information.

Maintained by Yii Software.

db-pgsql's Issues

Add test for negative default values and parentheses

See #295

Dependabot can't resolve your PHP dependency files

Dependabot can't resolve your PHP dependency files.

As a result, Dependabot couldn't update your dependencies.

The error Dependabot encountered was:

Your requirements could not be resolved to an installable set of packages.
  Problem 1
    - Installation request for yiisoft/yii-web ^3.0@dev -> satisfiable by yiisoft/yii-web[3.0.x-dev].
    - yiisoft/yii-web 3.0.x-dev requires psr/http-factory-implementation 1.0 -> no matching package found.
  Problem 2
    - Installation request for yiisoft/view ^3.0@dev -> satisfiable by yiisoft/view[3.0.x-dev].
    - yiisoft/view 3.0.x-dev requires psr/event-dispatcher-implementation 1.0.0 -> no matching package found.

Potential causes:
 - A typo in the package name
 - The package is not available in a stable-enough version according to your minimum-stability setting
   see <https://getcomposer.org/doc/04-schema.md#minimum-stability> for more details.
 - It's a private package and you forgot to add a custom repository to find it

Read <https://getcomposer.org/doc/articles/troubleshooting.md> for further common problems.

If you think the above is an error on Dependabot's side please don't hesitate to get in touch - we'll do whatever we can to fix it.

View the update logs.

May be need add compatible result for phptypes?

See -

db-pgsql/src/ColumnSchema.php

Line 81 in 3d47b9a

$value = $this->getArrayParser()->parse($value);

if ($this->dimension > 0) {
            if (!is_array($value)) {
                $value = $this->getArrayParser()->parse($value);
            }
...

After change DB from pgsql to mysql - we can't will use class ArrayParser (and other pgsql specified types from this package) in our application

if ($value instanceof ArrayParser) - will be throws Exception

Invalid index columns order

https://github.com/yiisoft/db-pgsql/blob/master/src/Schema.php#L340
This line provides invalid index column order

yiisoft/yii2#18507

Make `ArrayParser` internal and prevent multiple create instance of it

1) Suggest make ArrayParser as internal. Users don't must use this class directly, it'sright? (#297 (comment))

2. For each type casting of string values created new instance of ArrayParser.

db-pgsql/src/ColumnSchema.php

Lines 107 to 112 in 56df5ac

	public function phpTypecast(mixed $value): mixed
	{
	if ($this->dimension > 0) {
	if (is_string($value)) {
	$value = $this->getArrayParser()->parse($value);
	}

It would be good prevent this behavior and create instance of ArrayParser once or make ArrayParser methods static.

Similar case: #303 (comment)

Schema not loading with `PDO::ATTR_STRINGIFY_FETCHES => true`

$dsn = (new Dsn('pgsql', '127.0.0.1'))->asString();
$pdoDriver = new Driver($dsn, 'postgres', '', [\PDO::ATTR_STRINGIFY_FETCHES => true]);
$schemaCache = new SchemaCache(new FileCache(__DIR__ . '/runtime/cache'));
$db = new Connection($pdoDriver, $schemaCache);
$schema = $db->getSchema();
$tableSchema = $schema->getTableSchema('table_name', true);

The result is
TypeError: Yiisoft\Db\Schema\AbstractColumnSchema::allowNull(): Argument #1 ($value) must be of type bool, string given, called in ...\db-pgsql\src\Schema.php on line 775

Use stable version of `yiisoft/db` in CI

Now used dev-master:

Wrong SQL with not unique index type

Good day. In in case when create GIN/GIST other not unique index asresult we have wrong SQL query. For example in migration:

$b->createIndex('name', 'table', 'column', 'GIN');

CREATE GIN INDEX 'name' ON 'table' USING GIN ('column') //wrong GIN after CREATE

Dependabot can't resolve your PHP dependency files

Dependabot can't resolve your PHP dependency files.

As a result, Dependabot couldn't update your dependencies.

The error Dependabot encountered was:

Your requirements could not be resolved to an installable set of packages.
  Problem 1
    - Installation request for yiisoft/db-pgsql No version set (parsed as 1.0.0) -> satisfiable by yiisoft/db-pgsql[No version set (parsed as 1.0.0)].
    - yiisoft/db 3.0.x-dev requires yiisoft/db-pgsql ^3.0@dev -> satisfiable by yiisoft/db-pgsql[3.0.x-dev].
    - Can only install one of: yiisoft/db-pgsql[3.0.x-dev, No version set (parsed as 1.0.0)].
    - Installation request for yiisoft/db ^3.0@dev -> satisfiable by yiisoft/db[3.0.x-dev].

If you think the above is an error on Dependabot's side please don't hesitate to get in touch - we'll do whatever we can to fix it.

View the update logs.

Add ability to alter column type without changing default value and null-state

What steps will reproduce the problem?

if I write a query that changes column type only, query builder add queries that will drop not null and default value
https://github.com/yiisoft/db-pgsql/blob/master/src/QueryBuilder.php#L333
https://github.com/yiisoft/db-pgsql/blob/master/src/QueryBuilder.php#L345

What is the expected result?

If I want to change type only, default value and null-state should be kept as is

Trim git history

update links

What steps will reproduce the problem?

http=>https

Upsert commands always builds a INSERT statemant for PostgreSQL Updatable Views

After upgrading to 2.0.14, application always show errors like this:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "tbl_user_session_pkey"
DETAIL: Key (id)=(s9ud40t3uorkfo22t9bvd76am3 )
The SQL being executed was: INSERT INTO "tbl_user_session" ("data", "id", "expire") VALUES ('__flash|a:0:{}', 's9ud40t3uorkfo22t9bvd76am3', 1519149822);

For security reasons, we designed the database to have a schema "protected" that have all tables, and a schema "public", that have no tables, only filtered updatable views.

The database component have this schemaMap config:

'schemaMap' => [
    'pgsql'=> [
        'class'=>'yii\db\pgsql\Schema',
        'defaultSchema' => 'public',
    ],
],

The upsert command introduced in Yii2 2.0.14 didn't recognize the primary keys or unique keys metadata from Updatable Views, so it always build a INSERT statement instead.

Additional info

Q	A
Yii version	2.0.14
PHP version	5.6, 7.1
Operating system	any
PostgreSQL version	9.5.5

Side note: My ActiveRecord models works on past Yii2 versions because I fix the primary keys manually. They extends the class below:

<?php

namespace app\common\models;

use Yii;

class UpdatableViewActiveRecord extends \yii\db\ActiveRecord
{
    public static function primaryKey()
    {
        throw new \Exception("primary key must be overrided!");
    }

    public static function getTableSchema()
    {
        $tabelSchema = parent::getTableSchema();

        if (static::primaryKey()) {
            $tabelSchema->fixPrimaryKey(static::primaryKey());
        }

        return $tabelSchema;
    }
}

Prevent auto increment of primary key on duplicate values when `upsert()`

When attempting to upsert() duplicate values the autoincrement primary key will be incremented even if no duplicates are inserted.

$columns = ['unique_field' => 'unique value', 'field' => 'value'];
$command->upsert('table_name', $columns, false)->execute();

Generated query:

INSERT INTO table_name (unique_field, field) VALUES ('unique value', 'value') ON CONFLICT DO NOTHING

This can be solved by generating query without ON CONFLICT DO NOTHING

INSERT INTO table_name (unique_field, field)
SELECT 'unique value', 'value'
WHERE NOT EXISTS(
    SELECT 1
    FROM table_name
    WHERE unique_field = 'unique value'
)

Q	A
Version	1.1.0

How to after release

What steps will reproduce the problem?

Тип array postgresql возвращается object, сейчас еще есть возможность сказать дай array https://github.com/yiisoft/yii2/blob/1fe3d61a3bac1fb8e59b9d026d9f37b5c5eadd4a/framework/db/pgsql/ColumnSchema.php#L99

What is the expected result?

Теперь после обновления

db-pgsql/src/ColumnSchema.php

Line 66 in 3edf67d

return new ArrayExpression($value, $this->dbType, $this->dimension);

всегда object

What do you get instead?

Нельзя ли оставить возможность выбора, потому что не удобно получать данные. Когда из базы после populate летит object приходится в модели делать getер, задача которого вызывать getValue() и все, хотя лучше просто из базы получить нужный массив с данными... Но может я не прав... В общем как будем жить дальше?

Additional info

Q	A
Yii version	2.0.16
PHP version	7.2
Operating system	CentOS 7

Wrong type for serial in migration

Good day. After last update of yiisoft/db pgsql migration set wrong type (varchar) for serial type. It doesn't matter how set that type - using MigrstionBuilger::primaryKey method or set it manually using ColumnSchemaBuilder instance. And i not tested with other db/native db types. Thanks

Support composite types

PostgreSQL has composite types.
https://www.postgresql.org/docs/current/rowtypes.html

They are useful to store complex data, for example money with currency

CREATE TYPE currency_money AS
(
  value numeric(19,4),
  currency_code char(3)
);

CREATE TABLE product (
    id serial NOT NULL,
    price currency_money DEFAULT '(0,USD)',
    CONSTRAINT product_pkey PRIMARY KEY (id)
);

In PHP we can use it as array

$command->insert('product', ['price' => ['value' => 10, 'currency_code' => 'USD']])->execute(); // or [10, 'USD']
$price = (new Query($db))->select(['price'])->from('product')->scalar();
$pricePhpType = $tableSchema->getColumn('price')->phpTypecast($price);
$pricePhpType === ['value' => 10, 'currency_code' => 'USD'];

Examples of composite types

CREATE TYPE file_with_name AS
(
 path text,
 name text
);

CREATE TYPE url_with_name AS
(
 url text,
 name text
);

CREATE TYPE phone_composite AS
(
 country_code char(2),
 number bigint,
 extension smallint,
 leading_zeros smallint,
 type text
);

CREATE TYPE permission_composite AS
(
 create bool,
 view bool,
 update bool,
 delete bool
);

But it is not possible to transfer data to another DBMS without changing PHP code.

I can realize if you agree.

Add support for USING clause for alterColumn

Postgres alter column often require explicit typecast with USING clause

Propose to add the ability for support

    $db->alterColumn('my_table', 'text_field', 'VARCHAR(255) USING text_field::varchar')

https://github.com/yiisoft/db-pgsql/blob/master/src/QueryBuilder.php#L319