English

PostgreSQL Tutorial

Advanced PostgreSQL

PostgreSQL Interfaces

PostgreSQL - Data Types

Hello there, future database wizards! ? Welcome to our exciting journey into the world of PostgreSQL data types. As your friendly neighborhood computer science teacher, I'm thrilled to guide you through this fascinating topic. Don't worry if you're new to programming – we'll start from the very basics and work our way up. So, grab your virtual wand (keyboard), and let's dive in!

PostgreSQL - Data Types

Numeric Types

Ah, numbers – the building blocks of computing! PostgreSQL offers a variety of numeric types to suit different needs. Let's explore them with some fun examples.

Integer Types

PostgreSQL provides four types of integers:

Type Storage Size Range
smallint 2 bytes -32768 to +32767
integer 4 bytes -2147483648 to +2147483647
bigint 8 bytes -9223372036854775808 to +9223372036854775807
serial 4 bytes 1 to 2147483647

Let's create a table to store information about our favorite books:

CREATE TABLE favorite_books (
    id serial PRIMARY KEY,
    title VARCHAR(100),
    publication_year smallint,
    pages integer,
    isbn bigint
);

In this example:

  • id uses serial, which automatically increments for each new entry.
  • publication_year uses smallint because books aren't that old (sorry, ancient scrolls!).
  • pages uses integer for book lengths.
  • isbn uses bigint for those long ISBN numbers.

Floating-Point Types

For when you need decimal precision:

Type Description
real 6 decimal digits precision
double precision 15 decimal digits precision

Let's add some more details to our book table:

ALTER TABLE favorite_books
ADD COLUMN rating real,
ADD COLUMN price double precision;

Now we can store book ratings (e.g., 4.5 stars) and precise prices.

Arbitrary Precision Numbers

For when you need extreme precision:

CREATE TABLE scientific_constants (
    name VARCHAR(50),
    value numeric(1000, 900)
);

INSERT INTO scientific_constants (name, value)
VALUES ('Pi', 3.14159265358979323846264338327950288419716939937510);

The numeric(1000, 900) allows for up to 1000 total digits with 900 after the decimal point. Perfect for those pi enthusiasts out there!

Monetary Types

Money makes the world go round, and PostgreSQL has just the type for it:

CREATE TABLE product_prices (
    product_name VARCHAR(50),
    price money
);

INSERT INTO product_prices (product_name, price)
VALUES ('Fancy Pen', '$9.99');

The money type automatically handles currency symbols and comma separators.

Character Types

Text is everywhere in databases. PostgreSQL offers three main character types:

Type Description
character(n) Fixed-length, blank padded
varchar(n) Variable-length with limit
text Variable unlimited length

Let's use these in our book database:

ALTER TABLE favorite_books
ADD COLUMN author character(50),
ADD COLUMN description varchar(500),
ADD COLUMN full_text text;
  • author is fixed at 50 characters (hope that's enough for "J.K. Rowling"!).
  • description can vary but won't exceed 500 characters.
  • full_text can store the entire book if we want (but let's not, okay?).

Binary Data Types

Sometimes, you need to store raw binary data. Enter bytea:

ALTER TABLE favorite_books
ADD COLUMN cover_image bytea;

-- Inserting an image (pretend this is actual binary data)
UPDATE favorite_books
SET cover_image = '\x89504E470D0A1A0A'
WHERE id = 1;

This allows us to store book cover images directly in the database.

Date/Time Types

Time is of the essence, and PostgreSQL has it covered:

Type Description
date Date (no time of day)
time Time of day (no date)
timestamp Both date and time
interval Time interval

Let's track when we read our favorite books:

ALTER TABLE favorite_books
ADD COLUMN purchase_date date,
ADD COLUMN reading_start_time time,
ADD COLUMN last_read timestamp,
ADD COLUMN reading_duration interval;

UPDATE favorite_books
SET purchase_date = '2023-04-01',
    reading_start_time = '20:30:00',
    last_read = '2023-04-15 22:15:30',
    reading_duration = '2 hours 30 minutes'
WHERE id = 1;

Now we can analyze our reading habits! ?⏱️

Boolean Type

True or false, 1 or 0, yes or no – sometimes life is binary:

ALTER TABLE favorite_books
ADD COLUMN is_favorite boolean;

UPDATE favorite_books
SET is_favorite = true
WHERE rating > 4.5;

Simple and effective!

Enumerated Type

When you have a fixed set of options, enums are your friend:

CREATE TYPE book_genre AS ENUM ('Fiction', 'Non-Fiction', 'Science', 'History');

ALTER TABLE favorite_books
ADD COLUMN genre book_genre;

UPDATE favorite_books
SET genre = 'Fiction'
WHERE id = 1;

No more typos in genre names!

Geometric Types

For all you shape enthusiasts out there:

CREATE TABLE geometric_objects (
    id serial PRIMARY KEY,
    name VARCHAR(50),
    location point,
    area box
);

INSERT INTO geometric_objects (name, location, area)
VALUES ('My House', '(10,20)', '((0,0),(100,100))');

Perfect for mapping out your Dungeons & Dragons campaign!

Network Address Type

In our interconnected world, storing network information is crucial:

CREATE TABLE network_devices (
    id serial PRIMARY KEY,
    name VARCHAR(50),
    ip inet,
    mac macaddr
);

INSERT INTO network_devices (name, ip, mac)
VALUES ('My Router', '192.168.1.1', '08:00:2b:01:02:03');

Now you can keep track of all those devices on your network!

Bit String Type

Sometimes, you just need to work with raw bits:

CREATE TABLE permissions (
    user_id integer,
    access_flags bit(8)
);

INSERT INTO permissions (user_id, access_flags)
VALUES (1, B'10110000');

Each bit can represent a different permission. How's that for efficient storage?

Text Search Type

PostgreSQL has built-in support for full-text search:

ALTER TABLE favorite_books
ADD COLUMN search_vector tsvector;

UPDATE favorite_books
SET search_vector = to_tsvector('english', title || ' ' || description);

CREATE INDEX textsearch_idx ON favorite_books USING GIN (search_vector);

Now you can perform lightning-fast searches on your book collection!

UUID Type

Universally Unique Identifiers are great for distributed systems:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE distributed_data (
    id uuid DEFAULT uuid_generate_v4(),
    data TEXT
);

INSERT INTO distributed_data (data) VALUES ('Some distributed data');

No more worrying about ID collisions across different servers!

XML Type

For when you need to store structured XML data:

CREATE TABLE xml_documents (
    id serial PRIMARY KEY,
    doc xml
);

INSERT INTO xml_documents (doc)
VALUES (
    '<book>
        <title>PostgreSQL for Beginners</title>
        <author>Your Favorite Teacher</author>
    </book>'
);

XML in a database? Why not!

JSON Type

In the age of web services, JSON is king:

ALTER TABLE favorite_books
ADD COLUMN metadata jsonb;

UPDATE favorite_books
SET metadata = '{"tags": ["educational", "fun"], "reviews": [{"user": "Alice", "rating": 5}, {"user": "Bob", "rating": 4}]}'
WHERE id = 1;

The jsonb type allows for efficient storage and querying of JSON data.

Array Type

Sometimes, you need to store multiple values in one column:

ALTER TABLE favorite_books
ADD COLUMN keywords text[];

UPDATE favorite_books
SET keywords = ARRAY['database', 'programming', 'awesome']
WHERE id = 1;

Arrays allow you to store lists of values without creating separate tables.

Composite Types

Create your own custom types by combining existing ones:

CREATE TYPE address AS (
    street VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50)
);

ALTER TABLE favorite_books
ADD COLUMN publisher_address address;

UPDATE favorite_books
SET publisher_address = ROW('123 Book St', 'Libraryville', 'Bookland')
WHERE id = 1;

Composite types help you organize related data more logically.

Range Types

When you need to represent a range of values:

CREATE TABLE hotel_bookings (
    id serial PRIMARY KEY,
    room_number integer,
    stay daterange
);

INSERT INTO hotel_bookings (room_number, stay)
VALUES (101, '[2023-07-01, 2023-07-07)');

The daterange type makes it easy to check for overlapping bookings!

Object Identifier Types

These types are used internally by PostgreSQL:

CREATE TABLE table_info (
    table_name VARCHAR(50),
    oid oid
);

INSERT INTO table_info (table_name, oid)
SELECT 'favorite_books', 'favorite_books'::regclass::oid;

While you might not use these often, they're crucial for PostgreSQL's internal operations.

Pseudo-Types

These aren't real types, but they're used in function declarations:

CREATE FUNCTION multiply_any(anyelement, anyelement)
RETURNS anyelement AS $$
    SELECT $1 * $2;
$$ LANGUAGE SQL;

SELECT multiply_any(5, 3);  -- Returns 15
SELECT multiply_any(5.5, 2);  -- Returns 11.0

Pseudo-types like anyelement allow for more flexible function definitions.

And there you have it, my eager students! We've explored the vast landscape of PostgreSQL data types. Remember, choosing the right data type is crucial for performance, storage efficiency, and data integrity. It's like picking the right tool for the job – you wouldn't use a sledgehammer to hang a picture, would you?

As you continue your PostgreSQL journey, you'll discover even more nuances and tricks with these data types. Keep experimenting, stay curious, and most importantly, have fun with your databases! Who knows, you might just become the next database wizard in town. ?‍♂️?

Now, go forth and create amazing databases! And remember, in the world of PostgreSQL, every data type has its place – just like how every book has its perfect spot on a library shelf. Happy coding! ??

Credits: Image by storyset

Previous Tutorial:
PostgreSQL - Syntax
Next Tutorial:
PostgreSQL - Create Database