Netezza DROP TABLE IF EXISTS Syntax and Examples

This is the one of the feature that most of people are waiting since long time. Finally, IBM has added Netezza DROP TABLE IF EXISTS feature in its latest release i.e. Netezza 7.2.X.

We will discuss this feature with an example in this article.

Netezza DROP TABLE IF EXISTS

The Netezza DROP TABLE IF EXISTS option is typically used for scripted applications that are running lot of SQL commands, and you want to suppress the ‘table already exists error message so that it does not halt the scripted application. The application will simply continue with remaining Netezza nzsql SQL command in the script. If a table with the specified name exists in the current database and schema, the DROP TABLE IF EXISTS command drops the tables and subsequent CREATE TABLE command is executed.

Read:

• Netezza Server Basic Commands
• Netezza CREATE TABLE IF NOT EXISTS Syntax and Examples
• Commonly used basic Netezza Linux Commands
• Netezza CREATE TABLE syntax and examples

Netezza DROP TABLE IF EXISTS Syntax and Example

Below is the Netezza DROP TABLE IF EXISTS Syntax:

DROP TABLE <table-name> IF EXISTS;

CREATE TABLE IF NOT EXISTS <table>
(
col1 datatype,
col2 datatype,
...
) DISTRIBUTE ON RANDOM | (col);

Below is the example on how to use Netezza DROP TABLE IF EXISTS.

[nz@netezza ~]$ nzsql -e -db training -f test1.sql 
drop table table_name if exists;
DROP TABLE
create table IF NOT EXISTS table_name
( 
field_name1 varchar(10),
field_name2 varchar(10)
);
CREATE TABLE
[nz@netezza ~]$