Everything we've written about PostgreSQL: connections, SQL patterns, imports, clients, and comparisons.
Connect to PostgreSQL from Ruby with the pg gem, Sequel, or ActiveRecord. Working code for connections, connection pools, parameterized queries, prepared statements, and the sslmode and PostgreSQL 18 cancel-key traps.
Connect to PostgreSQL from PHP with PDO and the pgsql extension: prepared statements, the PDO driver subclasses added in 8.4, persistent connections, transactions, SSL, and the errors you actually hit.
Connect to PostgreSQL from C# with Npgsql: ADO.NET basics, async commands, parameters, connection pooling, NpgsqlDataSource, Dapper and EF Core, SSL, and the errors you actually hit.
Connect to PostgreSQL from Java with the pgJDBC driver: DriverManager, DataSource, PreparedStatement, HikariCP pooling, SSL, and the errors you actually hit in production.
Connect to PostgreSQL from Go with pgx or lib/pq. Working code for connection pools, the database/sql vs native pgx interface decision, context timeouts, parameterized queries, and the TLS sslmode trap.
Connect to PostgreSQL from Node.js with node-postgres (pg) or Postgres.js. Working code for pools and queries, the SSL self-signed certificate trap, parameterized queries, and which client to pick.
Connect to PostgreSQL from Python with psycopg 3, psycopg2, asyncpg, or SQLAlchemy. Working code for connections, queries, pooling, and the gotchas that bite in production.
Master PostgreSQL window functions like ROW_NUMBER(), RANK(), and LAG() to perform advanced analytics and row-level calculations.
Learn how to work with PostgreSQL array types -- creation, operators, functions like unnest and array_agg, indexing with GIN, and common patterns for storing and querying arrays.
Learn how to use EXPLAIN and EXPLAIN ANALYZE in PostgreSQL to understand query execution, identify bottlenecks, and optimize slow queries -- including how to read nodes, costs, and buffer statistics.
Learn how to use generated columns in PostgreSQL -- automatically computed values derived from other columns -- including STORED vs VIRTUAL modes (PostgreSQL 18+), use cases, and limitations.
Understand PostgreSQL LATERAL joins -- what they are, how they differ from regular subqueries, and practical patterns like top-N per group, calling functions row-by-row, and flattening arrays.
Learn how to use declarative table partitioning in PostgreSQL -- range, list, and hash strategies -- with examples, partition pruning, indexing, and maintenance patterns for large tables.
Learn how to use PostgreSQL's WITH RECURSIVE to query hierarchical and graph-structured data -- org charts, categories, file trees, and bill-of-materials -- with practical examples and performance tips.
Learn how to use subqueries in PostgreSQL -- scalar, row, table, correlated, EXISTS, and ANY/ALL -- with real examples and performance guidance.
Understand how transactions work in PostgreSQL -- BEGIN, COMMIT, ROLLBACK, isolation levels, and savepoints -- with practical examples for reliable data operations.
Learn how to use PostgreSQL's INSERT ON CONFLICT to upsert data -- insert new rows or update existing ones atomically, with examples covering DO NOTHING, DO UPDATE, and partial indexes.
Learn how to create and use regular views and materialized views in PostgreSQL, when to use each, and how to manage refresh strategies.
How to use PostgreSQL aggregate functions with GROUP BY, HAVING, FILTER, DISTINCT, and ordered aggregates. Covers standard and advanced aggregates.
Learn how to use Common Table Expressions (CTEs) in PostgreSQL to write cleaner, more maintainable, and complex queries.
How to work with dates and timestamps in PostgreSQL: date_trunc, extract, age, intervals, timezone handling, and common patterns for time-series analysis.
How to implement full-text search in PostgreSQL using tsvector and tsquery, ranking results, handling multiple languages, and indexing for performance.
Understand every PostgreSQL index type, when to use each, how they affect query performance, and common pitfalls to avoid.
Learn every type of join in PostgreSQL -- INNER, LEFT, RIGHT, FULL, CROSS, SELF, and LATERAL -- with real examples and common pitfalls.
A practical guide to querying JSON and JSONB data in PostgreSQL, covering operators, path expressions, indexing, and common patterns.
How to create pivot tables in PostgreSQL using the crosstab() function from tablefunc, CASE expressions, and the FILTER clause. With working examples.
How to manipulate text in PostgreSQL: substring, concat, trim, regexp_replace, split_part, format, and more -- with real-world examples.
Four ways to import Excel data into PostgreSQL: convert to CSV and use COPY, Python with pandas, pgAdmin's import tool, and Mako. Covers type mapping, multi-sheet files, and common pitfalls.
Four ways to import JSON data into PostgreSQL: COPY with psql, pg_read_file, a Python script, and Mako. Covers jsonb vs json, flattening nested structures, and performance tips.
Four ways to import CSV files into PostgreSQL: the COPY command, psql \copy, pgAdmin's GUI, and Mako's visual importer. Includes gotchas for encoding, NULLs, and large files.
A detailed comparison of Google BigQuery and PostgreSQL, covering architecture, performance, use cases, and pricing.
A deep dive into the key differences between MySQL and PostgreSQL, covering performance, features, and use cases.