Skip to main content

Data Types and Constraints in PostgreSQL

Data Types and Constraints in PostgreSQL

PostgreSQL offers a rich set of data types and constraints to ensure data integrity and enforce business rules. Understanding and utilizing these features is essential for effective database design and management.


Understanding PostgreSQL Data Types

PostgreSQL supports various data types for different kinds of data:

  • Numeric Types: INTEGER, FLOAT, NUMERIC, etc.
  • Character Types: VARCHAR, CHAR, TEXT
  • Date/Time Types: DATE, TIMESTAMP, INTERVAL
  • Boolean Type: BOOLEAN
  • Geometric Types: POINT, LINE, CIRCLE
  • Network Address Types: CIDR, INET, MACADDR
  • Binary Data Types: BYTEA
  • UUID Type: UUID
  • JSON Types: JSON, JSONB
Data Type Description
INTEGER Stores whole numbers. Example: INTEGER
FLOAT Stores floating-point numbers. Example: FLOAT
CHAR(n) Stores fixed-length character strings. Example: CHAR(10)
VARCHAR(n) Stores variable-length character strings up to n characters. Example: VARCHAR(50)
TEXT Stores variable-length character strings with no specific limit. Example: TEXT
DATE Stores dates (year, month, day). Example: DATE
TIMESTAMP Stores date and time. Example: TIMESTAMP
BOOLEAN Stores TRUE or FALSE values. Example: BOOLEAN
BYTEA Stores binary data. Example: BYTEA
ARRAY Stores an array of values of a specific type. Example: INTEGER[]
JSON Stores JSON data. Example: JSON
JSONB Stores JSON data in a binary format for faster processing. Example: JSONB

Defining Constraints

Constraints enforce rules at the database level. Common constraints include:

  • Primary Key: Ensures each row is unique and not null. Example:
    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      username VARCHAR(50)
    );
  • Foreign Key: Enforces a link between columns in two tables. Example:
    CREATE TABLE orders (
      order_id SERIAL PRIMARY KEY,
      user_id INTEGER REFERENCES users(id)
    );
  • Unique: Ensures all values in a column are unique. Example:
    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(100) UNIQUE
    );
  • Not Null: Ensures a column cannot have a null value. Example:
    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      username VARCHAR(50) NOT NULL
    );

Using Enum and Array Data Types

PostgreSQL supports ENUM and ARRAY data types for storing specific sets of values or arrays of values:

  • Enum Type: Example:
    CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
    CREATE TABLE person (
      name VARCHAR(50),
      current_mood mood
    );
  • Array Type: Example:
    CREATE TABLE teams (
      name VARCHAR(50),
      members VARCHAR(50)[]
    );

Working with JSON and JSONB Data Types

PostgreSQL provides robust support for JSON data with the JSON and JSONB data types:

  • JSON Type: Stores JSON data as text, preserving the order of keys.
  • JSONB Type: Stores JSON data in a binary format, allowing for faster processing and indexing.

Example of using JSONB:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 999, "in_stock": true}');
SELECT data->>'name' AS name FROM products WHERE data->>'price' = '999';

Conclusion

Understanding PostgreSQL data types and constraints is crucial for designing efficient and reliable databases. By leveraging these features, you can ensure data integrity and take full advantage of PostgreSQL's powerful capabilities.

Comments