gem-hunter

Laravel supports many different databases and therefore has to limit itself to the lowest common denominator of all databases. PostgreSQL, however, offers a ton more functionality which is being added to Laravel by this extension.

You can install the package via composer:

composer require tpetry/laravel-postgresql-enhanced

This package follows the semantic versioning policy. So you can always safely upgrade to a new minor and patch version without any issues.

As Laravel is a healthy framework that is still adding new database functionality, some things may break: For instance, in the past, Laravel introduced new builder methods that had already been part of this package for a long time - but with different semantics. In such cases, a new major version is promptly released with a breaking change to align with Laravel's implementation.

When upgrading Laravel, you may also have to upgrade this package to a new major version when something breaks! The minimal breaking changes of the past years are listed in the breaking changes section.

• IDE Autocomplete
• PHPStan
• Migration
  • Zero Downtime Migration
  • Extensions
  • Functions
  • Triggers
  • Views
    • Materialized Views
  • Foreign Keys
  • Indexes
    • Concurrently
    • Nulls Not Distinct
    • Partial Indexes
    • Include Columns
    • If Not Exists
    • Storage Parameters
    • Functional Indexes / Column Options
    • Fulltext Indexes
    • Temporal Indexes
  • Domain Types
  • Table Options
    • Unlogged
    • Storage Parameters
    • Row Level Security
  • Column Options
    • Compression
    • Initial
    • Using
  • Column Types
    • Arrays
    • Ranges
    • Bit Strings
    • Case Insensitive Text
    • Full Text Search
    • Hstore
    • Identity
    • IP Networks
    • International Product Numbers
    • Label Tree
    • Vector
    • XML
  • Grants
• Query
  • Explain
  • Fulltext Search
  • Lateral Subquery Joins
  • Returning Data From Modified Rows
  • Common Table Expressions (CTE)
  • Lazy By Cursor
  • Where Clauses
  • Order By
• Eloquent
  • Casts
  • Refresh Data on Save
  • Date Formats
• Connection
  • Lazy Transaction
• Expressions
• Supported Extensions
  • Timescale

Laravel provides many extension capabilities, making it hard for IDEs to do proper autocomplete. Currently, there is no generic way to tell IDEs about the added functionality of this enhanced driver. However, this package is integrated with specific IDE combinations:

• PhpStorm with Laravel Idea: The IDE Helper for Laravel can detect all PostgreSQL features by running the Generate Helper Code (Eloquent, Maco, Facades, etc.) action from the Laravel -> Code Generation toolbar menu.

This extension is adding a lot of missing PostgreSQL functionality to Laravel. A custom set of PHPStan extensions have been developed to get full static analysis support!

All features provided this extension are automatically recognized by the phpstan/extension-installer. Otherwise, you have to manually add the following path to your includes list in phpstan.neon, your config should now look like this:

includes:
    - ./vendor/nunomaduro/larastan/extension.neon
    - ./vendor/tpetry/laravel-postgresql-enhanced/phpstan-extension.neon

For applications with 24/7 requirements, migrations must never impact availability. PostgreSQL provides many functionalities to execute changes on the schema without downtime. However, sometimes a change to the schema is not tested sufficiently and locks the tables for a longer period of time in order to make the desired change. To avoid this problem, a migration can be marked as zero-downtime migration. If the migration exceeds a specified time limit, it is cancelled and the schema is reset to its original state.

use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Schema\Concerns\ZeroDowntimeMigration;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

class Test123 extends Migration
{
    use ZeroDowntimeMigration;

    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table('user', function (Blueprint $table) {
            $table->string('name', 128)->change();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('user', function (Blueprint $table) {
            $table->string('name', 32)->change();
        });
    }
}

The timeout for a maximum time limit of 1.0 second can be set separately for each migration. You can set private float $timeout = 5.0 on the migration for a up/down timeout. Or you can set the specific timeouts $timeoutUp and $timeoutDown to differentiate between the methods.

The Schema facade supports the creation of extensions with the createExtension and createExtensionIfNotExists methods:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createExtension('tablefunc');
Schema::createExtensionIfNotExists('tablefunc');

To remove extensions, you may use the dropExtension and dropExtensionIfExists methods provided by the Schema facade:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropExtension('tablefunc');
Schema::dropExtensionIfExists('tablefunc');

You may drop many extensions at once by passing multiple extension names:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropExtension('tablefunc', 'fuzzystrmatch');
Schema::dropExtensionIfExists('tablefunc', 'fuzzystrmatch');

The Schema facade supports the creation of functions with the createFunction and createFunctionOrReplace methods. For the definition of your function you have to provide the name of the function, the parameters, the return type, the function's language and body:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createFunction(
  name: 'sales_tax',
  parameters: ['subtotal' => 'numeric'],
  return: 'numeric',
  language: 'plpgsql',
  body: '
    BEGIN
      RETURN subtotal * 0.06;
    END;
  '
);

A sixth parameter lets you define further options for the function. Please read the manual for the exact meaning, some of them set enable or disable ways for PostgreSQL to optimize the execution.

The former example can be optimized by using the special sql:expression language identifier created by this driver. The function body can only be one SQL expression, but it will be inlined in the query instead of executed with recent PostgreSQL versions for much better performance:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createFunction('sales_tax', ['subtotal' => 'numeric'], 'numeric', 'sql:expression', 'subtotal * 0.06', [
  'parallel' => 'safe',
  'volatility' => 'immutable',
]);

If you want your function to return a table, you have to provide the columns as return type:

Schema::createFunction('search_user', ['pattern' => 'text'], ['id' => 'int', 'email' => 'text'], 'plpgsql', "
  BEGIN
    RETURN QUERY select user_id, contactemail from users where name ilike '%' || pattern || '%';
  END;
");

To remove functions, you may use the dropFunction and dropFunctionIfExists methods provided by the Schema facade:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropFunction('sales_tax');
Schema::dropFunctionIfExists('sales_tax');

On your Blueprint you can add triggers to a table. You need to pass in a unique name, call of a function you've created before and the action that will fire the trigger:

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('projects', function (Blueprint $table): void {
    $table->trigger('rollup_quota', 'update_quota_by_projects()', 'AFTER INSERT OR DELETE');
});

The following table contains all of the available trigger modifiers:

To remove trigger, you may use the dropTrigger and dropTriggerIfExists methods provided by the table's Blueprint class:

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('projects', function (Blueprint $table): void {
    $table->dropTrigger('update_quota');
    $table->dropTriggerIfExists('update_quota');
});

The Schema facade supports the creation of views with the createView and createViewOrReplace methods. The definition of your view can be a sql query string or a query builder instance:

use Illuminate\Support\Facades\DB;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createView('users_with_2fa', 'SELECT * FROM users WHERE two_factor_secret IS NOT NULL');
Schema::createViewOrReplace('users_without_2fa', DB::table('users')->whereNull('two_factor_secret'));

You can specify alternative names for the view's columns by passing an array as the last parameter:

use Illuminate\Support\Facades\DB;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createView('users_with_2fa', DB::table('users')->select('id')->whereNull('two_factor_secret'), ['user_id']);

If you need to create recursive views the createRecursiveView and createRecursiveViewOrReplace methods can be used like in the former examples but you need to provide the available columns as last parameter:

use Illuminate\Support\Facades\DB;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

// TODO simple example explaining the concept
Schema::createRecursiveView('viewname', 'SELECT id, col1, col2 FROM ....', ['id', 'col1', 'col2']);
Schema::createRecursiveViewOrReplace('viewname', 'SELECT id, col1, col2 FROM ....', ['id', 'col1', 'col2']);

To remove views, you may use the dropView and dropViewIfExists methods provided by the Schema facade. You don't have to distinguish normala and recursive views:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropView('myview');
Schema::dropViewIfExists('myview');

You may drop many views at once by passing multiple view names:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropView('myview1', 'myview2');
Schema::dropViewIfExists('myview1', 'myview2');

With materialized views you can populate a view with the contents of a query's results at the time the query is executed. You can use them to cache expensive queries so they are not re-run all the time.

Materialized views are created (and dropped) the same as normal views. You can either pass in a query builder or raw sql query. A useful method to create materialized views for very slow queries is to create them without any data initially. By passing the withData: false parameter the materialized view is created instantly and no data is stored, you need to refresh it later to contain some data.

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createMaterializedView('users_with_2fa', 'SELECT * FROM users WHERE two_factor_secret IS NOT NULL');
Schema::createMaterializedView('users_with_2fa', DB::table('users')->whereNull('two_factor_secret'));
Schema::createMaterializedView('users_with_2fa', DB::table('users')->select('id')->whereNull('two_factor_secret'), columns: ['user_id']);

Schema::createMaterializedView('very_slow_query_materialized', 'SELECT ...', withData: false);

Schema::dropMaterializedView('users_with_2fa');
Schema::dropMaterializedViewIfExists('users_with_2fa');

The stored values of a created materialized view can be refreshed whenever you want to. When passing the concurrently: true parameter the command will finish instantly and PostgreSQL will refresh the values in the background. You can also change the materialized views behaviour to (not) contain any data anymore with the withData: true and withData: false parameter.

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::refreshMaterializedView('users_with_2fa');
Schema::refreshMaterializedView('users_with_2fa', concurrently: true);
Schema::refreshMaterializedView('users_with_2fa', withData: false);
Schema::refreshMaterializedView('users_with_2fa', withData: true);

Many large applications don't use foreign keys because of performance reasons and migrations become more complicated. However, they are convenient as graphical tools can show the relationships between tables. With PostgreSQL 18, you can activate the NOT ENFORCED mode for foreign keys so they are never checked, while still helping database tools to show the relationships between tables.

On top of that, the integration with Laravel is built so you can have actual foreign keys in development/testing (to see development issues early when foreign key errors are thrown), while production uses the non-enforced version.

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('posts', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained()->notEnforced(app()->isProduction());
});

Laravel provides uniqueness with the $table->unique() method but these are unique constraints instead of unique indexes. If you want to make values unique in the table they will behave identical. However, only for unique indexes advanced options like partial indexes, including further columns or column options are available.

To use these great features and not break compatibility with Laravel the method uniqueIndex has been added which can be used identical to unique:

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('users', function(Blueprint $table) {
    $table->uniqueIndex('email');
});

In addition to the Laravel methods to drop indexes, methods to drop indexes if they exist have been added. The methods dropFullTextIfExists, dropIndexIfExists, dropPrimaryIfExists, dropSpatialIndexIfExists and dropSpatialIndexIfExists match the semantics of their laravel originals.

With PostgreSQL, you can say goodbye to half-executed migrations on errors and the tedious effort to restore the database to a stable state. This is all thanks to its transactional approach: either all changes of a migration to your database will succeed or will be rolled back. Yay! Because of that, creating an index on a big table will take a long time and block all SQL queries during that time. You can now instruct PostgreSQL to create the index in the background without blocking any SQL query, but you must opt out of running those changes in a transaction.

<?php

use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public $withinTransaction = false;

    public function up(): void
    {
        Schema::table('blog_visits', function (Blueprint $table) {
            $table->index(['url', 'ip_address'])->concurrently();
        });
    }
};

NULL values in unique indexes are handled in a non-comprehensible way for most developers. When you e.g. save active subscriptions, you want to limit every user to have only one active subscription by e.g. creating a unique index on (user_id, cancelled_at). But as active subscriptions don't have a cancelled_at timestamp, multiple rows can be entered with the same user_id and a NULL value for cancelled_at because two NULL values are never the same. But with PostgreSQL 15 you can now instruct the database not to allow those duplicate rows by handling NULL values as not distinct:

use Illuminate\Database\Query\Builder;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::create('subscriptions', function(Blueprint $table) {
    $table->id('user_id');
    $table->timestampTz('cancelled_at');


    $table->uniqueIndex(['user_id', 'cancelled_at'])->nullsNotDistinct();
});

A partial index is an index built over a subset of a table; the subset is defined by a condition. The index contains entries only for those table rows that satisfy the condition. Partial indexes are a specialized feature, but there are several situations in which they are useful. Take for example you want to make the email address column of your users table unique and you are using soft-deletes. This is not possible because by deleting a user and creating it again the email address is used twice. With partial indexes this can be done by limiting the index to only untrashed rows:

use Illuminate\Database\Query\Builder;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('users', function(Blueprint $table) {
    $table->uniqueIndex('email')->where("deleted_at IS NULL");
    // or:
    $table->uniqueIndex('email')->where(fn (Builder $condition) => $condition->whereNull('deleted_at'));
});

Partial Indexes are created with the where method on an index created by fullText(), index(), spatialIndex() or uniqueIndex().

User::upsertPartial($users, ['email'], ['name', 'subscriptions'], 'deleted_at is null');
User::upsertPartial($users, ['email'], ['name', 'subscriptions'], fn($query) => $query->whereNull('deleted_at'));

A really great feature of recent PostgreSQL versions is the ability to include columns in an index as non-key columns. A non-key column is not used for efficient lookups but PostgreSQL can use these columns to do index-only operations which won't need to load the specific columns from the table as they are already included in the index.

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('users', function(Blueprint $table) {
    // The query "SELECT firstname, lastname FROM users WHERE email = 'test@example.com'" can be executed as an index-only scan without loading the table data
    $table->index('email')->include(['firstname', 'lastname']);
});

Columns are included in an index with the include method on an index created by index(), spatialIndex or uniqueIndex.

Sometimes, you fix performance issues by testing new indexes on the production database rather than pushing a new migration each time. But when you find the perfect one, you should also make a migration for it. Dropping the existing index to recreate it by the migration is silly. Right? You can now skip the index creation from the migration when the exact index already exists.

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('invoices', function(Blueprint $table) {
    $table->index(['target', 'division', 'date'])->ifNotExists();
});

In some cases you want to specify the storage parameters of an index. If you are using gin indexes you should read the article Debugging random slow writes in PostgreSQL why storage parameters for a gin index are important:

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('bookmarks', function(Blueprint $table) {
    $table->index('data')->algorithm('gin')->with(['fastupdate' => false]);
});

Storage parameters are defined with the with method on an index created by fullText(), index(), spatialIndex or uniqueIndex.

Sometimes an index with only column specifications is not sufficient. For maximum performance, the extended index functionalities of PostgreSQL has to be used in some cases.

• To create functional indexes the function must be bracketed and a separate index name must be specified, since an index name cannot be generated automatically from the expression.
• Column specific properties like collation, opclass, sorting or positioning of NULL values can easily be specified like in a normal SQL query directly after the column name.

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('users', function(Blueprint $table) {
    $table->uniqueIndex('(LOWER(email))', 'users_email_unique');
    $table->index(['firstname ASC NULLS FIRST', 'lastname ASC NULLS FIRST']);
    $table->index('attributes jsonb_path_ops')->algorithm('gin');
});

Fulltext-search in PostgreSQL is language-dependent: For better results all words are stemmed to their root form. Laravel is using the english language by default, for your application you may have to use a different one. You can also use the generic simple language which is not doing any stemming, but your search term will then have to include the exact string to match the record.

Additionally, you can specify a relative weight for each column of the index to control the ranking. In this example the title column is a more relevant information than the description column, so it's relative weight has been set more important (A precedes B).

For more information on all the options for fulltext-search read this article: Fine Tuning Full Text Search with PostgreSQL 12.

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('book', function (Blueprint $table) {
    $table->fullText(['title', 'description'])
        ->language('spanish')
        ->weight(['A', 'B']);
});

Imagine you've built a shopping system and one day a customer complains that his order was sent to their old address, which is hundreds of miles away from the current one. The current data in the database also shows you the current address - so you made a mistake? But how was the address when the order was shipped? Have you logged the change in an audit table? Happy for you if you did, but querying the data is complicated...

So PostgreSQL 18 added support for temporal database features: You can now version control your data with primary keys, unique keys and foreign keys that are only valid for a specific point in time:

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createExtensionIfNotExists('btree_gist');
Schema::create('addresses', function (Blueprint $table) {
    $table->uuid('id');
    $table->timestampTzRange('valid');
    // ...

    $table->primary(['id', 'valid WITHOUT OVERLAPS']);
});
Schema::create('orders', function (Blueprint $table) {
    $table->uuid('id');
    $table->uuid('address_id');
    $table->text('reference_nr');
    $table->timestampTzRange('valid');
    // ...

    $table->primary(['id', 'valid WITHOUT OVERLAPS']);
    $table->unique(['reference_nr', 'valid WITHOUT OVERLAPS']);
    $table->foreign(['address_id', 'PERIOD valid'])->references(['id', 'PERIOD valid'])->on('addresses');
});

Address::insert([
    [
        'id' => '7f8f1ee1-63fc-4472-8950-034aeb75da38',
        'valid' => '["2000-01-01 00:00:00+00","2025-01-01 00:00:00+00")',
    ],
    [
        'id' => '7f8f1ee1-63fc-4472-8950-034aeb75da38',
        'valid' => '["2025-01-01 00:00:00+00",)',
    ],
]);
Order::insert([
    'id' => '93c7d2a9-41d9-4fba-9943-7c97c4420d59',
    'address_id' => '7f8f1ee1-63fc-4472-8950-034aeb75da38',
    'reference_nr' => '1234567890',
    'valid' => '["2025-10-17 10:46:34+00",)',
]);
        
Order::query()
    ->join('addresses', 'addresses.id', 'orders.address_id')
    ->where('addresses.valid', '@>', new Expression('LOWER(orders.valid)'))
    ->whereKey('93c7d2a9-41d9-4fba-9943-7c97c4420d59');

A relational database like PostgreSQL provides a lot of data types you can choose from. But they are only generic types that should match thousands of applications. Your specific requirements are not covered. But with domain types, you can add application-specific types like a price column that has a specific amount of digits and is never negative: An existing base type (numeric(9,2)) is aliased to a new type with an optional condition that all values have to match. You can use that to create repeatable price columns in your tables or create completely new types like a license plate type that has to match a specific format.

The Schema facade supports the creation of domain types with the createDomain method by passing the name, the base type and an optional SQL condition to validate any value.

use Tpetry\PostgresqlEnhanced\Query\Builder;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createDomain('price', 'numeric(9,2)');
Schema::createDomain('price', 'numeric(9,2)', 'VALUE >= 0');
Schema::createDomain('price', 'numeric(9,2)', fn (Builder $query) => $query->where('VALUE', '>=', 0));

Your created domain types can be used in a migration like every other column by using the domain column type and using the column name and domain type name:

Schema::create('products', function (Blueprint $table): void {
  $table->id();
  $table->string('item_name');
  $table->domain('item_price', 'price');
  $table->timestampsTz();
});

The base type of a domain type can't be changed after it has been created. But you can change the condition to validate the values by removing it or replacing it with a new one:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

// To drop the validation condition:
Schema::changeDomainConstraint('price', null);

// To change validation condition:
Schema::changeDomainConstraint('price', 'VALUE > 0');
Schema::changeDomainConstraint('price', fn (Builder $query) => $query->where('VALUE', '>', 0));

To remove domain types, you have first to drop all column using them (or change their type) and then use dropDomain or dropDomainIfExists provided by the Schema facade:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropDomain('price');
Schema::dropDomainIfExists('price');

You may drop many domain types at once by passing multiple domain names:

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropDomain('price', 'license_plate');
Schema::dropDomainIfExists('price', 'license_plate');

You can mark high-write load tables as unlogged if losing that data is not an issue and you want a big speed boost for write operations. Unlogged tables are written to disk by PostgreSQL but some durability requirements to be crash-safe are skipped. They behave like every other table and keep their data on a clean shutdown while on a server crash all data is lost. This is a perfect option for temporary data which you are okay with to lose like e.g. sessions as every user can just login again. You can activate and deactivate the unlogged table mode with the unlogged method on the table blueprint.

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('sessions', function (Blueprint $table): void {
    // make the table unlogged
    $table->unlogged();
    
    // make the table crash-safe again
    $table->unlogged(false);
});

With storage parameters, you can fine-tune tables to your application requirements and it's specific workload. Storage parameters and options you may want to change:

• fillfactor for faster UPDATE-s: HOT updates for better performance
• autovacuum_analyze_scale_factor for tables with millions of rows: Explained configuration value, Table Maintenance after Bulk Modifications

You can find more suggestions for specific workloads in tuning autovacuum guide.

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('sessions', function (Blueprint $table): void {
    $table->with([