Introduction to PostgreSQL

PostgreSQL (Postgres) is a powerful, open-source relational database system with over 35 years of active development. It's known for its reliability, feature robustness, and performance.

Installation

# Ubuntu/Debian
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Basic SQL

-- Create a table
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Insert data
INSERT INTO posts (title, content)
VALUES ('My First Post', 'Hello world!');

-- Query data
SELECT * FROM posts ORDER BY created_at DESC;

Node.js Integration

npm install pg

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const { rows } = await pool.query('SELECT * FROM posts');