Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

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

Avatar

Avatar
Springboard
Level 6
David__Garcia
Level 6

Likes

106 likes

Total Posts

192 posts

Correct Reply

44 solutions
Top badges earned
Springboard
Establish
Validate 10
Coach
Contributor
View profile

Avatar
Springboard
Level 6
David__Garcia
Level 6

Likes

106 likes

Total Posts

192 posts

Correct Reply

44 solutions
Top badges earned
Springboard
Establish
Validate 10
Coach
Contributor
View profile
David__Garcia
Level 6

02-07-2021

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

 

PostgreSQL sql sqlExec

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Springboard
Level 6
David__Garcia
Level 6

Likes

106 likes

Total Posts

192 posts

Correct Reply

44 solutions
Top badges earned
Springboard
Establish
Validate 10
Coach
Contributor
View profile

Avatar
Springboard
Level 6
David__Garcia
Level 6

Likes

106 likes

Total Posts

192 posts

Correct Reply

44 solutions
Top badges earned
Springboard
Establish
Validate 10
Coach
Contributor
View profile
David__Garcia
Level 6

05-07-2021

Hi All

 

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

 

david_garcia1_2-1625484187608.png

 

Answers (2)

Answers (2)

Avatar

Avatar
Affirm 3
Level 2
Krishnanunni
Level 2

Likes

5 likes

Total Posts

21 posts

Correct Reply

2 solutions
Top badges earned
Affirm 3
Boost 5
Contributor
Shape 1
Boost 3
View profile

Avatar
Affirm 3
Level 2
Krishnanunni
Level 2

Likes

5 likes

Total Posts

21 posts

Correct Reply

2 solutions
Top badges earned
Affirm 3
Boost 5
Contributor
Shape 1
Boost 3
View profile
Krishnanunni
Level 2

04-07-2021

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

Avatar
Affirm 100
MVP
_Manoj_Kumar
MVP

Likes

115 likes

Total Posts

327 posts

Correct Reply

100 solutions
Top badges earned
Affirm 100
Validate 1
Contributor
Ignite 1
Shape 1
View profile

Avatar
Affirm 100
MVP
_Manoj_Kumar
MVP

Likes

115 likes

Total Posts

327 posts

Correct Reply

100 solutions
Top badges earned
Affirm 100
Validate 1
Contributor
Ignite 1
Shape 1
View profile
_Manoj_Kumar
MVP

03-07-2021

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