pg-software-version

PostgreSQL domain and operators for comparing software version strings using Python's packaging.version.

Overview

This project extends PostgreSQL with a custom software_version domain type that properly handles semantic versioning comparisons. It uses Python's packaging.version library to parse and compare version strings according to PEP 440 standards.

Features

• Custom Domain Type: software_version as a varchar(255) with semantic versioning logic
• Comparison Operators: <, <=, =, >=, >, != for version comparisons
• B-tree Index Support: Full indexing capabilities for efficient queries
• Python Integration: Uses plpython3u for robust version parsing
• Convenience Functions: software_version_parse() for explicit version parsing

Installation

Prerequisites

• PostgreSQL with plpython3u extension enabled
• Python with packaging library
• Superuser privileges (for initial setup)

Quick Start

1. Enable the Python extension:

   CREATE EXTENSION plpython3u;

2. Run the installation script:

   psql -d your_database -f install.sql

3. Verify installation:

   SELECT '1.2.3'::software_version < '1.2.4'::software_version;
   -- Should return: true

Usage Examples

Basic Comparisons

-- Create a table with software versions
CREATE TABLE software_packages (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    version software_version
);

-- Insert some test data
INSERT INTO software_packages (name, version) VALUES
    ('nginx', '1.18.0'),
    ('nginx', '1.19.0'),
    ('nginx', '2.0.0'),
    ('postgresql', '13.1'),
    ('postgresql', '13.2'),
    ('postgresql', '14.0');

-- Find latest versions
SELECT name, MAX(version) as latest_version
FROM software_packages
GROUP BY name;

-- Find packages that need updates (older than 1.19.0)
SELECT * FROM software_packages 
WHERE version < '1.19.0'::software_version;

-- Find packages in a version range
SELECT * FROM software_packages 
WHERE version >= '1.18.0'::software_version 
  AND version < '2.0.0'::software_version;

Advanced Queries

-- Find the most recent version of each package
SELECT DISTINCT ON (name) name, version
FROM software_packages
ORDER BY name, version DESC;

-- Compare version ranges
SELECT 
    name,
    version,
    CASE 
        WHEN version < '1.0.0'::software_version THEN 'legacy'
        WHEN version < '2.0.0'::software_version THEN 'stable'
        ELSE 'latest'
    END as version_category
FROM software_packages;

Project Structure

pg-software-version/
├── install.sql          # Main installation script
├── uninstall.sql        # Cleanup script to remove the extension
├── examples.sql         # Comprehensive usage examples
├── tests/
│   ├── test_operators.sql  # Operator functionality tests
│   └── test_index.sql      # Index performance tests
├── README.md            # This file
└── LICENSE              # MIT License

Examples and Testing

Running Examples

The examples.sql file contains comprehensive examples including:

• Software package management scenarios
• Version compatibility matrices
• Update recommendation functions
• Version statistics and categorization

psql -d your_database -f examples.sql

Running Tests

Test the extension functionality:

# Test operator functionality
psql -d your_database -f tests/test_operators.sql

# Test index performance
psql -d your_database -f tests/test_index.sql

Uninstallation

To remove the extension from your database:

psql -d your_database -f uninstall.sql

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

• Uses Python's packaging.version library for robust version parsing
• Uses plpython3u extension for PostgreSQL-Python integration