DBAL-815: Returning a wrong field type for Postgres #2048

Closed
doctrinebot opened this Issue Feb 18, 2014 · 9 comments

2 participants

@doctrinebot

Jira issue originally created by user odiel:

Trying to create a Postgres table widh a JSON field type I found a bug, the following code is not returning the right field type for this operation.

https://github.com/doctrine/dbal/blob/ca6a8dd20f3e8c1ad5fadaac8eac4547c081cf3b/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php#L864

It is returning TEXT instead of JSON.

Take in consideration that if a table was created before and an update is required in order to change the table structure errors will shown up, here an example.

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'ALTER TABLE mytable ALTER metadata TYPE JSON':

SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "metadata" cannot be cast automatically to type json
HINT: Specify a USING expression to perform the conversion.

[PDOException]
SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "metadata" cannot be cast automatically to type json
HINT: Specify a USING expression to perform the conversion.

@doctrinebot

Comment created by @deeky666:

[~odiel] The JSON data type is only available in PostgreSQL version 9.2 and onwards. We added support for the native JSON data type in DBAL 2.5, which is not yet released. If you want to make use of the native JSON type you have to configure DBAL to use PostgreSQL92Platform.

@doctrinebot

Comment created by @deeky666:

Still for versions < 9.2 the TEXT type still should work. Unfortunately you did not provide enough information about what you are trying to achieve and in which context your mentioned errors occurr. But this is definitely not a bug in Doctrine.

@doctrinebot

Comment created by @deeky666:

Here is the PR where we implemented native JSON type support: #469

@doctrinebot

Comment created by odiel:

Yes sorry about that.

Basically I'm trying to create a JSON field type in a Postgres table, I'm using Postgres 9.3.

Trying to update my DB from the command line using Symfony2 commands, doctrine:update:schema, I always get a query updating the field to be TEXT, even if I force the command to run, it pops up again and again, not changing the field to be JSON type, here is the code for my entity:

<?php

use Doctrine\ORM\Mapping as ORM;

/****
 * @ORM\Table(name="mytable")
 */
class Mytable
{
    /****
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /****
     * @var string
     *
     * @ORM\Column(name="signature", type="json_array")
     */
    private $signature;

    /****
     * @var int
     *
     * @ORM\Column(name="created_at", type="datetime")
     */
    private $createdAt;


    /****
     * Get id
     *
     * @codeCoverageIgnore
     * @return integer
     */
    public function getSignature()
    {
        return $this->id;
    }

    /****
     * Sets the job id.
     *
     * @param $signature
     * @return $this
     */
    public function setSignature($signature)
    {
        $this->signature = $signature;

        return $this;
    }

    /****
     * Sets the created at timestamp.
     *
     * @param int $timestamp
     * @return $this
     */
    public function setCreatedAt($timestamp)
    {
        $this->createdAt = $timestamp;

        return $this;
    }

    /****
     * Returns the created at timestamp.
     *
     * @return int
     */
    public function getCreatedAt()
    {
        return $this->createdAt;
    }

}
@doctrinebot

Comment created by @deeky666:

Yeah, as I said, native JSON types will be supported in DBAL 2.5. If you want to make immediate use of this, you have to upgrade your dependencies to use one of the Doctrine 2.5 beta versions or the current master. Also please note when doing this upgrade for DBAL only, this might break ORM compatibility unless you upgrade ORM to an according version, too.

@doctrinebot

Issue was closed with resolution "Duplicate"

@doctrinebot

Comment created by odiel:

Ok, that sounds good, thanks for the explanation, would you be able to point me out a good ORM version compatible with it?

@doctrinebot

Comment created by @deeky666:

IMO you should use DBAL master and ORM master, which should be in sync at most. BUT!! We do not advice you to use it in production as we don't take responsibility for it being stable by now. ORM does not have a beta for 2.5 yet.

@doctrinebot doctrinebot added the Bug label Dec 7, 2015
@beberlei beberlei was assigned by doctrinebot Dec 7, 2015
@doctrinebot doctrinebot added this to the 2.5 milestone Dec 7, 2015
@doctrinebot doctrinebot closed this Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment