PostgreSQLDriver Class

Overview

The Kotchasan\Connection\PostgreSQLDriver class is a specialized driver for PostgreSQL databases. It supports advanced PostgreSQL features such as schemas, SSL connections, and database-specific SQL functions.

Installation and Requirements

Requirements

• PHP 7.4 or higher
• PDO PostgreSQL extension (pdo_pgsql)
• PostgreSQL 10.0+

Checking Extension

if (!extension_loaded('pdo_pgsql')) {
    throw new \Exception('PDO PostgreSQL extension is not installed');
}

Connection Configuration

Basic Configuration

use Kotchasan\Connection\PostgreSQLDriver;

$config = [
    'host' => 'localhost',
    'port' => 5432,
    'database' => 'myapp',
    'username' => 'postgres',
    'password' => 'password',
    'schema' => 'public'
];

$driver = new PostgreSQLDriver();
$connected = $driver->connect($config);

SSL Configuration

$config = [
    'host' => 'postgres.example.com',
    'port' => 5432,
    'database' => 'secure_db',
    'username' => 'app_user',
    'password' => 'secure_pass',
    'schema' => 'app_schema',
    'sslmode' => 'require',
    'sslcert' => '/path/to/client.crt',
    'sslkey' => '/path/to/client.key',
    'sslrootcert' => '/path/to/ca.crt',
    'application_name' => 'MyKotchasanApp'
];

PostgreSQL-Specific Features

Schema Management

// Connect to specific schema
$config['schema'] = 'analytics';
$driver->connect($config);

Table Optimization

// PostgreSQL uses VACUUM instead of OPTIMIZE
$success = $driver->optimizeTable('users');
// Executes: VACUUM FULL "users";

Emptying Tables

// TRUNCATE with PostgreSQL-specific options
$success = $driver->emptyTable('temp_data', [
    'use_truncate' => true,
    'restart_identity' => true,  // Reset sequences
    'cascade' => false           // Don't delete related tables
]);

SQL Functions for PostgreSQL

Date and Time Functions

// Extract year (uses EXTRACT)
$yearFunc = $driver->formatSqlFunction('YEAR', ['column' => 'created_at'], 'year_created');
// Result: EXTRACT(YEAR FROM "created_at") AS "year_created"

// Convert to date
$dateFunc = $driver->formatSqlFunction('DATE', ['column' => 'timestamp_col'], 'date_only');
// Result: "timestamp_col"::DATE AS "date_only"

// Date formatting
$formatFunc = $driver->formatSqlFunction('DATE_FORMAT', [
    'column' => 'created_at',
    'format' => 'YYYY-MM-DD'
], 'formatted_date');
// Result: TO_CHAR("created_at", 'YYYY-MM-DD') AS "formatted_date"

String Functions

// Group concatenation (uses STRING_AGG)
$groupConcatFunc = $driver->formatSqlFunction('GROUP_CONCAT', [
    'column' => 'tag_name',
    'separator' => ', ',
    'order' => ['tag_name']
], 'tags');
// Result: STRING_AGG("tag_name", ', ' ORDER BY "tag_name") AS "tags"

// Handle NULL values (uses COALESCE)
$ifnullFunc = $driver->formatSqlFunction('IFNULL', [
    'column1' => 'nickname',
    'column2' => 'first_name'
], 'display_name');
// Result: COALESCE("nickname", "first_name") AS "display_name"

Other Functions

// Random (uses RANDOM)
$randFunc = $driver->formatSqlFunction('RAND', [], 'random_value');
// Result: RANDOM() AS "random_value"

// Date difference
$datediffFunc = $driver->formatSqlFunction('DATEDIFF', [
    'column1' => 'end_date',
    'column2' => 'start_date'
], 'duration');
// Result: ("end_date" - "start_date") AS "duration"

Usage Examples

Working with Sequences

// Get last ID from sequence
$lastId = $driver->lastInsertId('users_id_seq');

// Use in INSERT
$stmt = $driver->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(['John Doe', 'john@example.com']);
$newId = $driver->lastInsertId('users_id_seq');

Advanced Transaction Management

try {
    $driver->beginTransaction();

    // Set isolation level
    $driver->prepare("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")->execute();

    // Perform operations
    $stmt1 = $driver->prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)");
    $stmt1->execute([1, 100.00]);

    $stmt2 = $driver->prepare("UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?");
    $stmt2->execute([1, 123]);

    $driver->commit();

} catch (\Exception $e) {
    $driver->rollback();
    throw $e;
}

Working with JSON (PostgreSQL 9.2+)

// Query JSON data
$sql = "SELECT data->>'name' as name, data->>'email' as email FROM user_profiles WHERE data ? 'active'";
$stmt = $driver->prepare($sql);
$result = $stmt->execute();

Error Handling

Common Errors

try {
    $driver->connect($config);
} catch (\PDOException $e) {
    $errorCode = $e->getCode();
    $message = $e->getMessage();

    if (strpos($message, 'authentication failed') !== false) {
        echo "Authentication failed";
    } elseif (strpos($message, 'database') !== false && strpos($message, 'does not exist') !== false) {
        echo "Kotchasan\Database not found";
    } elseif (strpos($message, 'Connection refused') !== false) {
        echo "Cannot connect to PostgreSQL server";
    } else {
        echo "PostgreSQL error: " . $message;
    }
}

Performance Tuning

Recommended PostgreSQL Settings

-- postgresql.conf
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

Using EXPLAIN ANALYZE

// Analyze query performance
$sql = "EXPLAIN ANALYZE SELECT * FROM users WHERE email = ?";
$stmt = $driver->prepare($sql);
$result = $stmt->execute(['user@example.com']);

Best Practices and Considerations

Best Practices

1. Use Schemas: Organize tables with schemas
2. Configure SSL: Use SSL for secure connections
3. Use VACUUM: Run VACUUM regularly
4. Manage Sequences: Specify sequence name when using lastInsertId()

Considerations

1. Case Sensitivity: PostgreSQL is case-sensitive
2. Identifier Quoting: Use double quotes for identifiers
3. Data Types: Be aware of data type differences

Common Issues

1. Schema Path: Set search_path correctly
2. Encoding: Use UTF-8 encoding
3. Connection Pooling: Use connection pooling for large applications

Related Classes

• Connection - Basic connection class
• ConnectionManager - Connection manager
• DriverInterface - Driver interface
• MySQLDriver - MySQL driver