__HTML_TAG_93_ __HTML_TAG_94_
Postgre SQL es un sistema de base de datos de código abierto con más de 30 años de desarrollo activo. Conocido por su fiabilidad, su robustez y rendimiento, soporta tanto la búsqueda SQL como JSON.
# Update package index
sudo apt update
# Install PostgreSQL
sudo apt install postgresql postgresql-contrib
# Install specific version
sudo apt install postgresql-14 postgresql-contrib-14
# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql
# Check status
sudo systemctl status postgresql
# Switch to postgres user
sudo -i -u postgres
# Access PostgreSQL prompt
psql
# Install PostgreSQL repository
sudo dnf install postgresql-server postgresql-contrib
# Initialize database
sudo postgresql-setup --initdb
# Start and enable PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql
# Switch to postgres user
sudo -i -u postgres
psql
# Using Homebrew
brew install postgresql
# Start PostgreSQL
brew services start postgresql
# Create database
createdb mydatabase
# Connect to PostgreSQL
psql mydatabase
# Using PostgreSQL.app
# Download from https://postgresapp.com/
# Download installer from https://www.postgresql.org/download/windows/
# Run the installer and follow the setup wizard
# Connect using psql
psql -U postgres -h localhost
# Or use pgAdmin GUI tool
# Pull PostgreSQL image
docker pull postgres:15
# Run PostgreSQL container
docker run --name postgres-container \
-e POSTGRES_PASSWORD=mypassword \
-e POSTGRES_DB=mydatabase \
-p 5432:5432 \
-d postgres:15
# Connect to PostgreSQL in container
docker exec -it postgres-container psql -U postgres -d mydatabase
# Run with persistent data
docker run --name postgres-container \
-e POSTGRES_PASSWORD=mypassword \
-e POSTGRES_DB=mydatabase \
-p 5432:5432 \
-v postgres-data:/var/lib/postgresql/data \
-d postgres:15
-- Connect to PostgreSQL
psql -U username -d database_name
-- Connect to remote server
psql -h hostname -p 5432 -U username -d database_name
-- Connect with SSL
psql "host=hostname port=5432 dbname=database_name user=username sslmode=require"
-- Connect and execute command
psql -U username -d database_name -c "SELECT version();"
-- Connect from file
psql -U username -d database_name -f script.sql
-- Show PostgreSQL version
SELECT version();
-- Show current user
SELECT current_user;
-- Show current database
SELECT current_database();
-- Show current date and time
SELECT now();
-- Show server settings
SHOW ALL;
-- Show specific setting
SHOW shared_buffers;
-- Show active connections
SELECT * FROM pg_stat_activity;
-- Show database size
SELECT pg_size_pretty(pg_database_size(current_database()));
-- List databases
\l
-- Connect to database
\c database_name
-- List tables
\dt
-- List all relations (tables, views, sequences)
\d
-- Describe table
\d table_name
-- List schemas
\dn
-- List users/roles
\du
-- List functions
\df
-- Show table sizes
\dt+
-- Execute system command
\! ls -la
-- Quit psql
\q
-- Help
\?
-- SQL help
\h SELECT
-- Create database
CREATE DATABASE mydatabase;
-- Create database with options
CREATE DATABASE mydatabase
WITH OWNER = myuser
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE = template0;
-- Create database with specific tablespace
CREATE DATABASE mydatabase
WITH TABLESPACE = my_tablespace;
-- Create database if not exists (PostgreSQL 9.1+)
CREATE DATABASE IF NOT EXISTS mydatabase;
-- List all databases
\l
SELECT datname FROM pg_database;
-- Connect to database
\c mydatabase
-- Show current database
SELECT current_database();
-- Rename database
ALTER DATABASE old_name RENAME TO new_name;
-- Change database owner
ALTER DATABASE mydatabase OWNER TO new_owner;
-- Drop database
DROP DATABASE mydatabase;
-- Drop database if exists
DROP DATABASE IF EXISTS mydatabase;
-- Terminate connections to database
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'mydatabase'
AND pid <> pg_backend_pid();
-- Show database size
SELECT
datname as database_name,
pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
-- Show table sizes in current database
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- Show database connections
SELECT
datname,
numbackends,
xact_commit,
xact_rollback,
blks_read,
blks_hit
FROM pg_stat_database;
-- Basic table creation
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Table with various data types
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
category_id INTEGER REFERENCES categories(id),
tags TEXT[],
metadata JSONB,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Table with constraints
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
CONSTRAINT valid_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);
-- Temporary table
CREATE TEMPORARY TABLE temp_data (
id INTEGER,
value TEXT
);
-- Table with inheritance
CREATE TABLE vehicles (
id SERIAL PRIMARY KEY,
make VARCHAR(50),
model VARCHAR(50),
year INTEGER
);
CREATE TABLE cars (
doors INTEGER
) INHERITS (vehicles);
-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Add column with default value
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) DEFAULT '';
-- Modify column type
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150);
-- Set column default
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT NOW();
-- Drop column default
ALTER TABLE users ALTER COLUMN created_at DROP DEFAULT;
-- Rename column
ALTER TABLE users RENAME COLUMN username TO user_name;
-- Drop column
ALTER TABLE users DROP COLUMN phone;
-- Add constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
-- Drop constraint
ALTER TABLE users DROP CONSTRAINT unique_email;
-- Add foreign key
ALTER TABLE posts ADD CONSTRAINT fk_user_id
FOREIGN KEY (user_id) REFERENCES users(id);
-- Rename table
ALTER TABLE old_table_name RENAME TO new_table_name;
-- Change table owner
ALTER TABLE users OWNER TO new_owner;
-- Set table schema
ALTER TABLE users SET SCHEMA new_schema;
-- Describe table structure
\d users
\d+ users -- with additional info
-- Show table columns
SELECT
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_name = 'users';
-- Show table constraints
SELECT
constraint_name,
constraint_type,
column_name
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name = ccu.constraint_name
WHERE tc.table_name = 'users';
-- Show table indexes
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE tablename = 'users';
-- Show foreign keys
SELECT
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_name = 'users';
-- Drop table
DROP TABLE table_name;
-- Drop table if exists
DROP TABLE IF EXISTS table_name;
-- Drop multiple tables
DROP TABLE table1, table2, table3;
-- Drop table cascade (remove dependent objects)
DROP TABLE table_name CASCADE;
-- Truncate table (delete all data, keep structure)
TRUNCATE TABLE table_name;
-- Truncate with cascade
TRUNCATE TABLE table_name CASCADE;
-- Truncate and restart identity
TRUNCATE TABLE table_name RESTART IDENTITY;
-- Integer types
SMALLINT -- 2 bytes, -32,768 to 32,767
INTEGER -- 4 bytes, -2,147,483,648 to 2,147,483,647
BIGINT -- 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
-- Auto-incrementing integers
SMALLSERIAL -- 2 bytes, 1 to 32,767
SERIAL -- 4 bytes, 1 to 2,147,483,647
BIGSERIAL -- 8 bytes, 1 to 9,223,372,036,854,775,807
-- Decimal types
DECIMAL(precision, scale) -- Exact numeric
NUMERIC(precision, scale) -- Exact numeric (same as DECIMAL)
REAL -- 4 bytes, single precision
DOUBLE PRECISION -- 8 bytes, double precision
-- Examples
CREATE TABLE numeric_examples (
id SERIAL PRIMARY KEY,
small_int SMALLINT,
regular_int INTEGER,
big_int BIGINT,
price DECIMAL(10,2),
percentage REAL,
scientific DOUBLE PRECISION
);
-- Character types
CHAR(n) -- Fixed-length character string
VARCHAR(n) -- Variable-length character string
TEXT -- Variable-length character string (unlimited)
-- Examples
CREATE TABLE text_examples (
id SERIAL PRIMARY KEY,
code CHAR(5), -- Fixed 5 characters
name VARCHAR(100), -- Up to 100 characters
description TEXT -- Unlimited length
);
-- Date and time types
DATE -- Date only (YYYY-MM-DD)
TIME -- Time only (HH:MM:SS)
TIME WITH TIME ZONE -- Time with timezone
TIMESTAMP -- Date and time
TIMESTAMP WITH TIME ZONE -- Date and time with timezone
INTERVAL -- Time interval
-- Examples
CREATE TABLE datetime_examples (
id SERIAL PRIMARY KEY,
birth_date DATE,
meeting_time TIME,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
duration INTERVAL
);
-- Insert examples
INSERT INTO datetime_examples (birth_date, meeting_time, duration) VALUES
('1990-05-15', '14:30:00', '2 hours 30 minutes');
-- Boolean type
BOOLEAN -- TRUE, FALSE, or NULL
CREATE TABLE boolean_examples (
id SERIAL PRIMARY KEY,
is_active BOOLEAN DEFAULT TRUE,
is_verified BOOLEAN DEFAULT FALSE
);
-- Array types
INTEGER[] -- Array of integers
TEXT[] -- Array of text
VARCHAR(50)[] -- Array of varchar
CREATE TABLE array_examples (
id SERIAL PRIMARY KEY,
tags TEXT[],
scores INTEGER[],
emails VARCHAR(100)[]
);
-- Insert array data
INSERT INTO array_examples (tags, scores, emails) VALUES
(ARRAY['tag1', 'tag2', 'tag3'], ARRAY[85, 92, 78], ARRAY['email1@example.com', 'email2@example.com']);
-- Alternative syntax
INSERT INTO array_examples (tags, scores) VALUES
('{"tag1", "tag2", "tag3"}', '{85, 92, 78}');
-- Query array data
SELECT * FROM array_examples WHERE 'tag1' = ANY(tags);
SELECT * FROM array_examples WHERE tags @> ARRAY['tag1'];
-- JSON types
JSON -- JSON data (stored as text)
JSONB -- Binary JSON (more efficient)
CREATE TABLE json_examples (
id SERIAL PRIMARY KEY,
data JSON,
metadata JSONB
);
-- Insert JSON data
INSERT INTO json_examples (data, metadata) VALUES
('{"name": "John", "age": 30}', '{"preferences": {"theme": "dark", "language": "en"}}');
-- Query JSON data
SELECT data->>'name' as name FROM json_examples;
SELECT * FROM json_examples WHERE metadata @> '{"preferences": {"theme": "dark"}}';
-- UUID type
UUID -- Universally Unique Identifier
-- Enable uuid extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE uuid_examples (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name VARCHAR(100)
);
-- Geometric types
POINT -- Point on a plane
LINE -- Infinite line
LSEG -- Line segment
BOX -- Rectangular box
PATH -- Geometric path
POLYGON -- Closed geometric path
CIRCLE -- Circle
CREATE TABLE geometric_examples (
id SERIAL PRIMARY KEY,
location POINT,
area BOX,
boundary POLYGON
);
-- Insert geometric data
INSERT INTO geometric_examples (location, area) VALUES
(POINT(1, 2), BOX(POINT(0, 0), POINT(10, 10)));
-- Basic insert
INSERT INTO users (username, email, password)
VALUES ('john_doe', 'john@example.com', 'hashed_password');
-- Insert multiple rows
INSERT INTO users (username, email, password) VALUES
('alice', 'alice@example.com', 'password1'),
('bob', 'bob@example.com', 'password2'),
('charlie', 'charlie@example.com', 'password3');
-- Insert with returning
INSERT INTO users (username, email, password)
VALUES ('dave', 'dave@example.com', 'password4')
RETURNING id, username, created_at;
-- Insert from another table
INSERT INTO users_backup (username, email)
SELECT username, email FROM users WHERE created_at < '2023-01-01';
-- Insert with ON CONFLICT (upsert)
INSERT INTO users (id, username, email)
VALUES (1, 'john_doe', 'newemail@example.com')
ON CONFLICT (id)
DO UPDATE SET email = EXCLUDED.email, updated_at = NOW();
-- Insert with DO NOTHING on conflict
INSERT INTO users (username, email, password)
VALUES ('existing_user', 'existing@example.com', 'password')
ON CONFLICT (username) DO NOTHING;
-- Insert array data
INSERT INTO products (name, tags)
VALUES ('Product 1', ARRAY['electronics', 'gadget', 'new']);
-- Insert JSON data
INSERT INTO user_preferences (user_id, preferences)
VALUES (1, '{"theme": "dark", "notifications": true}');
-- Basic select
SELECT * FROM users;
-- Select specific columns
SELECT username, email FROM users;
-- Select with alias
SELECT username AS user_name, email AS user_email FROM users;
-- Select with WHERE clause
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE username = 'john_doe';
SELECT * FROM users WHERE created_at > '2023-01-01';
-- Select with multiple conditions
SELECT * FROM users WHERE username = 'john_doe' AND email = 'john@example.com';
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email IS NOT NULL;
-- Select with ORDER BY
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, created_at DESC;
-- Select with LIMIT and OFFSET
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users ORDER BY id LIMIT 5;
-- Select with GROUP BY
SELECT COUNT(*) as user_count FROM users;
SELECT DATE(created_at) as date, COUNT(*) as daily_users
FROM users GROUP BY DATE(created_at);
-- Select with HAVING
SELECT DATE(created_at) as date, COUNT(*) as daily_users
FROM users
GROUP BY DATE(created_at)
HAVING COUNT(*) > 5;
-- Select with window functions
SELECT
username,
created_at,
ROW_NUMBER() OVER (ORDER BY created_at) as row_num,
RANK() OVER (ORDER BY created_at) as rank,
LAG(username) OVER (ORDER BY created_at) as prev_user
FROM users;
-- Select with CTE (Common Table Expression)
WITH recent_users AS (
SELECT * FROM users WHERE created_at > '2023-01-01'
)
SELECT COUNT(*) FROM recent_users;
-- Recursive CTE
WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;
-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
-- Update multiple columns
UPDATE users SET
email = 'newemail@example.com',
updated_at = NOW()
WHERE id = 1;
-- Update with conditions
UPDATE users SET password = 'new_password' WHERE username = 'john_doe';
-- Update multiple rows
UPDATE users SET is_active = FALSE WHERE created_at < '2022-01-01';
-- Update with FROM clause
UPDATE users SET status = 'premium'
FROM subscriptions s
WHERE users.id = s.user_id AND s.plan = 'premium';
-- Update with subquery
UPDATE users SET status = 'premium'
WHERE id IN (SELECT user_id FROM subscriptions WHERE plan = 'premium');
-- Update with CASE
UPDATE users SET
status = CASE
WHEN created_at > '2023-01-01' THEN 'new'
WHEN created_at > '2022-01-01' THEN 'regular'
ELSE 'old'
END;
-- Update with RETURNING
UPDATE users SET email = 'updated@example.com'
WHERE id = 1
RETURNING id, username, email, updated_at;
-- Update array elements
UPDATE products SET tags = array_append(tags, 'featured') WHERE id = 1;
UPDATE products SET tags = array_remove(tags, 'old') WHERE id = 1;
-- Update JSON data
UPDATE user_preferences
SET preferences = preferences || '{"new_setting": true}'
WHERE user_id = 1;
-- Basic delete
DELETE FROM users WHERE id = 1;
-- Delete with conditions
DELETE FROM users WHERE username = 'john_doe';
DELETE FROM users WHERE created_at < '2022-01-01';
-- Delete multiple rows
DELETE FROM users WHERE id IN (1, 2, 3);
-- Delete with USING clause
DELETE FROM users
USING user_profiles p
WHERE users.id = p.user_id AND p.is_deleted = TRUE;
-- Delete with subquery
DELETE FROM users
WHERE id IN (SELECT user_id FROM inactive_users);
-- Delete with RETURNING
DELETE FROM users WHERE id = 1
RETURNING id, username, email;
-- Delete all rows (keep table structure)
DELETE FROM users;
-- Truncate table (faster for deleting all rows)
TRUNCATE TABLE users;
TRUNCATE TABLE users RESTART IDENTITY;
-- Create index on single column
CREATE INDEX idx_users_username ON users(username);
-- Create index on multiple columns
CREATE INDEX idx_users_name_email ON users(username, email);
-- Create unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Create partial index
CREATE INDEX idx_active_users ON users(username) WHERE is_active = TRUE;
-- Create expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Create index with specific method
CREATE INDEX idx_users_username_hash ON users USING HASH (username);
-- Create index concurrently (doesn't block writes)
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);
-- Create GIN index for arrays
CREATE INDEX idx_products_tags ON products USING GIN (tags);
-- Create GiST index for full-text search
CREATE INDEX idx_posts_content ON posts USING GiST (to_tsvector('english', content));
-- Create BRIN index for large tables
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
-- List indexes for table
\d users
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';
-- Show index usage statistics
SELECT
schemaname,
tablename,
indexname,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'users';
-- Show index size
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes
WHERE tablename = 'users';
-- Drop index
DROP INDEX idx_users_username;
-- Drop index concurrently
DROP INDEX CONCURRENTLY idx_users_username;
-- Reindex
REINDEX INDEX idx_users_username;
REINDEX TABLE users;
REINDEX DATABASE mydatabase;
-- Analyze index usage
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE username = 'john_doe';
-- B-tree index (default)
CREATE INDEX idx_btree ON users(username);
-- Hash index (for equality comparisons)
CREATE INDEX idx_hash ON users(id) USING HASH;
-- GIN index (for arrays, JSONB, full-text search)
CREATE INDEX idx_gin_tags ON products USING GIN (tags);
CREATE INDEX idx_gin_jsonb ON products USING GIN (metadata);
-- GiST index (for geometric data, full-text search)
CREATE INDEX idx_gist_location ON locations USING GiST (coordinates);
-- SP-GiST index (for non-balanced data structures)
CREATE INDEX idx_spgist ON table_name USING SPGIST (column_name);
-- BRIN index (for very large tables with natural ordering)
CREATE INDEX idx_brin_timestamp ON large_table USING BRIN (timestamp);
-- Covering index (includes additional columns)
CREATE INDEX idx_covering ON users(username) INCLUDE (email, created_at);
-- Add primary key during table creation
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50)
);
-- Add primary key to existing table
ALTER TABLE users ADD PRIMARY KEY (id);
-- Composite primary key
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);
-- Named primary key constraint
CREATE TABLE users (
id SERIAL,
username VARCHAR(50),
CONSTRAINT pk_users PRIMARY KEY (id)
);
-- Drop primary key
ALTER TABLE users DROP CONSTRAINT users_pkey;
-- Add foreign key during table creation
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
title VARCHAR(200)
);
-- Add foreign key to existing table
ALTER TABLE posts ADD CONSTRAINT fk_posts_user_id
FOREIGN KEY (user_id) REFERENCES users(id);
-- Foreign key with actions
ALTER TABLE posts ADD CONSTRAINT fk_posts_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE;
-- Composite foreign key
ALTER TABLE order_items ADD CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id, customer_id) REFERENCES orders(id, customer_id);
-- Drop foreign key
ALTER TABLE posts DROP CONSTRAINT fk_posts_user_id;
-- Show foreign keys
SELECT
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name,
rc.update_rule,
rc.delete_rule
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints AS rc
ON tc.constraint_name = rc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';
-- Add unique constraint during table creation
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);
-- Add unique constraint to existing table
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);
-- Composite unique constraint
ALTER TABLE users ADD CONSTRAINT uk_users_username_email UNIQUE (username, email);
-- Partial unique constraint
CREATE UNIQUE INDEX uk_active_users_email ON users(email) WHERE is_active = TRUE;
-- Drop unique constraint
ALTER TABLE users DROP CONSTRAINT uk_users_email;
-- Add check constraint during table creation
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2) CHECK (price > 0),
stock_quantity INTEGER CHECK (stock_quantity >= 0)
);
-- Add check constraint to existing table
ALTER TABLE products ADD CONSTRAINT chk_products_price CHECK (price > 0);
-- Complex check constraint
ALTER TABLE users ADD CONSTRAINT chk_users_email
CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
-- Check constraint with multiple conditions
ALTER TABLE orders ADD CONSTRAINT chk_orders_status
CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'));
-- Drop check constraint
ALTER TABLE products DROP CONSTRAINT chk_products_price;
-- Disable/Enable constraint
ALTER TABLE products DISABLE TRIGGER ALL;
ALTER TABLE products ENABLE TRIGGER ALL;
-- Add NOT NULL during table creation
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
-- Add NOT NULL to existing column
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- Remove NOT NULL constraint
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;
-- Add column with NOT NULL and default
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';
-- Exclusion constraint (PostgreSQL specific)
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
room_id INTEGER,
during TSRANGE,
EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);
-- Exclusion constraint with condition
ALTER TABLE reservations ADD CONSTRAINT exclude_overlapping_reservations
EXCLUDE USING GIST (room_id WITH =, during WITH &&) WHERE (status = 'confirmed');
-- ROW_NUMBER, RANK, DENSE_RANK
SELECT
username,
created_at,
ROW_NUMBER() OVER (ORDER BY created_at) as row_num,
RANK() OVER (ORDER BY created_at) as rank,
DENSE_RANK() OVER (ORDER BY created_at) as dense_rank
FROM users;
-- Partition by
SELECT
username,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;
-- LAG and LEAD
SELECT
username,
created_at,
LAG(created_at) OVER (ORDER BY created_at) as prev_created_at,
LEAD(created_at) OVER (ORDER BY created_at) as next_created_at
FROM users;
-- FIRST_VALUE and LAST_VALUE
SELECT
username,
salary,
FIRST_VALUE(salary) OVER (ORDER BY salary DESC) as highest_salary,
LAST_VALUE(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_salary
FROM employees;
-- NTILE (divide into buckets)
SELECT
username,
salary,
NTILE(4) OVER (ORDER BY salary) as salary_quartile
FROM employees;
-- Cumulative sum
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;
-- Basic CTE
WITH recent_users AS (
SELECT * FROM users WHERE created_at > '2023-01-01'
)
SELECT COUNT(*) FROM recent_users;
-- Multiple CTEs
WITH
active_users AS (
SELECT * FROM users WHERE is_active = TRUE
),
user_stats AS (
SELECT
COUNT(*) as total_users,
AVG(EXTRACT(YEAR FROM AGE(birth_date))) as avg_age
FROM active_users
)
SELECT * FROM user_stats;
-- Recursive CTE
WITH RECURSIVE employee_hierarchy AS (
-- Base case
SELECT id, name, manager_id, 1 as level, name as path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case
SELECT
e.id,
e.name,
e.manager_id,
eh.level + 1,
eh.path || ' -> ' || e.name
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
WHERE eh.level < 10 -- Prevent infinite recursion
)
SELECT * FROM employee_hierarchy ORDER BY level, path;
-- CTE for data modification
WITH deleted_users AS (
DELETE FROM users
WHERE last_login < '2022-01-01'
RETURNING *
)
INSERT INTO archived_users SELECT * FROM deleted_users;
-- LATERAL join (correlated subquery in FROM clause)
SELECT
u.username,
recent_posts.title,
recent_posts.created_at
FROM users u
CROSS JOIN LATERAL (
SELECT title, created_at
FROM posts p
WHERE p.user_id = u.id
ORDER BY created_at DESC
LIMIT 3
) recent_posts;
-- LATERAL with function
SELECT
u.username,
user_stats.post_count,
user_stats.avg_post_length
FROM users u
CROSS JOIN LATERAL (
SELECT
COUNT(*) as post_count,
AVG(LENGTH(content)) as avg_post_length
FROM posts p
WHERE p.user_id = u.id
) user_stats;
-- Simple CASE
SELECT
username,
CASE status
WHEN 'active' THEN 'User is active'
WHEN 'inactive' THEN 'User is inactive'
WHEN 'banned' THEN 'User is banned'
ELSE 'Unknown status'
END as status_description
FROM users;
-- Searched CASE
SELECT
username,
salary,
CASE
WHEN salary < 30000 THEN 'Low'
WHEN salary < 60000 THEN 'Medium'
WHEN salary < 100000 THEN 'High'
ELSE 'Very High'
END as salary_category
FROM employees;
-- CASE in aggregate
SELECT
department,
COUNT(*) as total_employees,
COUNT(CASE WHEN salary > 50000 THEN 1 END) as high_earners,
COUNT(CASE WHEN salary <= 50000 THEN 1 END) as low_earners
FROM employees
GROUP BY department;
-- Scalar subquery
SELECT
username,
(SELECT COUNT(*) FROM posts WHERE user_id = users.id) as post_count
FROM users;
-- EXISTS subquery
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);
-- NOT EXISTS subquery
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);
-- IN subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM posts WHERE created_at > '2023-01-01');
-- ANY/ALL subqueries
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'electronics');
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'books');
-- String functions
SELECT
CONCAT('Hello', ' ', 'World'), -- Hello World
LENGTH('Hello World'), -- 11
UPPER('hello world'), -- HELLO WORLD
LOWER('HELLO WORLD'), -- hello world
SUBSTRING('Hello World', 1, 5), -- Hello
LEFT('Hello World', 5), -- Hello
RIGHT('Hello World', 5), -- World
TRIM(' Hello World '), -- Hello World
REPLACE('Hello World', 'World', 'PostgreSQL'), -- Hello PostgreSQL
REVERSE('Hello'), -- olleH
REPEAT('Ha', 3), -- HaHaHa
LPAD('123', 5, '0'), -- 00123
RPAD('123', 5, '0'); -- 12300
-- String search and pattern matching
SELECT
POSITION('World' IN 'Hello World'), -- 7
STRPOS('Hello World', 'World'), -- 7
'Hello World' LIKE 'Hello%', -- true
'Hello World' ILIKE 'hello%', -- true (case insensitive)
'Hello World' ~ '^Hello', -- true (regex)
'Hello World' ~* '^hello'; -- true (case insensitive regex)
-- Numeric functions
SELECT
ABS(-15), -- 15
CEIL(4.3), -- 5
FLOOR(4.7), -- 4
ROUND(4.567, 2), -- 4.57
TRUNC(4.567, 2), -- 4.56
MOD(10, 3), -- 1
POWER(2, 3), -- 8
SQRT(16), -- 4
RANDOM(), -- Random number 0-1
GREATEST(1, 5, 3, 9, 2), -- 9
LEAST(1, 5, 3, 9, 2); -- 1
-- Date and time functions
SELECT
NOW(), -- Current timestamp
CURRENT_DATE, -- Current date
CURRENT_TIME, -- Current time
EXTRACT(YEAR FROM NOW()), -- Current year
EXTRACT(MONTH FROM NOW()), -- Current month
EXTRACT(DAY FROM NOW()), -- Current day
DATE_PART('year', NOW()), -- Current year
AGE('1990-05-15'), -- Age from birth date
DATE_TRUNC('month', NOW()), -- First day of current month
NOW() + INTERVAL '1 day', -- Tomorrow
NOW() - INTERVAL '1 week'; -- Last week
-- Aggregate functions
SELECT
COUNT(*),
COUNT(DISTINCT username),
SUM(salary),
AVG(salary),
MIN(salary),
MAX(salary),
STDDEV(salary),
VARIANCE(salary),
STRING_AGG(username, ', ' ORDER BY username) -- Concatenate strings
FROM employees;
-- Simple function
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;
-- Function with parameters
CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER)
RETURNS TABLE(id INTEGER, username VARCHAR, email VARCHAR) AS $$
BEGIN
RETURN QUERY
SELECT u.id, u.username, u.email
FROM users u
WHERE u.id = user_id;
END;
$$ LANGUAGE plpgsql;
-- Function with default parameters
CREATE OR REPLACE FUNCTION get_users_by_status(
user_status VARCHAR DEFAULT 'active',
limit_count INTEGER DEFAULT 10
)
RETURNS TABLE(id INTEGER, username VARCHAR, status VARCHAR) AS $$
BEGIN
RETURN QUERY
SELECT u.id, u.username, u.status
FROM users u
WHERE u.status = user_status
LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;
-- Function with OUT parameters
CREATE OR REPLACE FUNCTION get_user_stats(
OUT total_users INTEGER,
OUT active_users INTEGER,
OUT inactive_users INTEGER
) AS $$
BEGIN
SELECT COUNT(*) INTO total_users FROM users;
SELECT COUNT(*) INTO active_users FROM users WHERE is_active = TRUE;
SELECT COUNT(*) INTO inactive_users FROM users WHERE is_active = FALSE;
END;
$$ LANGUAGE plpgsql;
-- Function with exception handling
CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
IF b = 0 THEN
RAISE EXCEPTION 'Division by zero';
END IF;
RETURN a / b;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Cannot divide by zero, returning NULL';
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
```_
### Procedimientos almacenados
```sql
-- Simple procedure
CREATE OR REPLACE PROCEDURE update_user_status(
user_id INTEGER,
new_status VARCHAR
) AS $$
BEGIN
UPDATE users SET status = new_status WHERE id = user_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'User with id % not found', user_id;
END IF;
COMMIT;
END;
$$ LANGUAGE plpgsql;
-- Procedure with transaction control
CREATE OR REPLACE PROCEDURE transfer_funds(
from_account INTEGER,
to_account INTEGER,
amount DECIMAL
) AS $$
DECLARE
from_balance DECIMAL;
BEGIN
-- Start transaction
BEGIN
-- Check balance
SELECT balance INTO from_balance
FROM accounts
WHERE id = from_account
FOR UPDATE;
IF from_balance < amount THEN
RAISE EXCEPTION 'Insufficient funds';
END IF;
-- Debit from source account
UPDATE accounts
SET balance = balance - amount
WHERE id = from_account;
-- Credit to destination account
UPDATE accounts
SET balance = balance + amount
WHERE id = to_account;
-- Log transaction
INSERT INTO transactions (from_account, to_account, amount, timestamp)
VALUES (from_account, to_account, amount, NOW());
COMMIT;
RAISE NOTICE 'Transfer completed successfully';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE EXCEPTION 'Transfer failed: %', SQLERRM;
END;
END;
$$ LANGUAGE plpgsql;
-- Call procedures
CALL update_user_status(1, 'inactive');
CALL transfer_funds(100, 200, 500.00);
-- List functions
\df
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public';
-- Show function definition
\df+ function_name
SELECT pg_get_functiondef(oid)
FROM pg_proc
WHERE proname = 'function_name';
-- Drop function
DROP FUNCTION IF EXISTS get_user_count();
-- Drop procedure
DROP PROCEDURE IF EXISTS update_user_status(INTEGER, VARCHAR);
-- Call function
SELECT get_user_count();
SELECT * FROM get_user_by_id(1);
-- Call function with OUT parameters
SELECT * FROM get_user_stats();
-- Create trigger function
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create trigger
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();
-- BEFORE INSERT trigger
CREATE OR REPLACE FUNCTION before_user_insert()
RETURNS TRIGGER AS $$
BEGIN
NEW.created_at = NOW();
NEW.username = LOWER(NEW.username);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_user_insert_trigger
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION before_user_insert();
-- AFTER INSERT trigger
CREATE OR REPLACE FUNCTION after_user_insert()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO user_audit (user_id, action, timestamp)
VALUES (NEW.id, 'INSERT', NOW());
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER after_user_insert_trigger
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION after_user_insert();
-- BEFORE UPDATE trigger with conditions
CREATE OR REPLACE FUNCTION before_user_update()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
-- Check if email changed
IF NEW.email != OLD.email THEN
NEW.email_verified = FALSE;
END IF;
-- Prevent username changes
IF NEW.username != OLD.username THEN
RAISE EXCEPTION 'Username cannot be changed';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_user_update_trigger
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION before_user_update();
-- AFTER UPDATE trigger
CREATE OR REPLACE FUNCTION after_user_update()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO user_audit (user_id, action, old_values, new_values, timestamp)
VALUES (
NEW.id,
'UPDATE',
row_to_json(OLD),
row_to_json(NEW),
NOW()
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER after_user_update_trigger
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION after_user_update();
-- BEFORE DELETE trigger
CREATE OR REPLACE FUNCTION before_user_delete()
RETURNS TRIGGER AS $$
BEGIN
-- Archive user before deletion
INSERT INTO deleted_users (original_id, username, email, deleted_at)
VALUES (OLD.id, OLD.username, OLD.email, NOW());
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_user_delete_trigger
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION before_user_delete();
-- Statement-level trigger
CREATE OR REPLACE FUNCTION log_table_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO table_audit (table_name, operation, row_count, timestamp)
VALUES (TG_TABLE_NAME, TG_OP, TG_NARGS, NOW());
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO table_audit (table_name, operation, row_count, timestamp)
VALUES (TG_TABLE_NAME, TG_OP, TG_NARGS, NOW());
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO table_audit (table_name, operation, row_count, timestamp)
VALUES (TG_TABLE_NAME, TG_OP, TG_NARGS, NOW());
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER log_users_changes
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH STATEMENT
EXECUTE FUNCTION log_table_changes();
-- Conditional trigger
CREATE OR REPLACE FUNCTION update_product_stock()
RETURNS TRIGGER AS $$
DECLARE
current_stock INTEGER;
BEGIN
SELECT stock_quantity INTO current_stock
FROM products
WHERE id = NEW.product_id;
IF current_stock >= NEW.quantity THEN
UPDATE products
SET stock_quantity = stock_quantity - NEW.quantity
WHERE id = NEW.product_id;
ELSE
RAISE EXCEPTION 'Insufficient stock. Available: %, Requested: %',
current_stock, NEW.quantity;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_product_stock_trigger
AFTER INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_product_stock();
-- Trigger with WHEN clause
CREATE TRIGGER update_expensive_products_only
BEFORE UPDATE ON products
FOR EACH ROW
WHEN (NEW.price > 100)
EXECUTE FUNCTION log_expensive_product_changes();
-- List triggers
\dS
SELECT trigger_name, event_manipulation, event_object_table
FROM information_schema.triggers
WHERE trigger_schema = 'public';
-- Show trigger definition
\d+ table_name
-- Disable trigger
ALTER TABLE users DISABLE TRIGGER update_users_updated_at;
-- Enable trigger
ALTER TABLE users ENABLE TRIGGER update_users_updated_at;
-- Disable all triggers on table
ALTER TABLE users DISABLE TRIGGER ALL;
-- Enable all triggers on table
ALTER TABLE users ENABLE TRIGGER ALL;
-- Drop trigger
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
-- Drop trigger function
DROP FUNCTION IF EXISTS update_modified_column();
```_
## Views and Materialized Views
## Creando vistas
```sql
-- Simple view
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE is_active = TRUE;
-- View with JOIN
CREATE VIEW user_posts AS
SELECT
u.username,
u.email,
p.title,
p.content,
p.created_at as post_date
FROM users u
JOIN posts p ON u.id = p.user_id;
-- View with aggregation
CREATE VIEW user_stats AS
SELECT
u.id,
u.username,
COUNT(p.id) as post_count,
MAX(p.created_at) as last_post_date,
AVG(LENGTH(p.content)) as avg_post_length
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;
-- View with window functions
CREATE VIEW user_rankings AS
SELECT
username,
post_count,
RANK() OVER (ORDER BY post_count DESC) as rank,
PERCENT_RANK() OVER (ORDER BY post_count DESC) as percentile
FROM (
SELECT
u.username,
COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.username
) user_post_counts;
-- Recursive view
CREATE RECURSIVE VIEW employee_hierarchy AS
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id;
-- Create materialized view
CREATE MATERIALIZED VIEW user_summary AS
SELECT
DATE_TRUNC('month', created_at) as month,
COUNT(*) as user_count,
COUNT(*) FILTER (WHERE is_active = TRUE) as active_count
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
-- Create materialized view with index
CREATE MATERIALIZED VIEW product_sales_summary AS
SELECT
p.id,
p.name,
SUM(oi.quantity) as total_sold,
SUM(oi.quantity * p.price) as total_revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;
CREATE INDEX idx_product_sales_summary_revenue
ON product_sales_summary(total_revenue DESC);
-- Materialized view with data
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
DATE_TRUNC('month', order_date) as month,
SUM(total_amount) as total_sales,
COUNT(*) as order_count,
AVG(total_amount) as avg_order_value
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY DATE_TRUNC('month', order_date)
WITH DATA;
-- Materialized view without data (populate later)
CREATE MATERIALIZED VIEW large_summary AS
SELECT * FROM very_large_table
WITH NO DATA;
-- List views
\dv
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'public';
-- List materialized views
\dm
SELECT schemaname, matviewname, ispopulated
FROM pg_matviews;
-- Show view definition
\d+ view_name
-- Query views
SELECT * FROM active_users;
SELECT * FROM user_stats WHERE post_count > 5;
-- Update view (if updatable)
UPDATE active_users SET email = 'new@example.com' WHERE id = 1;
-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_summary;
-- Refresh materialized view concurrently (requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales_summary;
-- Populate materialized view
REFRESH MATERIALIZED VIEW large_summary;
-- Drop view
DROP VIEW IF EXISTS active_users;
-- Drop materialized view
DROP MATERIALIZED VIEW IF EXISTS user_summary;
-- Alter view
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, created_at, last_login
FROM users
WHERE is_active = TRUE;
-- Create updatable view
CREATE VIEW user_profiles AS
SELECT id, username, email, first_name, last_name
FROM users
WHERE is_active = TRUE;
-- Insert through view
INSERT INTO user_profiles (username, email, first_name, last_name)
VALUES ('newuser', 'new@example.com', 'New', 'User');
-- Update through view
UPDATE user_profiles
SET email = 'updated@example.com'
WHERE id = 1;
-- Delete through view
DELETE FROM user_profiles WHERE id = 1;
-- View with INSTEAD OF triggers for complex updates
CREATE OR REPLACE FUNCTION update_user_profile()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO users (username, email, first_name, last_name, is_active)
VALUES (NEW.username, NEW.email, NEW.first_name, NEW.last_name, TRUE);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE users SET
username = NEW.username,
email = NEW.email,
first_name = NEW.first_name,
last_name = NEW.last_name
WHERE id = NEW.id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE users SET is_active = FALSE WHERE id = OLD.id;
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER user_profiles_trigger
INSTEAD OF INSERT OR UPDATE OR DELETE ON user_profiles
FOR EACH ROW
EXECUTE FUNCTION update_user_profile();
-- Create user
CREATE USER username WITH PASSWORD 'password';
-- Create user with options
CREATE USER username WITH
PASSWORD 'password'
CREATEDB
CREATEROLE
LOGIN
VALID UNTIL '2024-12-31';
-- Create role (cannot login by default)
CREATE ROLE role_name;
-- Create role with login capability
CREATE ROLE role_name WITH LOGIN PASSWORD 'password';
-- Create role with specific attributes
CREATE ROLE app_role WITH
NOLOGIN
CREATEDB
CREATEROLE
CONNECTION LIMIT 10;
-- Grant database privileges
GRANT CONNECT ON DATABASE mydatabase TO username;
GRANT CREATE ON DATABASE mydatabase TO username;
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO username;
-- Grant schema privileges
GRANT USAGE ON SCHEMA public TO username;
GRANT CREATE ON SCHEMA public TO username;
GRANT ALL ON SCHEMA public TO username;
-- Grant table privileges
GRANT SELECT ON users TO username;
GRANT INSERT, UPDATE, DELETE ON users TO username;
GRANT ALL PRIVILEGES ON users TO username;
-- Grant privileges on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
-- Grant privileges on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO username;
-- Grant sequence privileges
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO username;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO username;
-- Grant function privileges
GRANT EXECUTE ON FUNCTION function_name TO username;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO username;
-- Grant column-level privileges
GRANT SELECT (username, email), UPDATE (email) ON users TO username;
-- Grant role to user
GRANT role_name TO username;
-- Grant role with admin option
GRANT role_name TO username WITH ADMIN OPTION;
-- Revoke role from user
REVOKE role_name FROM username;
-- Set default role
ALTER USER username SET ROLE role_name;
-- Create role hierarchy
CREATE ROLE read_only;
CREATE ROLE read_write;
CREATE ROLE admin;
GRANT read_only TO read_write;
GRANT read_write TO admin;
-- Grant privileges to roles
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
-- Show current user
SELECT current_user;
-- Show current role
SELECT current_role;
-- Show user privileges
\du
SELECT * FROM pg_user;
SELECT * FROM pg_roles;
-- Show table privileges
\dp table_name
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'users';
-- Show database privileges
SELECT datname, datacl FROM pg_database WHERE datname = 'mydatabase';
-- Revoke privileges
REVOKE SELECT ON users FROM username;
REVOKE ALL PRIVILEGES ON users FROM username;
REVOKE CONNECT ON DATABASE mydatabase FROM username;
-- Change user password
ALTER USER username WITH PASSWORD 'new_password';
-- Change user attributes
ALTER USER username WITH CREATEDB;
ALTER USER username WITH NOCREATEDB;
ALTER USER username WITH CREATEROLE;
ALTER USER username WITH NOCREATEROLE;
-- Set connection limit
ALTER USER username WITH CONNECTION LIMIT 5;
-- Set password expiration
ALTER USER username WITH VALID UNTIL '2024-12-31';
-- Lock user account
ALTER USER username WITH NOLOGIN;
-- Unlock user account
ALTER USER username WITH LOGIN;
-- Drop user
DROP USER username;
-- Drop role
DROP ROLE role_name;
-- Enable RLS on table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- Create policy
CREATE POLICY user_policy ON users
FOR ALL
TO application_role
USING (user_id = current_setting('app.current_user_id')::INTEGER);
-- Create policy for SELECT
CREATE POLICY select_own_posts ON posts
FOR SELECT
TO application_role
USING (user_id = current_setting('app.current_user_id')::INTEGER);
-- Create policy for INSERT
CREATE POLICY insert_own_posts ON posts
FOR INSERT
TO application_role
WITH CHECK (user_id = current_setting('app.current_user_id')::INTEGER);
-- Create policy for UPDATE
CREATE POLICY update_own_posts ON posts
FOR UPDATE
TO application_role
USING (user_id = current_setting('app.current_user_id')::INTEGER)
WITH CHECK (user_id = current_setting('app.current_user_id')::INTEGER);
-- Create policy for DELETE
CREATE POLICY delete_own_posts ON posts
FOR DELETE
TO application_role
USING (user_id = current_setting('app.current_user_id')::INTEGER);
-- Show policies
\d+ table_name
SELECT * FROM pg_policies WHERE tablename = 'users';
-- Disable RLS
ALTER TABLE users DISABLE ROW LEVEL SECURITY;
-- Drop policy
DROP POLICY user_policy ON users;
-- Force RLS for table owners
ALTER TABLE users FORCE ROW LEVEL SECURITY;
# Backup single database
pg_dump -U username -h hostname database_name > backup.sql
# Backup with specific options
pg_dump -U username -h hostname \
--verbose \
--clean \
--no-owner \
--no-privileges \
database_name > backup.sql
# Backup in custom format (compressed)
pg_dump -U username -h hostname -Fc database_name > backup.dump
# Backup in directory format
pg_dump -U username -h hostname -Fd database_name -f backup_dir
# Backup specific tables
pg_dump -U username -h hostname -t users -t posts database_name > tables_backup.sql
# Backup specific schema
pg_dump -U username -h hostname -n public database_name > schema_backup.sql
# Backup structure only (no data)
pg_dump -U username -h hostname --schema-only database_name > structure.sql
# Backup data only (no structure)
pg_dump -U username -h hostname --data-only database_name > data.sql
# Backup with parallel jobs
pg_dump -U username -h hostname -Fd -j 4 database_name -f backup_dir
# Exclude specific tables
pg_dump -U username -h hostname --exclude-table=logs database_name > backup.sql
# Backup all databases
pg_dumpall -U username -h hostname > all_databases.sql
# Backup only global objects (roles, tablespaces)
pg_dumpall -U username -h hostname --globals-only > globals.sql
# Restore from SQL file
psql -U username -h hostname -d database_name < backup.sql
# Restore and create database
createdb -U username -h hostname database_name
psql -U username -h hostname -d database_name < backup.sql
# Restore from custom format
pg_restore -U username -h hostname -d database_name backup.dump
# Restore with specific options
pg_restore -U username -h hostname \
--verbose \
--clean \
--no-owner \
--no-privileges \
-d database_name backup.dump
# Restore specific tables
pg_restore -U username -h hostname -d database_name -t users backup.dump
# Restore with parallel jobs
pg_restore -U username -h hostname -d database_name -j 4 backup.dump
# List contents of backup file
pg_restore --list backup.dump
# Restore from directory format
pg_restore -U username -h hostname -d database_name backup_dir
# Restore all databases
psql -U username -h hostname < all_databases.sql
-- Enable WAL archiving (in postgresql.conf)
-- wal_level = replica
-- archive_mode = on
-- archive_command = 'cp %p /path/to/archive/%f'
-- Create base backup
SELECT pg_start_backup('backup_label');
-- Copy data directory
SELECT pg_stop_backup();
-- Or use pg_basebackup
# Create base backup using pg_basebackup
pg_basebackup -U username -h hostname -D /backup/base -Ft -z -P
# Point-in-time recovery
# 1. Stop PostgreSQL
# 2. Replace data directory with base backup
# 3. Create recovery.conf
# 4. Start PostgreSQL
# recovery.conf example
restore_command = 'cp /path/to/archive/%f %p'
recovery_target_time = '2023-12-25 14:30:00'
# Configure WAL archiving (postgresql.conf)
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/archive/%f && cp %p /backup/archive/%f'
archive_timeout = 300
# Create base backup
pg_basebackup -U replication_user -h hostname -D /backup/base -Ft -z -P -W
# Automated backup script
#!/bin/bash
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydatabase"
# Create backup directory
mkdir -p $BACKUP_DIR/$DATE
# Perform base backup
pg_basebackup -U postgres -D $BACKUP_DIR/$DATE -Ft -z -P
# Clean old backups (keep last 7 days)
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;
echo "Backup completed: $BACKUP_DIR/$DATE"
-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
-- Use EXPLAIN ANALYZE for actual execution stats
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE username = 'john_doe';
-- Use EXPLAIN with different options
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.username;
-- Optimize WHERE clauses
-- Bad: Function on column prevents index usage
SELECT * FROM users WHERE UPPER(username) = 'JOHN_DOE';
-- Good: Use functional index or rewrite query
CREATE INDEX idx_users_upper_username ON users(UPPER(username));
-- Or
SELECT * FROM users WHERE username = 'john_doe';
-- Use appropriate JOIN types
-- Use EXISTS instead of IN for better performance
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);
-- Use LIMIT for large result sets
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- Use partial indexes for filtered queries
CREATE INDEX idx_active_users ON users(username) WHERE is_active = TRUE;
Optimización del índice
-- Analyze table statistics
ANALYZE users;
-- Update all table statistics
ANALYZE;
-- Check index usage
SELECT
schemaname,
tablename,
indexname,
idx_tup_read,
idx_tup_fetch,
idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Find unused indexes
SELECT
schemaname,
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- Check index bloat
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as size,
CASE
WHEN pg_relation_size(indexrelid) = 0 THEN 0
ELSE (pgstatindex(indexrelid)).avg_leaf_density
END as avg_leaf_density
FROM pg_stat_user_indexes;
-- Reindex to reduce bloat
REINDEX INDEX idx_users_username;
REINDEX TABLE users;
REINDEX DATABASE mydatabase;
-- Create covering indexes
CREATE INDEX idx_users_covering ON users(username) INCLUDE (email, created_at);
-- Show current configuration
SHOW ALL;
-- Important parameters to tune
SHOW shared_buffers; -- Should be 25% of RAM
SHOW effective_cache_size; -- Should be 50-75% of RAM
SHOW work_mem; -- Per-operation memory
SHOW maintenance_work_mem; -- For maintenance operations
SHOW max_connections; -- Based on application needs
SHOW checkpoint_segments; -- For write-heavy workloads
SHOW wal_buffers; -- WAL buffer size
-- Set parameters for session
SET work_mem = '256MB';
SET enable_seqscan = off;
-- Show query execution statistics
SELECT
query,
calls,
total_time,
mean_time,
rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
-- Reset statistics
SELECT pg_stat_reset();
SELECT pg_stat_statements_reset();
-- Monitor active queries
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
ORDER BY duration DESC;
-- Monitor locks
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
-- Monitor table and index sizes
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- Monitor database connections
SELECT
datname,
numbackends,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
temp_files,
temp_bytes
FROM pg_stat_database;
```_
## Vacuum and Maintenance
```sql
-- Manual vacuum
VACUUM users;
VACUUM ANALYZE users;
VACUUM FULL users; -- Reclaims space but locks table
-- Show vacuum statistics
SELECT
schemaname,
tablename,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables;
-- Configure autovacuum (postgresql.conf)
-- autovacuum = on
-- autovacuum_max_workers = 3
-- autovacuum_naptime = 1min
-- autovacuum_vacuum_threshold = 50
-- autovacuum_analyze_threshold = 50
-- autovacuum_vacuum_scale_factor = 0.2
-- autovacuum_analyze_scale_factor = 0.1
-- Per-table autovacuum settings
ALTER TABLE large_table SET (
autovacuum_vacuum_scale_factor = 0.1,
autovacuum_analyze_scale_factor = 0.05
);
-- Check table bloat
SELECT
schemaname,
tablename,
n_dead_tup,
n_live_tup,
ROUND(n_dead_tup * 100.0 / GREATEST(n_live_tup + n_dead_tup, 1), 2) as dead_tuple_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_tuple_percent DESC;
-- Master configuration (postgresql.conf)
-- wal_level = replica
-- max_wal_senders = 3
-- wal_keep_segments = 64
-- archive_mode = on
-- archive_command = 'cp %p /path/to/archive/%f'
-- Create replication user on master
CREATE USER replication_user WITH REPLICATION LOGIN PASSWORD 'replication_password';
-- Configure pg_hba.conf on master
-- host replication replication_user slave_ip/32 md5
-- Get master status
SELECT pg_current_wal_lsn();
# Create base backup for slave
pg_basebackup -h master_ip -D /var/lib/postgresql/data -U replication_user -P -W
# Slave configuration (postgresql.conf)
# hot_standby = on
# Create recovery.conf on slave
standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=replication_user password=replication_password'
trigger_file = '/tmp/postgresql.trigger'
# Start slave
systemctl start postgresql
-- On master: Check replication status
SELECT
client_addr,
state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
sync_state
FROM pg_stat_replication;
-- On slave: Check replication lag
SELECT
now() - pg_last_xact_replay_timestamp() AS replication_lag;
-- Check if in recovery mode
SELECT pg_is_in_recovery();
-- Show recovery status
SELECT
pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn(),
pg_last_xact_replay_timestamp();
-- On publisher: Create publication
CREATE PUBLICATION my_publication FOR ALL TABLES;
-- Or for specific tables
CREATE PUBLICATION my_publication FOR TABLE users, posts;
-- On subscriber: Create subscription
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=publisher_ip port=5432 user=replication_user password=password dbname=mydatabase'
PUBLICATION my_publication;
-- Monitor logical replication
SELECT * FROM pg_publication;
SELECT * FROM pg_subscription;
SELECT * FROM pg_stat_subscription;
-- Add table to publication
ALTER PUBLICATION my_publication ADD TABLE new_table;
-- Remove table from publication
ALTER PUBLICATION my_publication DROP TABLE old_table;
-- Refresh subscription
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;
-- Drop subscription
DROP SUBSCRIPTION my_subscription;
-- Drop publication
DROP PUBLICATION my_publication;
# Promote slave to master
pg_ctl promote -D /var/lib/postgresql/data
# Or create trigger file
touch /tmp/postgresql.trigger
# Planned switchover
# 1. Stop writes to master
# 2. Wait for slave to catch up
# 3. Promote slave
# 4. Reconfigure applications
# 5. Set up old master as new slave
-- List available extensions
SELECT * FROM pg_available_extensions ORDER BY name;
-- List installed extensions
\dx
SELECT * FROM pg_extension;
-- Install extension
CREATE EXTENSION IF NOT EXISTS extension_name;
-- Install extension in specific schema
CREATE EXTENSION extension_name SCHEMA schema_name;
-- Update extension
ALTER EXTENSION extension_name UPDATE;
-- Drop extension
DROP EXTENSION extension_name;
-- UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
-- Cryptographic functions
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT crypt('password', gen_salt('bf'));
SELECT digest('text', 'sha256');
-- Full-text search
CREATE EXTENSION IF NOT EXISTS unaccent;
SELECT unaccent('Café');
-- PostGIS for geographic data
CREATE EXTENSION IF NOT EXISTS postgis;
-- Foreign data wrappers
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
-- Additional data types
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS ltree;
CREATE EXTENSION IF NOT EXISTS citext;
-- Statistics and monitoring
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Fuzzy string matching
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
SELECT soundex('Smith');
SELECT levenshtein('kitten', 'sitting');
-- hstore examples
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
attributes HSTORE
);
INSERT INTO products (name, attributes) VALUES
('Laptop', 'brand=>Dell, ram=>16GB, storage=>512GB SSD');
SELECT name, attributes->'brand' as brand FROM products;
SELECT * FROM products WHERE attributes @> 'brand=>Dell';
-- ltree examples (hierarchical data)
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
path LTREE,
name VARCHAR(100)
);
INSERT INTO categories (path, name) VALUES
('electronics', 'Electronics'),
('electronics.computers', 'Computers'),
('electronics.computers.laptops', 'Laptops');
SELECT * FROM categories WHERE path ~ '*.computers.*';
SELECT * FROM categories WHERE path <@ 'electronics';
-- citext examples (case-insensitive text)
CREATE TABLE users_ci (
id SERIAL PRIMARY KEY,
username CITEXT UNIQUE,
email CITEXT
);
INSERT INTO users_ci (username, email) VALUES
('JohnDoe', 'JOHN@EXAMPLE.COM');
SELECT * FROM users_ci WHERE username = 'johndoe'; -- Case insensitive
JSON y JSONB
-- JSON: stored as text, preserves formatting
-- JSONB: binary format, more efficient, supports indexing
CREATE TABLE json_test (
id SERIAL PRIMARY KEY,
data JSON,
metadata JSONB
);
-- Insert JSON data
INSERT INTO json_test (data, metadata) VALUES
('{"name": "John", "age": 30, "city": "New York"}',
'{"preferences": {"theme": "dark", "language": "en"}, "tags": ["user", "premium"]}');
-- Extract JSON field
SELECT data->>'name' as name FROM json_test;
SELECT data->'age' as age FROM json_test;
-- Extract nested JSON
SELECT metadata->'preferences'->>'theme' as theme FROM json_test;
-- Extract array element
SELECT metadata->'tags'->0 as first_tag FROM json_test;
-- Check if key exists
SELECT * FROM json_test WHERE data ? 'name';
SELECT * FROM json_test WHERE metadata ? 'preferences';
-- Check if any array element exists
SELECT * FROM json_test WHERE metadata->'tags' ? 'premium';
-- Contains operator (JSONB only)
SELECT * FROM json_test WHERE metadata @> '{"preferences": {"theme": "dark"}}';
-- Contained by operator (JSONB only)
SELECT * FROM json_test WHERE '{"theme": "dark"}' <@ metadata->'preferences';
-- Path exists
SELECT * FROM json_test WHERE metadata @? '$.preferences.theme';
-- Path match
SELECT * FROM json_test WHERE metadata @@ '$.tags[*] == "premium"';
-- JSON creation functions
SELECT json_build_object('name', 'John', 'age', 30);
SELECT json_build_array('apple', 'banana', 'orange');
SELECT to_json(row(1, 'John', 'john@example.com'));
-- JSON aggregation
SELECT json_agg(username) FROM users;
SELECT json_object_agg(username, email) FROM users;
-- JSONB aggregation
SELECT jsonb_agg(username) FROM users;
SELECT jsonb_object_agg(username, email) FROM users;
-- JSON modification (JSONB only)
SELECT metadata || '{"new_field": "value"}' FROM json_test;
SELECT metadata - 'preferences' FROM json_test;
SELECT metadata #- '{preferences,theme}' FROM json_test;
-- Set value
SELECT jsonb_set(metadata, '{preferences,theme}', '"light"') FROM json_test;
-- Insert value
SELECT jsonb_insert(metadata, '{tags,1}', '"new_tag"') FROM json_test;
-- Pretty print
SELECT jsonb_pretty(metadata) FROM json_test;
-- GIN index on JSONB column
CREATE INDEX idx_metadata_gin ON json_test USING GIN (metadata);
-- GIN index on specific JSON path
CREATE INDEX idx_metadata_preferences ON json_test USING GIN ((metadata->'preferences'));
-- B-tree index on extracted value
CREATE INDEX idx_metadata_theme ON json_test ((metadata->'preferences'->>'theme'));
-- Partial index
CREATE INDEX idx_premium_users ON json_test USING GIN (metadata)
WHERE metadata->'tags' ? 'premium';
-- Expression index
CREATE INDEX idx_metadata_keys ON json_test USING GIN (jsonb_object_keys(metadata));
-- Check if valid JSON
SELECT '{"name": "John"}' IS JSON; -- PostgreSQL 12+
-- Custom validation function
CREATE OR REPLACE FUNCTION is_valid_email_json(data JSONB)
RETURNS BOOLEAN AS $$
BEGIN
RETURN data->>'email' ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
END;
$$ LANGUAGE plpgsql;
-- Add check constraint
ALTER TABLE json_test ADD CONSTRAINT valid_email_in_data
CHECK (is_valid_email_json(metadata));
```_
## Full-Text Search
## Basic Full-Text Search
```sql
-- Create table with text data
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title VARCHAR(200),
content TEXT
);
INSERT INTO documents (title, content) VALUES
('PostgreSQL Tutorial', 'Learn PostgreSQL database management system'),
('Advanced SQL', 'Master complex SQL queries and optimization'),
('Database Design', 'Best practices for designing relational databases');
-- Basic text search
SELECT * FROM documents
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'PostgreSQL');
-- Search with ranking
SELECT
title,
ts_rank(to_tsvector('english', title || ' ' || content), to_tsquery('english', 'database')) as rank
FROM documents
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'database')
ORDER BY rank DESC;
-- Show available text search configurations
SELECT cfgname FROM pg_ts_config;
-- Show available dictionaries
SELECT dictname FROM pg_ts_dict;
-- Show available parsers
SELECT prsname FROM pg_ts_parser;
-- Create custom text search configuration
CREATE TEXT SEARCH CONFIGURATION my_config (COPY = english);
-- Modify configuration
ALTER TEXT SEARCH CONFIGURATION my_config
ALTER MAPPING FOR word WITH english_stem, simple;
-- Set default configuration
SET default_text_search_config = 'my_config';
```_
## Advanced Text Search
```sql
-- Add tsvector column for better performance
ALTER TABLE documents ADD COLUMN search_vector TSVECTOR;
-- Update search vector
UPDATE documents SET search_vector =
to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));
-- Create GIN index
CREATE INDEX idx_documents_search ON documents USING GIN (search_vector);
-- Create trigger to maintain search vector
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector := to_tsvector('english', coalesce(NEW.title, '') || ' ' || coalesce(NEW.content, ''));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_documents_search_vector
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();
-- Complex queries
SELECT * FROM documents
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & database');
SELECT * FROM documents
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL | MySQL');
SELECT * FROM documents
WHERE search_vector @@ to_tsquery('english', 'database & !Oracle');
-- Phrase search
SELECT * FROM documents
WHERE search_vector @@ phraseto_tsquery('english', 'database management system');
-- Fuzzy search with similarity
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT * FROM documents
WHERE similarity(title, 'PostgreSQL') > 0.3
ORDER BY similarity(title, 'PostgreSQL') DESC;
-- Highlighting search results
SELECT
title,
ts_headline('english', content, to_tsquery('english', 'database'),
'StartSel=<b>, StopSel=</b>, MaxWords=35, MinWords=15') as snippet
FROM documents
WHERE search_vector @@ to_tsquery('english', 'database');
-- Get search statistics
SELECT
word,
ndoc,
nentry
FROM ts_stat('SELECT search_vector FROM documents');
-- Parse text into tokens
SELECT * FROM ts_parse('default', 'PostgreSQL is a powerful database system');
-- Convert text to tsvector with positions
SELECT to_tsvector('english', 'PostgreSQL is a powerful database system');
-- Show lexemes
SELECT unnest(tsvector_to_array(to_tsvector('english', 'PostgreSQL databases')));
-- Current activity
SELECT
pid,
usename,
application_name,
client_addr,
state,
query_start,
now() - query_start as duration,
query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;
-- Database statistics
SELECT
datname,
numbackends,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted
FROM pg_stat_database;
-- Table statistics
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_live_tup,
n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
-- Index statistics
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Long running queries
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
ORDER BY duration DESC;
-- Blocking queries
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
-- Cache hit ratio
SELECT
'index hit rate' as name,
(sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) as ratio
FROM pg_stat_user_indexes
UNION ALL
SELECT
'table hit rate' as name,
sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) as ratio
FROM pg_stat_user_tables;
-- Connection count by state
SELECT
state,
count(*)
FROM pg_stat_activity
GROUP BY state;
```_
### Resource Monitoring
```sql
-- Database sizes
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
-- Table sizes
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- WAL statistics
SELECT
pg_current_wal_lsn(),
pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') as wal_bytes;
-- Checkpoint statistics
SELECT
checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
buffers_backend
FROM pg_stat_bgwriter;
#!/bin/bash
# postgresql_monitor.sh
DB_HOST="localhost"
DB_PORT="5432"
DB_USER="monitor_user"
DB_NAME="postgres"
# Check connection count
CONNECTIONS=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity;")
if [ $CONNECTIONS -gt 80 ]; then
echo "WARNING: High connection count: $CONNECTIONS"
fi
# Check for long running queries
LONG_QUERIES=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity WHERE (now() - query_start) > interval '10 minutes';")
if [ $LONG_QUERIES -gt 0 ]; then
echo "WARNING: $LONG_QUERIES long running queries detected"
fi
# Check replication lag (if applicable)
LAG=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()));")
if [ ! -z "$LAG" ] && [ $(echo "$LAG > 60" | bc) -eq 1 ]; then
echo "WARNING: Replication lag: $LAG seconds"
fi
echo "PostgreSQL monitoring completed at $(date)"
-- Use appropriate data types
-- Bad: Using TEXT for everything
CREATE TABLE bad_design (
id TEXT,
age TEXT,
price TEXT,
is_active TEXT
);
-- Good: Specific data types
CREATE TABLE good_design (
id SERIAL PRIMARY KEY,
age SMALLINT CHECK (age >= 0 AND age <= 150),
price DECIMAL(10,2) CHECK (price >= 0),
is_active BOOLEAN DEFAULT TRUE
);
-- Normalize your database
-- Avoid redundant data
-- Use foreign keys to maintain referential integrity
-- Use meaningful names
-- Bad
CREATE TABLE u (
i INTEGER,
n VARCHAR(50),
e VARCHAR(100)
);
-- Good
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE
);
-- Add appropriate constraints
ALTER TABLE posts ADD CONSTRAINT fk_posts_user_id
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE products ADD CONSTRAINT chk_products_price
CHECK (price > 0);
-- Use partial indexes for filtered queries
CREATE INDEX idx_active_users ON users(username) WHERE is_active = TRUE;
-- Use covering indexes to avoid table lookups
CREATE INDEX idx_users_covering ON users(username) INCLUDE (email, created_at);
-- Use prepared statements to prevent SQL injection
PREPARE get_user AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(1);
DEALLOCATE get_user;
-- Use specific columns instead of SELECT *
-- Bad
SELECT * FROM users;
-- Good
SELECT id, username, email FROM users;
-- Use EXISTS instead of IN for better performance
-- Bad
SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);
-- Good
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);
-- Use LIMIT for large result sets
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- Use proper JOIN syntax
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE u.is_active = TRUE;
-- Use window functions instead of subqueries when possible
-- Bad
SELECT
username,
(SELECT COUNT(*) FROM posts WHERE user_id = users.id) as post_count
FROM users;
-- Good
SELECT
u.username,
COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.username;
-- Use CTEs for complex queries
WITH recent_users AS (
SELECT * FROM users WHERE created_at > '2023-01-01'
),
user_stats AS (
SELECT
u.id,
u.username,
COUNT(p.id) as post_count
FROM recent_users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
)
SELECT * FROM user_stats WHERE post_count > 5;
-- Regular maintenance
ANALYZE;
VACUUM;
REINDEX DATABASE mydatabase;
-- Monitor query performance
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE username = 'john_doe';
-- Use connection pooling in applications
-- Configure appropriate connection limits
-- Partition large tables
CREATE TABLE sales (
id SERIAL,
sale_date DATE,
amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
-- Use read replicas for read-heavy workloads
-- Separate read and write operations
-- Configure autovacuum appropriately
ALTER TABLE large_table SET (
autovacuum_vacuum_scale_factor = 0.1,
autovacuum_analyze_scale_factor = 0.05
);
-- Use materialized views for expensive queries
CREATE MATERIALIZED VIEW user_summary AS
SELECT
DATE_TRUNC('month', created_at) as month,
COUNT(*) as user_count
FROM users
GROUP BY DATE_TRUNC('month', created_at);
-- Refresh materialized views regularly
REFRESH MATERIALIZED VIEW user_summary;
-- Use least privilege principle
-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON users TO app_user;
-- Use row level security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_policy ON users
FOR ALL
TO app_role
USING (user_id = current_setting('app.current_user_id')::INTEGER);
-- Use SSL connections
-- Configure pg_hba.conf to require SSL
-- hostssl all all 0.0.0.0/0 md5
-- Encrypt sensitive data
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Hash passwords
INSERT INTO users (username, password_hash)
VALUES ('john', crypt('password', gen_salt('bf')));
-- Validate passwords
SELECT * FROM users
WHERE username = 'john'
AND password_hash = crypt('password', password_hash);
-- Use audit logging
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(50),
operation VARCHAR(10),
user_name VARCHAR(50),
timestamp TIMESTAMP DEFAULT NOW(),
old_values JSONB,
new_values JSONB
);
-- Regular security updates
-- Monitor for suspicious activity
-- Backup encryption keys securely
# Regular automated backups
# Full backup daily
pg_dump -U postgres -Fc mydatabase > backup_$(date +%Y%m%d).dump
# Point-in-time recovery setup
# Configure WAL archiving
# Take regular base backups
# Test restore procedures regularly
pg_restore -U postgres -d test_database backup.dump
# Store backups in multiple locations
# Encrypt backup files
# Document recovery procedures
# Monitor backup success
# Set up alerts for backup failures
# Verify backup integrity
-...
PostgreSQL es un avanzado sistema de bases de datos relacionales que ofrece un excelente rendimiento, fiabilidad y extensibilidad. Esta hoja de trampa completa cubre las operaciones esenciales de PostgreSQL desde la instalación básica a temas avanzados como replicación, búsqueda de texto completo y optimización de rendimiento.
Key Strengths - Cumplimiento de la CIA: Soporte completo de transacción con fuerte consistencia - Extensibilidad: Ecosistema de extensión rico y tipos de datos personalizados - Características avanzadas: JSON/JSONB, búsqueda de texto completo, funciones de ventana, CTEs - Performance: Planificador de consultas sofisticado y características de optimización - ** Cumplimiento de los clientes**: Excelente cumplimiento estándar de SQL
Mejores casos de uso: - Aplicaciones web complejas que requieren características SQL avanzadas - Aplicaciones de almacenamiento de datos y análisis - Aplicaciones geoespaciales (con extensión PostGIS) - Aplicaciones que requieren almacenamiento de documentos JSON - Aplicaciones empresariales con lógica empresarial compleja
** Consideraciones importantes:** - El mantenimiento regular (VACUUM, ANALYZE) es crucial para un rendimiento óptimo - Una estrategia adecuada de indexación es esencial para el desempeño de las consultas - El ajuste de configuración debe basarse en las características del volumen de trabajo - Se debe vigilar y alertar a los sistemas de producción
Siguiendo las prácticas y técnicas descritas en esta hoja de trampa, puede diseñar, implementar y mantener eficazmente bases de datos PostgreSQL seguras, performantes y fiables para cualquier escala de aplicación.