Netezza Create Table Command and Examples

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

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

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:

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:

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:

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

Tags: , , ,