Solved
ACC sqlExec PGS-220000 PostgreSQL error: ERROR: null value in column
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.
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?
