Help Center/ GaussDB(DWS)/ Troubleshooting/ Database Use/ DROP TABLE IF EXISTS Syntax Errors
Updated on 2022-07-04 GMT+08:00

DROP TABLE IF EXISTS Syntax Errors

Symptom

The syntax of a DROP TABLE IF EXISTS statement is incorrect and deletes the wrong table.

Possible Causes

The operations performed by DROP TABLE IF EXISTS are as follows:

  1. Check whether a table exists on the current CN.
  2. If it does, deliver the DROP command to other CNs and DNs. If it does not, no operations are required.

A misunderstanding is as follows:

  1. Deliver DROP TABLE IF EXISTS to all CNs and DNs.
  2. Each CN or DN drops the table (if it exists).

Solution

If some table definitions exist in only part of the CNs/DNs, do not use the DROP TABLE IF EXISTS statement.

Perform the following operations instead:

  1. Use the CREATE TABLE IF NOT EXISTS statement to complete the table definition on all the CNs and DNs.
  2. If the table is no longer necessary, use the DROP TABLE statement on the CN to delete the table definition on all CNs and DNs.