I’m in the process of creating data tables to be populated by our load system. This will be updated periodically, so using a MERGE statement makes sense. After creating the table and attempting to merge I receive the following error:

ERROR 4115:  No super projection found for table table_name

But isn’t a super projection created by default when a table is created? Checking the documentation confirms that yes a super projection should be created. So why am I getting this error?

This comes down to one of Vertica’s counter-intuitive optimizations. For this table I didn’t describe the super projection so I am relying on Vertica to create one for me (I may revisit the projections once I’ve made my proof of concept). So the information can be derived and the projection will be created if I INSERT into the table.

In fact, doing that resolves the issue. The call to INSERT creates the projection and the problem resolves itself. It would seem that Vertica does not think to create the projection at MERGE time despite the fact the INSERTs and SELECTs will work independently of one another.

EDIT: Yes, this does have the desired effect, but the preferred work around is to add an encoding type to any column. Even if you explicitly set it to auto this does the trick. This is slightly less convoluted, but I don’t see why this step should be needed at all.