Netezza CREATE TABLE IF NOT EXISTS Syntax and Examples

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

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

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

Netezza CREATE TABLE IF NOT EXISTS

The IF NOT EXISTS option is typically used for scripted applications that are running lot of SQL commands, and you want to suppress the ‘table not found’ 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 CREATE TABLE command does not throw an error because it could not create the table.

Read:

Netezza CREATE TABLE IF NOT EXISTS Syntax and Example

Below is the Netezza CREATE TABLE IF NOT EXISTS Syntax:

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

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

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

Read:

Netezza CREATE TABLE IF NOT EXISTS Restrictions

The main restriction of the Netezza CREATE TABLE IF NOT EXISTS option is you cannot use it with the AS SELECT clause. This form of CREATE TABLE command throws an error. Read about Netezza CREATE TABLE syntax and examples

For example:

[nz@netezza ~]$ nzsql -e -db training -f test1.sql 
CREATE TABLE IF NOT EXISTS table_name AS Select * from PATIENT_1;
nzsql:test1.sql:1: ERROR: 'CREATE TABLE IF NOT EXISTS table_name AS Select * from PATIENT_1;'
error ^ found "AS" (at char 39) expecting `'(''
[nz@netezza ~]$

If you want to perform a CREATE TABLE … AS SELECT, consider calling the DROP TABLE IF EXISTS command first to ensure that the target table does not exist in the database and schema before you use the CREATE TABLE … AS SELECT command

Tags: