topic Re: Truncate <...> cascade in Talend Studio

Truncate <...> cascade

Pasha23 — Sat, 16 Nov 2024 00:44:20 GMT

Hello,

first I will describe what I want to achieve.

We are migrating from MySQL db to PostgreSQL db. I need to truncate PostgreSQL table(s) with all sequences and than add new data from MySQL and restore the sequences. If I'm running a classic job:

tDBInput(MySQL)-->tFileOutputDelimited-->tMap-->tDBOutput-->tLogRow

Where in output I select Truncate and Inser

than this Error appears:

Exception in component tDBOutput_1 (UsersCSV)

org.postgresql.util.PSQLException: ERROR: cannot truncate a table referenced in a foreign key constraint

Detail: Table "user_role" references "users".

Hint: Truncate table "user_role" at the same time, or use TRUNCATE ... CASCADE.

How can I implement Truncante <...> Cascade for this or any other solution to solve this.

Thanks.

Re: Truncate <...> cascade

PaulyWally — Thu, 28 Jan 2021 19:54:51 GMT

Can you truncate "user_role"? I assume you can since you are truncating "user". Since there's a referential constraint, any records left in "user_role" would likely be pointless.

I am completely speculating based on the little info you gave me! Please ensure truncating "user_role" would not cause any adverse affects!

So the answer is likely to truncate "user_role" first.

Use a tDBRow prior to the subjob you outlined. Add a truncate query to the tDBRow:

TRUNCATE user_role...

https://www.postgresql.org/docs/9.1/sql-truncate.html

Your flow would look like this:

tDBRow --> On Subjob OK --> tDBInput(MySQL) --> tFileOutputDelimited --> tMap --> tDBOutput --> tLogRow

Re: Truncate <...> cascade

Pasha23 — Fri, 29 Jan 2021 08:43:56 GMT

PaulyWally,

thank you.

I'll try it...