Introduction to PostgreSQL (Coding Style)

This blog aims to assist you in understanding the basic concepts of PostgreSQL with complete coding as query language.

What is PostgreSQL?

• PostgreSQL (post-gress-Q-L) is an open-source relational database management system (DBMS) developed by a worldwide team of volunteers.

---

Key Features of PostgreSQL

• Complex SQL queries
• SQL Sub-selects
• Foreign keys
• Trigger
• Views
• Transactions
• Multiversion concurrency control (MVCC)
• Streaming Replication (as of 9.0)
• Hot Standby (as of 9.0)

---

PostgreSQL SQL commands

Note: Make sure you have Postgres installed on your system to proceed to this tutorial.

PostgreSQL - CONSTRAINTS

NOT NULL Constraint

• A column can hold NULL values by default, but a NOT NULL constraint must be defined to disallow NULL values for that column, ensuring data integrity.

Example:

CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

UNIQUE Constraint

• The UNIQUE constraint ensures that a specific column in a table does not contain duplicate values, maintaining data integrity.

Example:

CREATE TABLE COMPANY3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);

PRIMARY KEY Constraint

• The PRIMARY KEY constraint uniquely identifies records in a database table and ensures each key value is unique, with no NULL values.

• It's essential for database design, enabling relationships between tables by serving as foreign keys in related tables.

• While most databases enforce non-nullable primary keys, SQLite allows NULL values for primary keys due to a historic coding oversight, unlike other databases.

Example:

CREATE TABLE COMPANY4(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

FOREIGN KEY Constraint

• A foreign key constraint ensures that the values in a column or columns in one table must match values in a referenced row of another table, maintaining referential integrity between related tables. Foreign keys are also known as referencing keys as they establish connections outside the table where they're defined.

Example:

CREATE TABLE COMPANY6(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CHECK Constraint

• The CHECK Constraint allows specifying conditions that must be met for values entered into a record; if the condition is false, the record is rejected and not added to the table, ensuring data integrity.

Example:

CREATE TABLE COMPANY5(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);

EXCLUSION Constraint

• Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

Example:

CREATE TABLE COMPANY7(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT,
AGE INT ,
ADDRESS CHAR(50),
SALARY REAL,
EXCLUDE USING gist
(NAME WITH =,
AGE WITH <>)
);

Dropping Constraints

• To remove a constraint you need to know its name. If the name is known, it is easy to drop.

Example:

ALTER TABLE table_name DROP CONSTRAINT some_name;

---

I hope this blog has helped you understand the basic concepts of PostgreSQL with complete coding as a query language.

Check out a summary of Part-2.