Fixing duplicate records in a table that is used as a dimension in a prejoined projection

I have a table that had duplicate records mistakenly imported into it.

 

I am trying a number of ways to correct this. I've created a new temporary table using:

 

CREATE TABLE new_table AS SELECT DISTINCT * FROM users;

 

I then tried to truncate users.

 

I am getting the following error:

 

[Vertica][VJDBC](4882) ROLLBACK: Table "users" is used as a dimension in a prejoined projection

 

What do I need to do to remove the duplicate records from the users table?

 

Thank you!

Comments

  • I guess you've got a projection based on you table and that is why you need to drop projection first.

     

    Try to find it like this

    select * from projections p where p.anchor_table_name = 'USERS'

     

     

  • Thanks FiliN, that query is very very helpful. I found the related projections and will try removing them. 

     

    Drew

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file