ACC sqlExec PGS-220000 PostgreSQL error: ERROR: null...

[david--garcia]

Hi Experts,

My SQL insert syntax is resulting in a not null constraint issue.

PGS-220000 PostgreSQL error: ERROR: null value in column "igoal03" violates not-null constraint
DETAIL: Failing row contains (1, 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 3, 0, 100, null, null, null, 0, null, 5, 4, null, null).
WDB-200001 SQL statement 'INSERT INTO SchGoals (iGoal01,iGoal02,iGoal17,iPeople,iInclusion,iHealthWellness) VALUES (1,2,3,100,4,5)' could not be executed.

Here is my JS Code triggering the above

var sqlSyntax = "INSERT INTO SchGoals ("+stringColumns+") VALUES ("+stringValues+")";

sqlExec(sqlSyntax);

the SQL command being executed is literally

INSERT INTO SchGoals (iGoal01,iGoal02,iGoal17,iPeople,iInclusion,iHealthWellness) VALUES (1,2,3,100,4,5)

table structure is as following, I've tried removing the ;

sqlDefault="Null"

to no avail, same error.

​

  <element autopk="true" label="Goals" labelSingular="Goal" name="goals">

    <attribute label="Foreign key of the recipient" name="recipientId" type="int64"/>

    <attribute label="No Poverty" name="goal01" sqlDefault="Null" type="byte"/>
    <attribute label="Zero Hunger" name="goal02" sqlDefault="Null" type="byte"/>
    <attribute label="Good Health" name="goal03" sqlDefault="Null" type="byte"/>
    <attribute label="Quality Education" name="goal04" sqlDefault="Null" type="byte"/>
    <attribute label="Gender Equality" name="goal05" sqlDefault="Null" type="byte"/>
    <attribute label="Clean Water" name="goal06" sqlDefault="Null" type="byte"/>
    <attribute label="Affordable Energy" name="goal07" sqlDefault="Null" type="byte"/>
    <attribute label="Decent Work" name="goal08" sqlDefault="Null" type="byte"/>
    <attribute label="Industry" name="goal09" sqlDefault="Null" type="byte"/>
    <attribute label="Reduced Inequalities" name="goal10" sqlDefault="Null" type="byte"/>
    <attribute label="Sustainable Cities" name="goal11" sqlDefault="Null" type="byte"/>
    <attribute label="Responsible Consumption" name="goal12" sqlDefault="Null" type="byte"/>
    <attribute label="Climate Action" name="goal13" sqlDefault="Null" type="byte"/>
    <attribute label="Life Water" name="goal14" sqlDefault="Null" type="byte"/>
    <attribute label="Life on Land" name="goal15" sqlDefault="Null" type="byte"/>
    <attribute label="Peace" name="goal16" sqlDefault="Null" type="byte"/>
    <attribute label="Partnerships" name="goal17" sqlDefault="Null" type="byte"/>

    <attribute label="Inclusion" name="Inclusion" sqlDefault="Null" type="byte"/>
    <attribute label="Health and Wellness" name="HealthWellness" sqlDefault="Null"
               type="byte"/>
    <attribute label="Environment" name="Environment" sqlDefault="Null" type="byte"/>
    <attribute label="Sustainable Infrastructure" name="SustainableInfrastructure"
               sqlDefault="Null" type="byte"/>
    <attribute label="Responsible Consumption" name="ResponsibleConsumption" sqlDefault="Null"
               type="byte"/>

    <attribute label="People" name="people" sqlDefault="Null" type="byte"/>
    <attribute label="Planet" name="planet" sqlDefault="Null" type="byte"/>

    <attribute default="GetDate()" label="Creation date" name="created" type="datetime"/>
    <attribute label="Modification date" name="lastModified" type="datetime"/>
    
    <!--Link-->
    <element integrity="define" label="Recipient" name="lnkRecipient" revLink="goals"
             target="nms:recipient" type="link">
      <join xpath-dst="@id" xpath-src="@recipientId"/>
    </element>

Also, looking at a cloned schema,

ACC seems to be overiding my custom sqlDefault="Null" attribute, how do I tell the schema to allow nulls?

[_Manoj_Kumar_]

Hello @david--garcia 

Can you try adding default="null" in your attribute declaration?

Like this:

 <attribute label="No Poverty" name="goal01" default="null" type="byte"/>

[Krishnanunni]

Hi @david--garcia ,

As per my understanding, the SqlDefault set in schema is applied only at the time of creation of that column. So if you have created the goal03 column with an sqlDefault, then it cannot be modified by changing in the schema itself. To change the default value you should run the following SQL

ALTER TABLE <tablename>
ALTER COLUMN <columnname> SET DEFAULT '<default value>';

To drop a default value, you may try the drop default
ALTER TABLE <tablename>
ALTER COLUMN <columnname> DROP DEFAULT;

[david--garcia · Accepted solution]

Hi All

Thanks for the reply, it seems the correct approach is as following