WikiGalaxy

Personalize

PostgreSQL Create Table: Basic Syntax

Syntax Overview:

The CREATE TABLE statement is used to create a new table in PostgreSQL. It defines the table's structure by specifying columns and their data types.


CREATE TABLE table_name (
  column1_name data_type1,
  column2_name data_type2,
  ...
);

Example Explanation:

This basic syntax provides a template for creating tables. Each column is defined by a name and a data type, which specifies what kind of data the column can hold.

PostgreSQL Create Table: Primary Key

Defining a Primary Key:

A primary key uniquely identifies each record in a table. It ensures that no two rows have the same value in the specified column(s).


CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

Example Explanation:

In this example, the employee_id column is designated as the primary key, using the SERIAL data type to auto-increment the values.

PostgreSQL Create Table: Foreign Key

Adding a Foreign Key Constraint:

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. This relationship enforces referential integrity.


CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  order_date DATE
);

Example Explanation:

Here, customer_id is a foreign key referencing the customer_id column in the customers table, establishing a link between the orders and customers tables.

PostgreSQL Create Table: Unique Constraint

Enforcing Unique Values:

A unique constraint ensures that all values in a column are different. It is useful for columns that should not allow duplicate entries.


CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE
);

Example Explanation:

In this table, both the username and email columns are constrained to hold unique values, preventing duplicates.

PostgreSQL Create Table: Check Constraint

Using Check Constraints:

Check constraints ensure that all values in a column satisfy a specific condition. It is a way to enforce domain integrity.


CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name VARCHAR(100),
  price NUMERIC CHECK (price > 0)
);

Example Explanation:

The price column has a check constraint ensuring that only positive values are allowed for product prices.

PostgreSQL Create Table: Default Value

Setting Default Values:

Default values are automatically assigned to a column if no value is specified during an insert operation.


CREATE TABLE accounts (
  account_id SERIAL PRIMARY KEY,
  account_type VARCHAR(50) DEFAULT 'standard',
  balance NUMERIC DEFAULT 0
);

Example Explanation:

Here, the account_type and balance columns have default values of 'standard' and 0, respectively, if no values are provided upon insertion.

PostgreSQL Create Table: Not Null Constraint

Ensuring Non-Null Values:

The NOT NULL constraint ensures that a column cannot have a NULL value. It is important for fields that must always contain data.


CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL
);

Example Explanation:

In this table, both first_name and last_name columns are required to have non-null values.

PostgreSQL Create Table: Composite Primary Key

Defining Composite Keys:

A composite primary key is a primary key consisting of multiple columns. It is used when a single column is not sufficient to uniquely identify a record.


CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  enrollment_date DATE,
  PRIMARY KEY (student_id, course_id)
);

Example Explanation:

In this example, the combination of student_id and course_id forms the composite primary key, ensuring unique enrollment records.

PostgreSQL Create Table: Inheritance

Using Table Inheritance:

Table inheritance allows a table to inherit columns from one or more parent tables, facilitating shared structure and behavior.


CREATE TABLE people (
  person_id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE students (
  grade_level INT
) INHERITS (people);

Example Explanation:

The students table inherits from the people table, gaining access to its columns while adding the grade_level column.

PostgreSQL Create Table: Temporary Tables

Creating Temporary Tables:

Temporary tables exist for the duration of a database session. They are useful for storing intermediate results or data that does not need to persist.


CREATE TEMPORARY TABLE temp_data (
  id SERIAL PRIMARY KEY,
  value TEXT
);

Example Explanation:

In this example, a temporary table temp_data is created, which will be automatically dropped at the end of the session.