How to create a table in PostgreSQL

CREATE TABLE Syntax:

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

Data Types:

Text types:

Data type	Description
CHAR(size)	Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size)	Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT	Holds a string with a maximum length of 255 characters
TEXT	Holds a string with a maximum length of 65,535 characters
BLOB	For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT	Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB	For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT	Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB	For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.)	Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. Note: The values are sorted in the order you enter them. You enter the possible values in this format: ENUM('X','Y','Z')
SET	Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number types:

Data type	Description
TINYINT(size)	-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size)	-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size)	-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size)	-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size)	-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d)	A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d)	A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d)	A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

Constraints：

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

NOT NULL

CREATE TABLE table_name
(
column_name1 data_type NOT NULL,
column_name2 data_type NULL,
column_name3 data_type,
....
)

UNIQUE

CREATE TABLE table_name
(Method 1: 
column_name1 data_type UNIQUE,
Method 2: 
column_name2 data_type CONSTRAINT unique_constraint_name UNIQUE,
column_name3 data_type,
....
Method 3:
UNIQUE ([column_name]s...)
 Method 4:
CONSTRAINT [unique_constraint_name] UNIQUE ([column_name]s...)
)

Add a UNIQUE constraint to a PostgreSQL table.

Method 1:

>> CREATE UNIQUE INDEX [constraint_name] 

      ON [table_name] ([column_name1], [column_name2], ..., [column_nameN]);

Method 2:

>> ALTER TABLE [table_name] 

      AND CONSTRAINT [constraint_name]

      UNIQUE ([column_name1], [column_name2], ..., [column_nameN]);

To drop a UNIQUE constraint.

>> ALTER TABLE [table_name]

      DROP CONSTRAINT [constraint_name];

PRIMARY KEY

To create a PRIMARY KEY on the "column_name1".

 
CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY,
column_name2 data_type,
column_name3 data_type,
....
)

or, 
 
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
PRIMARY KEY(column_name1)
)

To create a PRIMARY KEY using the "column_name1" and "column_name2" .

 
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
PRIMARY KEY(column_name1, column_name2)
)

To allow naming of a PRIMARY KEY constraint.

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
CONSTRAINT [pk_contraint_name] PRIMARY KEY([column_name]s...)
)

Add a PRIMARY KEY constraint to a PostgreSQL table.

Method 1:
>> ALTER TABLE [table_name] 
     ADD PRIMARY KEY ([column_name]s...);

Method 2:
>> ALTER TABLE [table_name]
     ADD CONSTRAINT [pk_contraint_name] PRIMARY KEY ([column_name]s...);

To drop a PRIMARY KEY constraint.

>> ALTER table [table_name]
      DROP CONSTRAINT [pk_constraint_name];

FOREIGN KEY

To create a FOREIGN KEY on the "column_name1".

 
CREATE TABLE table_nameA
(
column_nameA1 data_type PRIMARY KEY,
column_nameA2 data_type,
column_nameA3 data_type,
....
) 

CREATE TABLE table_nameB
(
column_nameB1 data_type PRIMARY KEY,
column_nameB2 data_type REFERENCES table_nameA (column_nameA1),
column_nameB3 data_type,
....
)

or,


CREATE TABLE table_nameB
(
column_nameB1 data_type PRIMARY KEY,
column_nameB2 data_type,
column_nameB3 data_type,
....
FOREIGN KEY(column_nameB2) REFERENCES table_nameA (column_nameA1)
)
  

To create a FOREIGN KEY using more than one "column_name"

 
CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY,
column_name2 data_type,
column_name3 data_type,
....
FOREIGN KEY(column_name2, column_name3) 
REFERENCES [other_table](column_C1, column_C2)
)

To allow naming of a FOREIGN KEY constraint.

CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY,
column_name2 data_type,
column_name3 data_type,
....
CONSTRAINT [fk_contraint_name] FOREIGN KEY ([column_name]s...)
)

Add a FOREIGN KEY constraint to a PostgreSQL table.

Method 1:
>> ALTER TABLE [table_name] 
     ADD FOREIGN KEY ([column_name]s...);

Method 2:
>> ALTER TABLE [table_name]
     ADD CONSTRAINT [fk_contraint_name] FOREIGN KEY ([column_name]s...);

To drop a FOREIGN KEY constraint.

>> ALTER table [table_name]
      DROP CONSTRAINT [fk_constraint_name];

CHECK

CREATE TABLE table_name
(
column_name1 data_type,
Method 1:
column_name2 data_type CHECK ( column_name2 <operator> [value]),
Method 2:
column_name3 data_type CONSTRAINT new_column_name CHECK ( column_name2 <operator> [value]),
Method 3:
CHECK ( column_nameM <operator> [value]),
Method 4:
CONSTRAINT check_constraint_name CHECK ( column_nameN <operator> [value]),
....
)

DEFAULT

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type DEFAULT value,
....
)