RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.w3resource.com/PostgreSQL/snippets/postgresql-column-types.php below:

Website Navigation

PostgreSQL Column Types: A Complete Guide for Developers

PostgreSQL Column Types: A Complete Guide for DevelopersLast update on December 28 2024 13:04:37 (UTC/GMT +8 hours)

PostgreSQL Column Types: A Comprehensive Guide

PostgreSQL offers a wide variety of column data types, making it one of the most versatile relational database systems. Choosing the right data type for your column ensures efficient data storage, retrieval, and manipulation.

PostgreSQL provides built-in support for different types of data, ranging from primitive types like integers and strings to advanced types like arrays, JSON, and geometric data. Understanding these types is key to designing robust and efficient database schemas.

Common PostgreSQL Column Types

1. Numeric Types

Numeric types store numbers and are used for arithmetic operations.

Input Data

Type Description Example Value smallint 2-byte integer 32767 integer 4-byte integer (default) 2147483647 bigint 8-byte integer 9223372036854775807 decimal Arbitrary precision 12345.6789 numeric Same as decimal 100000.99 real 4-byte floating-point number 3.14159 double precision 8-byte float 2.7182818284

Example: Numeric Column

Code:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY, -- Auto-increment integer
    salary NUMERIC(10, 2)           -- Precision: 10, Scale: 2
);

2. Character Types

These are used to store textual data.

Type Description Example Value char(n) Fixed-length 'ABC ' varchar(n) Variable-length 'Hello' text Unlimited variable-length 'Long string of text'

Example: Character Column

Code:

CREATE TABLE users (
    username VARCHAR(50), -- Up to 50 characters
    bio TEXT              -- No length limit
);

3. Date/Time Types

Date and time types store temporal data.

Type Description Example Value date Date (year, month, day) '2024-01-01' time Time (hour, minute, second) '14:30:00' timestamp Date and time without zone '2024-01-01 14:30:00' timestamptz With timezone info '2024-01-01 14:30:00+01'

Example: Date/Time Column

Code:

CREATE TABLE meetings (
    meeting_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4. Boolean Type

The boolean type stores logical values (TRUE, FALSE, NULL).

Example: Boolean Column

Code:

CREATE TABLE features (
    feature_name TEXT,
    is_enabled BOOLEAN DEFAULT FALSE
);

5. Array Types

PostgreSQL supports arrays of any column type.

Example: Array Column

Code:

CREATE TABLE students (
    name VARCHAR(50),
    grades INTEGER[]
);

Query:

Code:


INSERT INTO students (name, grades) VALUES ('Alice', ARRAY[85, 90, 78]);

6. JSON/JSONB Types

Store structured data in JSON format.

Example: JSON Column

Code:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_details JSONB
);

Query:

Code:


INSERT INTO orders (order_details) VALUES ('{"product": "Laptop", "price": 1200}');

7. UUID Type

Stores universally unique identifiers.

Example: UUID Column

Code:

CREATE TABLE devices (
    device_id UUID DEFAULT gen_random_uuid()
);

Explanation:

Each data type in PostgreSQL serves a specific purpose:

Numeric types are ideal for calculations.
Character types store textual data efficiently.
Date/Time types help in managing schedules or timestamps.
Arrays and JSON are perfect for handling structured data without complex relationships.
UUIDs provide globally unique identifiers.

Choosing the right column type improves performance and ensures data integrity.

All PostgreSQL Questions, Answers, and Code Snippets Collection.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4