Write To Database - Update; Insert if New - Not Working

I'm having trouble updating an existing record in a database using the standard output tool and I'm not sure what the error is. A primary key is defined on the table and I can insert a record using this approach so I know it isn't permissions

Let's say the initial record in the database looks like the following

AddressID (primary key)	City	State	Zip
12345	[null]	NY	54321

I prepare my data to write and have it like this (to update the City)

AddressID (primary key)	City	State	Zip
12345	ThisTown	NY	54321

I'm using a the Update; Insert if New option and getting an error 'Duplicate Key violates unique constraint 'pk_addressID' key (addressID) = (12345) already exists

This is using a postgresql connection that I've used before and can't figure out whats wrong (connection string blanked out for this image)

Alteryx Error 1.png

Alteryx Error 2.png

Output

Best Practices

Database Connection

Accepted answers

regena

Thanks both - the issue was actually related to the schema I was using. Very strange & different than how I'm using it in my IDE.

Either way I'm running a PreSQL "SET search_path TO [desired_schema];

Then I removed the schema from the output [desired_schema].tablename -> tablename and it worked !

All comments

Sebastiaandb

Hi @regena ,

When updating a record in a database we normally close our old records by using some extra DATA PK fields, like this:

1- The new record gets inserted (with same business PK but different date PK)

2- Old record gets closed as the date gets stamped by datetimenow() with the same business key as the new inserted record.

In our case we don't use Update; insert if new option. What i believe could be the problem with your case is that it somehow wants to insert your altered record in the database since its throwing you that unique constraint (happens when the key is already there). Firs thing you could try is to set that option to "Update; error on update failure; i hope by that, you force it to update the field instead of trying to insert it.

Greetings,

Seb

regena

I can give that a try but I dont' understand why its not trying the Update function. if I was writing in SQL it would be something like

update table
set City = "ThisTown"
where AddressID = 12345

Is there no way to do this?

AddressID (primary key)	City	State	Zip
12345	ThisTown	NY	54321

Sebastiaandb

@regena ,

Should work. Maybe the following link helps?:

Solved: Output Data Tool - update, insert if new - Alteryx Community

SoccerTil

I agree with @Sebastiaandb , it should work. Be aware that Alteryx is deciding whether to do the insert or update, not the database. Assure that the field types in the database and Alteryx workflow match for the primary key field/attribute as this would cause Alteryx to assume it is a new record while the database will autoconvert the column data type.

regena

Thanks both - the issue was actually related to the schema I was using. Very strange & different than how I'm using it in my IDE.

Either way I'm running a PreSQL "SET search_path TO [desired_schema];

Then I removed the schema from the output [desired_schema].tablename -> tablename and it worked !

SoccerTil

glad @regena you found your problem and solution. MySQL does some of the same strange default processing that needs to be worked around.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703