Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-815

Returning a wrong field type for Postgres

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: 2.5
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      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.

        Issue Links

          Activity

          Hide
          Steve Müller added a comment -

          Odiel Leon 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.

          Show
          Steve Müller added a comment - Odiel Leon 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.
          Hide
          Steve Müller added a comment -

          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.

          Show
          Steve Müller added a comment - 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.
          Hide
          Steve Müller added a comment -

          Here is the PR where we implemented native JSON type support: https://github.com/doctrine/dbal/pull/469

          Show
          Steve Müller added a comment - Here is the PR where we implemented native JSON type support: https://github.com/doctrine/dbal/pull/469
          Hide
          Odiel Leon added a comment - - edited

          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;
          	}
          
          }
          
          Show
          Odiel Leon added a comment - - edited 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; } }
          Hide
          Steve Müller added a comment -

          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.

          Show
          Steve Müller added a comment - 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.
          Hide
          Odiel Leon added a comment -

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

          Show
          Odiel Leon added a comment - Ok, that sounds good, thanks for the explanation, would you be able to point me out a good ORM version compatible with it?
          Hide
          Steve Müller added a comment -

          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.

          Show
          Steve Müller added a comment - 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.

            People

            • Assignee:
              Benjamin Eberlei
              Reporter:
              Odiel Leon
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: