How to Create View in PostgreSQL DB with Output Data PostSQL tool?

Hello,

I am trying to use the Post Create SQL Statement to create a VIEW in one schema, A, from another schema, B.

CREATE OR REPLACE VIEW "A"."newVIEW" AS

SELECT * FROM "B"."oldTABLE";

I've tried several different methods, but I continuously get errors saying: ERROR: relation "oldTABLE" already exists. I also get an error saying: relation "oldTABLE" does not exist.

I've tried: changing all references to serial, all primary keys to integers to match up types, dropped tables, and recreated them. I still get this error.

Any help is appreciated, and if more info is needed, I'm glad to provide it.

Datasets

Developer

Developer Tools

Error Message

Output

Server

Help

In Database

Database Connection

In-Database

Accepted answers

All comments

apathetichell

is B on the same DB as A? can you pull in B.oldTABLE in input data/connect in-db?

kanemku

@apathetichell Yes, both schemas A and B are in the same DB, and I'm able to successfully pull from B.oldTABLE in the input data.

apathetichell

hmmm... I guess my next question would be - do you have create view permissions?

https://stackoverflow.com/questions/27403852/postgres-table-privileges-do-not-allow-user-to-create-select-view

and just to be clear - if you are using in-db you can set up a join from a table in A to a table in B without erroring?

kanemku

I'm using the postgres superuser account.

I just tried using the Connect In-DB tool to join two tables from each schema, and it worked successfully with the Join In-DB tool.

simonaubert_bd

Hello,

You can't create natively a view with in database Alteryx. However, I have developed a macro that retrieves your table create code (with a dynamic output tool) and transform it into a view (with formula) and reinject it in database (with pre-sql statement of a classic output tool).

Here something like that : (without the dynamic output and formula thing) . Please notice you will need a dummy table because you can't pass free sql

You can vote for this idea to support view creation in Alteryx :
https://community.alteryx.com/t5/Alteryx-Designer-Ideas/In-DB-Create-View/idi-p/157886#comments

Best regards,

Simon

simonaubert_bd

@kanemkuHello. Here the said macro.

Macro_create_view.yxmc

gaia_fnt

Hi simonaubert_db, can you please explain a bit more in details how this macro works?

Thank you

Gaia

simonaubert_bd

Hello @gaia_fnt

Quickly :
-batch macro with three parameters : in memory connection (aka..), name of the view, select query to create the view

-I use the pre-sql statement of an input box since I can write any sql in it.

Best regards,

Simon

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703