We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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