Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Output to Database ... Error Relation already exists

MatthewYoung
5 - Atom

I am trying to output results of a workflow to a database and I keep getting the below error ( the ... lists all the field names)

 

 

Error: Output Data (269): Error creating table "geagp_sccost2.s0_engndb_cms_exl_monthly_actuals_r": ERROR: relation "s0_engndb_cms_exl_monthly_actuals_r" already exists;
Error while executing the query
CREATE TABLE "geagp_sccost2"."s0_engndb_cms_exl_monthly_actuals_r" ("op_contract_part" text,"op_value_stream" text,....)

 

I'm connecting using the ODBC Connection and the User Name does have updater access to the schema.  

 

Does anyone know what this error is trying to say?  

 

11 REPLIES 11
asilva
7 - Meteor

I think it might be throwing you an error because the table you are attempting to output too already exists. 

 

Output.JPG

 

Make sure to adjust the highlighted piece of your output to not "Create New Table", if your table exists in your database already. You can change it to "Delete and Append" or "Append" depending on how you are attempting to update the table.

 

"Delete and Append" = Drop all data and put the data you are feeding it to the table, keeping the same table structure

"Append" = Keep all the data, just add the new data to in the table with the rest of the rows.

 "Overwrite Table (Drop)" = Drop entire table structure and data from database and re-create from scratch

 

If you are using the In-DB tools you have the same options as the output tool.

 

-Tony Silva

MarqueeCrew
20 - Arcturus
20 - Arcturus

I have OVERWRITE TABLE (DROP) selected.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
premvihari
5 - Atom

i too had the same issue even after select Overwrite table (Drop)..any idea how to fix? 

Matt_Curtis
9 - Comet

Anyone here ever solve the issue?  We're currently having the same issue -- Overwrite is selected but it's returning the error that "Cannot Create Table, relation already exists".

lparrett
5 - Atom

Hello All,

 

I was getting this error in one of my workflows too and after many hours of scouring forums I discovered that I had left a space before the schema name in the 'Table Name' textbox. When I removed this, the error disappeared.

 

This may not be the same issue for other users, but I thought I would mention just in case it helps anyone else.

 

Happy Alteryxing!

Kevigan
5 - Atom

The solution marked is not a global solution. I still get the same issue. I have marked the output option as "Overwrtie Table (drop)" but Alteryx thinks that I am trying to create a new table. 

Will Alteryx be fixing this bug? I am unable to schedule any jobs on the server, which ultimately make the server useless to us.

Mogul1
6 - Meteoroid

I was having the same issue when overwriting an existing table on SQL Server using the Data Stream In tool.  When I removed the [square brackets] around the schema in the 'Table Name' window, the issue was resolved.

ThibautCasier
5 - Atom

I've had the same issue. In my case the problem was caused by a database view that was referencing to this table. As soon as I dropped the view I had no more problems overwriting the table. Offcourse you're losing the view so as a solution I dropped the view in a pre SQL statement and created it again in the post SQL statement.

 

 

JDong
8 - Asteroid

@ThibautCasier @MatthewYoung @Mogul1 @Kevigan @lparrett 

 

Any update to this question please ?

 

I had a running bulk loader that overwrite(drop) into postgres database (new release v2020.3)

 

Worked find on a weekly schedule but this week it failed with below issue. 

 

Error: Output Data (405): Error creating table "": ERROR: relation "" already exists;
Error while executing the query
CREATE TABLE "".""."" ("))

 

Gallop_1-1611339117181.png

 

 

Gallop_0-1611338975566.png

 

Any updates will be really helpful. I tried recreating the output step too but no success...Thanks

Labels