Netezza Create Table Command and Examples

Use the Netezza CREATE TABLE command to create a new, initially empty table in the current database. The CREATE TABLE command automatically creates a data type that represents the tuple type (structure type) corresponding to one row of the table.

Netezza Create Table Command Syntax

CREATE [ TEMPORARY | TEMP ] TABLE <table>
( <col> <type> [<col_constraint>][,<col> <type> [<col_constraint>]…]
[<table_constraint>[,<table_constraint>… ] )
[ DISTRIBUTE ON { RANDOM | [HASH] (<col>[,<col>…]) } ]
[ ORGANIZE ON { (<col>) | NONE } ]
[ ROW SECURITY ] 

Read:

• Importance of right Distribution key in Netezza 
• IBM Netezza CREATE TABLE IF NOT EXISTS Syntax and Examples  
• Generate Netezza Table DDL using nz_ddl_table
• IBM Netezza DROP TABLE IF EXISTS Syntax and Examples
• Netezza Hash functions
• Netezza Primary Key Constraint and Syntax 

Netezza Create Table Examples

Create table with RANDOM distribution (round-robin) method syntax:

CREATE TABLE <tablename> (col1 int, col2 int, col3 int)
DISTRIBUTE ON RANDOM;

Example:

CREATE TABLE TEST (test_col1 int, test_col2 int, test_col3 char(5))
DISTRIBUTE ON RANDOM;

To create a table without specifying a distribution key:

By default, Netezza distributes on first column if you do not specify DISTRIBUTE ON clause:

CREATE TABLE <tablename> (col1 int, col2 int, col3 int);

Example:

CREATE TABLE TEST (test_col1 int, test_col2 int, test_col3 char(5)):

In above table, Netezza distribute data on col1.

CREATE TABLE AS Syntax

You can create the table from the other existing table, temporary table or external table by using CREATE TABLE AS command:

Read:

• Redistribute Netezza Tables,
• Create Temporary Tables and Examples
• Netezza External Tables and examples

CREATE TABLE <tablename> [ ( <col>[,<col>… ] ) ] AS 
<select_clause> [ DISTRIBUTE ON [HASH] ( <col>[<col>,… ] ) ];

Netezza CREATE TABLE AS Example

CREATE TABLE another_table AS 
SELECT * FROM test;

Netezza will inherit the distribution from base table if you miss to provide DISTRIBUTION ON clause while create. This is one of the best methods to re-distribute the Netezza table rows.

Restrictions on Netezza Column Names

A Netezza tables have following restriction on columns when you create table:

• Netezza table columns cannot be more than 1600 columns.

• Column name cannot be existing data type.

• Netezza table name cannot start with _T or _V as these are system tables. Table name cannot be same as system table or views.

• Netezza Table or view attributes cannot have following names:

  • cmax

  • cmin

  • createxid

  • ctid

  • datasliceid

  • deletexid

  • oid

  • rowid

  • tableoid

  • xmax

  • xmin

Above mentioned column names are reserved column names. Netezza uses these columns internally.