6

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.

EDIT:

I do not believe this is a duplicate. The post linked to offers a number of solutions that rely on user defined functions...redshift doesn't support UDF's. They did recently implement a python based UDF system, but my understanding is that its in beta, and we don't know how to implement it anyway.

Thanks for looking, though.

bigbenbt
  • 367
  • 2
  • 14
  • possible duplicate of [PostgreSQL create table if not exists](http://stackoverflow.com/questions/1766046/postgresql-create-table-if-not-exists) – Jakub Kania Mar 27 '15 at 16:24
  • 1
    It doesn't seem like a duplicate to me. They say that the feature was fixed in Postgres 9.1, but we're using redshift, which is branched off of a much earlier version of Postgres. Also, the solution offered in the linked post relies on a UDF, which redshift does not support. – bigbenbt Mar 29 '15 at 01:32

1 Answers1

6

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?

Indeed this combination of CREATE TABLE ... AS SELECT AND IF NOT EXISTS is not possible in Redshift (per documentation). Concerning PostgreSQL, it's possible since version 9.5.

On SO, this is discussed here: PostgreSQL: Create table if not exists AS . The accepted answer provides options that don't require any UDF or procedural code, so they're likely to work with Redshift too.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156