[DBAL-1291] In PostgreSqlSchemaManager::_getPortableTableColumnDefinition Regex for nextval/sequence does not match Created: 03/Sep/15  Updated: 03/Sep/15

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.5.1
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Jason B Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql

PostgreSQL 9.2.13


I ran into a schema that was showing the default value for a nextval as:

  "default" => "nextval(('tablename_id_seq'::text)::regclass)"
           Column           |       Type       |                     Modifiers
 id                         | bigint           | default nextval(('tablename_id_seq'::text)::regclass)

There seems to be an extra set of parentheses. I'm not sure how or why they're showing the extra set of parentheses. As far as I can tell the table column was created with BIGSERIAL. This table/schema could have originally been created in an older version of PostgreSQL.

I am suggesting we loosen up the regex to detect sequences/autoincrement


        if (preg_match("/^nextval\('(.*)'(::.*)?\)$/", $tableColumn['default'], $matches)) {


        if (preg_match("/^nextval\({1,2}'(.*)'(::.*)?\)$/", $tableColumn['default'], $matches)) {

This will match 1 or 2 open parentheses.

[DBAL-879] Sequence default value [PGSQL] Created: 29/Apr/14  Updated: 26/Oct/14

Status: Open
Project: Doctrine DBAL
Component/s: Drivers, Schema Managers
Affects Version/s: 2.4.2
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Mohammad Niknam Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: autoincrement, postgresql, sequence

PostgreSQL 9.3.4


I'm using dbal to generate schmea from database via Schema-Manager. The problem is that my primary field 'id' have default value of 'nextval('test1_id_seq'::regclass)' but when I retrive columns using Doctrine\DBAL\Schema\AbstractSchemaManager::listTableDetails() or Doctrine\DBAL\Schema\Table::getColumns() , default value of the column 'id' is null.
In Doctrine\DBAL\Schema\PostgreSqlSchemaManager::_getPortableTableColumnDefinition() method at line 292 default value replaced with null, I don't know why but I guess It's because Driver compatibility.
Also Doctrine\DBAL\Schema\Sequence has no method to retrieve that table.
So I don't have the default value (pointing at sequence) and I can't find out what Sequence is linked to this table either.

Comment by Steve Müller [ 26/Oct/14 ]

Can you please provide a code example of how you create table + sequence and retrieve it?

[DBAL-397] PostgreSQL - getDateTimeTzFormatString() Created: 12/Dec/12  Updated: 17/Dec/12  Resolved: 16/Dec/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Phill Pafford Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: datetime, dql, format, postgresql

PostgreSQL Database



  • vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php

The function:


only returns one datetime format:

     * {@inheritDoc}
    public function getDateTimeTzFormatString()
        //return 'Y-m-d H:i:sO'; // original format
        return 'Y-m-d H:i:s.uO'; // format also needed

Here is the error I get using the original format:

Could not convert database value "2012-12-07 16:01:52.580789-05" to Doctrine Type datetimetz. Expected format: Y-m-d H:i:s.O

If I use this format: 'Y-m-d H:i:s.uO' it works.

Comment by Phill Pafford [ 12/Dec/12 ]

adding html tags for code to display correctly

Comment by Benjamin Eberlei [ 16/Dec/12 ]

DateTimeTz is created as 'TIMESTAMP(0) WITH TIME ZONE' on DBAL. If you have 'TIMESTAMP(6) WITH TIME ZONE' instead, then you have to create your own datatye.

Comment by Phill Pafford [ 17/Dec/12 ]

Why would this be a new data type? I understand how to fix the problem with creating my own data type to handle the formatting issue and would even suggest just overriding the data type with

        Type::overrideType('datetimetz', 'Doctrine\DBAL\Types\VarDateTimeType');
    Type::overrideType('datetime', 'Doctrine\DBAL\Types\VarDateTimeType');

But it's not a new data type, it's a formatting issue.

Generated at Wed Oct 07 04:45:03 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.