Expand my Community achievements bar.

SOLVED

ACC sqlExec PGS-220000 PostgreSQL error: ERROR: null value in column

Avatar

Community Advisor
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?
 
david_garcia1_0-1625246588661.png

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi All

 

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

 

david_garcia1_2-1625484187608.png

 

View solution in original post

3 Replies

Avatar

Community Advisor

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"/>

 

 


     Manoj
     Find me on LinkedIn

Avatar

Level 4

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;

 

 

Avatar

Correct answer by
Community Advisor

Hi All

 

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

 

david_garcia1_2-1625484187608.png