cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
agigliotti
Frequent Visitor

Postgresql update row successful but throwing error

In Power Automate, if I update a row in an Postgresql table using the "update row" action for Postgresql, and I leave a field blank, and the actual value of that field is "null", the update is successful in updating the row and field I want, however, I get the error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints".  I only want to update one field in the record.  The first screenshot shows a portion of the table I am updating in the flow.

 

I have attempted to precede the "update row" with a postgresql "get row action" and defaulting each row in the postgresql update row action dynamically from the get row action.  But then I get the following error (my best guess at this semi-cryptic message is that some timeout was exceeded because this table has well over 100 fields, and some of them are bytea types which means they contain images or pdf's or other such documents and are very large).  Perhaps there is a way to increate the size limitation or timeout?

 

"message": "The gateway is either offline or could not be reached.
inner exception: Gateway on endpoint sb://wabi-us-east2-relay3.servicebus.windows.net/000f1604-0aff-4668-88e9-d05a9be69681 is unreachable
inner exception: The socket connection was aborted. This could be caused by an error processing your message
or a receive timeout being exceeded by the remote host, or an underlying network resource issue.
Local socket timeout was '00:01:00'. TrackingId:dea5456e-f54e-4577-8c2b-7900e7d92e2b,
SystemTracker:wabi-us-east2-relay3.servicebus.windows.net:000f1604-0aff-4668-88e9-d05a9be69681, Timestamp:2021-10-26T14:15:41
inner exception: The write operation failed, see inner exception.
inner exception: The socket connection was aborted. This could be caused by an error processing your message or a receive timeout being exceeded by the remote host, or an underlying network resource issue.
Local socket timeout was '00:01:00'. TrackingId:dea5456e-f54e-4577-8c2b-7900e7d92e2b,
SystemTracker:wabi-us-east2-relay3.servicebus.windows.net:000f1604-0aff-4668-88e9-d05a9be69681, Timestamp:2021-10-26T14:15:41
inner exception: An existing connection was forcibly closed by the remote host
clientRequestId: e11d890d-9fe3-43c0-adc4-4ca9d9e30153",

 

None of the fields are required (except the primary key of course), so any of the fields can be null and I will never know which ones and I have no control over which ones are going to be null, therefore if I go the route of defaulting each row in the postgresql update row action dynamically from the get row action, I must do it for all fields.  But this also presents another problem where I would not want to use this method because it changes all nulls to blanks for text fields, and all nulls to 0001-01-01 for date fields, and that is not ideal.

 

Therefore, is there another option to only update one field and not get the error:

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints".

???

 

Screenshot of "update row" action where I am only updating one field:

agigliotti_1-1635260042576.png

 

 

 

Here is a sample screenshot of what it looks like when I add a "get row" Postgresql action, and default all fields in the "update row" Postgresql action: 

 

agigliotti_3-1635260782218.png

 

 

 

 

 

Here is the screenshot of what the error looks like in the run history when I set all fields in the "update row" action dynamically from the "get row" action that immediately precedes the "update row" action:

 

agigliotti_2-1635260705437.png

 

 

0 REPLIES 0

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,712)