Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: Postgres empty string interpreted as null value

  1. Default Postgres empty string interpreted as null value

    In our table output statement the empty string value is interpreted as null value.

    The transformation is very simple:

    Table Input from one postgresql to table output of an identical postgresql database.

    However this field can't be null and has a default of ''.
    isdisabled | character varying(100) | not null default ''::character varying


    But on table output I get
    Code:
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Error inserting/updating row
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : ERROR: null value in column "isdisabled" violates not-null constraint
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : 
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : 
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:376)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:78)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.steps.tableoutput.TableOutput.run(TableOutput.java:630)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Error inserting/updating row
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : ERROR: null value in column "isdisabled" violates not-null constraint
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : 
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.core.database.Database.insertRow(Database.java:1314)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:222)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     ... 3 more
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "isdisabled" violates not-null constraint
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:305)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.core.database.Database.insertRow(Database.java:1262)
    2008/10/15 15:56:35 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     ... 4 more
    2008/10/15 15:56:35 - Table output.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2008/10/15 15:56:35 - Spoon - The transformation has finished!!
    2008/10/15 15:56:35 - test1 - Transformation detected one or more steps with errors.
    2008/10/15 15:56:35 - test1 - Transformation is killing the other steps!
    Why is that? It seems so simple. I read some places that Oracle does treat '' as a null value, does postgresql as well?

    What is the solution?

    K<o>

  2. #2
    Join Date
    Jul 2007
    Posts
    1,013

  3. Default Thansk but how do I solve my problem?

    Thanks tdidomenico,
    I appreciate the quick reply.

    Any idea how I solve my problem though? I get an empty string from a database how do I keep it an empty string?

    How do I ever feed an empty string into a database?

    Thanks

    K<o>

  4. #4
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    I made a very quick test, with a table input where I just "SELECT '' AS empty FROM table", and a table output.

    When I query the Postgres DB with "SELECT * FROM table2 WHERE empty IS NULL" I get nothing, and I get all the rows when I use "WHERE empty = ''". It would seem then like Table Output will not convert your empties to nulls. Maybe you could convert nulls to empties exactly before the output.

    I'm using PostgreSQL 8.3, by the way.

  5. Default Hmm, this does not work for me

    tdidomenico,
    Interesting, I actually found this source code in ValueMeta, that is checking explicitly for empty strings and then the get converted to null fields.

    Code:
        public void setValue(PreparedStatement ps, ValueMetaInterface v, Object object, int pos) throws KettleDatabaseException
        {
            String debug = "";
    
            try
            {
                switch(v.getType())
                {
    ...
                case ValueMetaInterface.TYPE_STRING : 
                    debug="String";
                    if (v.getLength()<DatabaseMeta.CLOB_LENGTH)
                    {
                        if (!v.isNull(object)) 
                        {
                            ps.setString(pos, v.getString(object));
                        }
                        else 
                        {
                            ps.setNull(pos, java.sql.Types.VARCHAR);
                        }
                    }
                    else
                    {
                        if (!v.isNull(object))
                        {
    ...
    Code:
        /**
         * Determine if an object is null.
         * This is the case if data==null or if it's an empty string.
         * @param data the object to test
         * @return true if the object is considered null.
         */
        public boolean isNull(Object data)
        {
            try{
                if (data==null) return true;
    //            if (isString()) {
    //                if (isStorageNormal() && ((String)data).length()==0) return true;
    //                if (isStorageBinaryString()) {
    //                        if ( ((byte[])data).length==0 ) return true;
    //                }
    //            }
                return false;
            }
            catch(ClassCastException e)
            {
                throw new RuntimeException("Unable to verify if ["+toString()+"] is null or not because of an error:"+e.toString(), e);
            }
        }
    Fixing this isNull() check, does make the issue go away. I wished that would be an option on the table/field and not just a fixed rule. I'm not to keen making such customizations in the guts of a tool I'm using.

    I'm using "psql (PostgreSQL) 8.2.4" but that should not make a difference, as kettle explicitly turns '' in to null;

    Still curious why yours works, can you give me a \d table2? I suspect your empty field is not restricted to NOT NULL, or is it?

    K<o>

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    I very much doubt we're going to switch the behaviour of "" and NULLs

    And changing the code is of course allowed, but at your own risk

    Regards,
    Sven
    Last edited by sboden; 10-16-2008 at 02:49 PM.

  7. #7
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    Quote Originally Posted by PlanBForOpenOffice View Post

    Still curious why yours works, can you give me a \d table2? I suspect your empty field is not restricted to NOT NULL, or is it?

    K<o>
    It wasn't, but changing that didn't seem to change the behaviour.

    I'm attaching my stupid transformation and DB script in case you want to test it yourself, because I'm probably doing something wrong

    Cheers!
    Last edited by tdidomenico; 10-16-2008 at 04:06 PM.

  8. #8
    Join Date
    May 2006
    Posts
    4,882

    Default

    It's one of those things which is partly database specific, and "partially" enforced by PDI

  9. Default Same error

    tdidomenico,
    thanks for sharing. I tried your version , just simplified the input to "SELECT '' AS empty limit 10". So I did get an error right away when using the batch update mode. Wit no clear message, but disabling batch update revealed the error below. So no change on my database.

    Either the version makes a difference, or some parameter is different on your database.

    Code:
    2008/10/16 15:49:44 - Spoon - Started the transformation execution.
    2008/10/16 15:49:44 - aaaaaaaaaaaa - Dispatching started for transformation [aaaaaaaaaaaa]
    2008/10/16 15:49:44 - aaaaaaaaaaaa - This transformation can be replayed with replay date: 2008/10/16 15:49:44
    2008/10/16 15:49:44 - Table output.0 - Connected to database [BDS @ pikatchu] (commit=100)
    2008/10/16 15:49:44 - Table input.0 - Finished reading query, closing connection.
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Because of an error, this step can't continue: 
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : org.pentaho.di.core.exception.KettleException: 
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Error inserting row into table [tempe] with values: []
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : 
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Error inserting/updating row
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : ERROR: null value in column "empty" violates not-null constraint
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : 
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : 
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:376)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:78)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.steps.tableoutput.TableOutput.run(TableOutput.java:630)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Error inserting/updating row
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : ERROR: null value in column "empty" violates not-null constraint
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : 
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.core.database.Database.insertRow(Database.java:1314)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:222)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     ... 3 more
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "empty" violates not-null constraint
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:305)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     at org.pentaho.di.core.database.Database.insertRow(Database.java:1262)
    2008/10/16 15:49:44 - Table output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :     ... 4 more
    2008/10/16 15:49:44 - Table input.0 - Finished processing (I=1, O=0, R=0, W=1, U=1, E=0)
    2008/10/16 15:49:44 - Table output.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2008/10/16 15:49:44 - Spoon - The transformation has finished!!
    2008/10/16 15:49:44 - aaaaaaaaaaaa - Transformation detected one or more steps with errors.
    2008/10/16 15:49:44 - aaaaaaaaaaaa - Transformation is killing the other steps!
    Either way I can't change the target database, so I have to live with the programmatic fix.

    Thanks for all your efforts to find the root cause. I really appreciate it.

    K<o>

  10. Default Would you consider compatible fixes/changes?

    Sven,
    I was just about to report a bug issue to see if we can "improve" this behavior.

    Looking at the source above, to me it looks like it is always enforced, so it seems to be decided somehow, somewhere that this is a good idea.

    Do you think it is worth trying to get this optional with some configuration or database specific? I understand the compatibility issues very well, so it should be optional.

    I'd argue that it should be possible to simply transfer records of identical tables from a source to a target db. Would you agree that this simple exercise is vital?

    I'd also argue that the behavior is likely not what Oracle does, as it is changing empty string into null and not treating them the same.

    Kind regards,

    K<o>

  11. #11
    Join Date
    May 2006
    Posts
    4,882

    Default

    I don't see it going optional. Personally I would give you the same result as one of the Oracle techies would give you if you advised to make the null/'' behaviour in Oracle optional

    And for oracle null and '' are actually the same.

    Regards,
    Sven

  12. Default

    Sven,
    they are not [treated] the same in Kettle, they are converted from one into the other, that is a major difference.

    I can not enter data into a table, because of this conversion and a NOT Null restriction is not a border case, or is it?

    The only way to avoid this would be to change an empty string into something with a single space, but how do I know the underlying application will take this change lightly?

    To me it smells like a bug, if it is by design or a coding bug :-)

    K<o>

  13. #13
    Join Date
    May 2006
    Posts
    4,882

    Default

    Semantics... fact is that changing it now would break a lot of stuff.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.