0

I am trying to create a temp table in redshift using

CREATE TABLE tmp_users (LIKE users);

This was working for some time.. but recently I am experiencing failures like below in production.

org.postgresql.util.PSQLException: ERROR: Invalid syntax: Create table sortkey auto with initial sortkeys.

My source tables are not provided with sort keys during CREATE TABLE query execution. So they will have default AUTO SORTKEY according to the redshift documentation. From SVV_TABLE_INFO

  table   | diststyle |  sortkey1         
--------------------------------------
  users   | AUTO(ALL) | AUTO(SORTKEY)

Performing the LIKE query for above source table schema is not causing any issue.

But I see in my production table the sortkey1 is changed to one of the columns of users table AUTO(SORTKEY(c1)) and performing a LIKE on this schema is causing the above exception.

My questions are

  1. Was the sortkey changed by redshift as part of auto performance tuning ?
  2. Why LIKE is failing in this case ? If it's expected.. how do I create a table copy ?

I have referred to the question nvalid syntax: Create table sortkey auto with initial sortkeys, but the suggested solution of dropping the sort key is not possible in my case as the table has millions of records.

projectile
  • 61
  • 7

1 Answers1

1

I had same issue. Reason why create table like fails is that original table has auto sortkey and Redshift have automatically generated some specific sortkey for this table.

Obviously, sortkey can't be dropped due to performance issues.

Solution in this case was to change sortkey and force it to be exactly the same which was automatically generated by Redshift.

Cons: Redshift will no longer automatically change sorkey for this table, you will have to monitor use cases of this table and apply best sortkey manually. Pros: You can use this table metadata to create other tables (create table like)

Edgars T.
  • 947
  • 8
  • 14