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

ScubaDrew

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!

FiliN

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'

 

 

ScubaDrew

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

 

Drew