How is PostgreSQL vs Mysql?

Are there any PostgreSQL and Mysql experts out there who have extensive experience with the Phalcon adapters? I've used Mysql a lot and never ran into huge problems except how esoteric it can be to setup the underlying PDO properly to receive integer keys back instead of string. Can anyone compare the two DB's and give a example of something that one can setup or do more easily in PHQL than the other? Are they pretty much equivalent? What about newer niche features? Anything that you can't live without in raw SQL that isn't provided by Phalcon yet?

edited Sep '18

By Raw sql you meant it, so it's possible. Probably you will use more with postgres than mysql

$db = \Phalcon\DI::getDefault()->get('db');

$result = $db->fetchAll("SELECT * FROM test where id = :id AND somenthing = :some", \Phalcon\Db::FETCH_ASSOC, [ 'id' => 1, 'some' => 'as' ],[bind types]);

edited Sep '18

I had some problems in PostgreSQL and Phalcon with custom functions like those used for jsonb operations.

It can be resolved by registering custom function for particular dialect, for example:

$dialect = new \Phalcon\Db\Dialect\Postgresql();
$dialect->registerCustomFunction(
    'jsonb_exists',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "jsonb_exists(%s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);
$dialect->registerCustomFunction(
    'jsonb_contains',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "jsonb_contains(%s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);
$dialect->registerCustomFunction(
    'jsonb_array_element_text',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "jsonb_array_element_text(%s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);
$dialect->registerCustomFunction(
    'jsonb_object_field_text',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "jsonb_object_field_text(%s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);

and then use it as argument when creating particular adapter:

$args = ['dialectClass' => $dialect, 'host' => ..., 'port' > ..., etc.];
$conn = new \Phalcon\Db\Adapter\Pdo\Postgresql( $args );

Maybe it will be helpful for You.



60.7k
edited Sep '18

Yes Tomasz thanks. I trying to build a list of odd behaviors and difficulties so that I can consider moving to Postgres.

You might want to consider moving your code to a Postgres dialect file in the Phalcon Incubator. If you just created a class based on the Mysql one and then added those functions then they would probably accept a pull request. Then everyone can easily use it from a composer package and it will get bug fixes. The fact that a Postrgres dialect class doesn't exist yet tells me that I need to continue asking questions for a learn about all of the edge cases.

[edit] I didn't see at first that you had already put this into a dialect class. This should be added to the Incubator.

Hello,

PostgreSQL dialect class is a part of Phalcon 3.4:

https://docs.phalconphp.com/en/3.4/api/Phalcon_Db_Dialect_Postgresql

so I don't think it need to be added to the Incubator, or maybe You was talking about the Example Code I provided?