vendredi 27 mars 2015

In Redshift, how do you combine CTAS with the "if not exists" clause?

I'm having some trouble getting this table creation query to work, and I'm wondering if I'm running in to a limitation in redshift.


Here's what I want to do: I have data that I need to move between schema, and I need to create the destination tables for the data on the fly, but only if they don't already exist.


Here are queries that I know work:



create table if not exists temp_table (id bigint);


This creates a table if it doesn't already exist, and it works just fine.



create table temp_2 as select * from temp_table where 1=2;


So that creates an empty table with the same structure as the previous one. That also works fine.


However, when I do this query:



create table if not exists temp_2 as select * from temp_table where 1=2;


Redshift chokes and says there is an error near as (for the record, I did try removing "as" and then it says there is an error near select)


I couldn't find anything in the redshift docs, and at this point I'm just guessing as to how to fix this. Is this something I just can't do in redshift?


I should mention that I absolutely can separate out the queries that selectively create the table and populate it with data, and I probably will end up doing that. I was mostly just curious if anyone could tell me what's wrong with that query.





Aucun commentaire:

Enregistrer un commentaire