PHPackages tpetry/laravel-postgresql-enhanced - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. tpetry/laravel-postgresql-enhanced ActiveLibrary[Database & ORM](/categories/database) tpetry/laravel-postgresql-enhanced ================================== Support for many missing PostgreSQL specific features 3.6.0(2mo ago)9982.0M—8.5%46[11 issues](https://github.com/tpetry/laravel-postgresql-enhanced/issues)12MITPHPPHP ^8.0CI passing Since Apr 13Pushed 2mo ago13 watchersCompare [ Source](https://github.com/tpetry/laravel-postgresql-enhanced)[ Packagist](https://packagist.org/packages/tpetry/laravel-postgresql-enhanced)[ Docs](https://github.com/tpetry/laravel-postgresql-enhanced)[ RSS](/packages/tpetry-laravel-postgresql-enhanced/feed)WikiDiscussions master Synced 1mo ago READMEChangelogDependencies (25)Versions (78)Used By (12) [![License](https://camo.githubusercontent.com/2a7260aaec86a51e05f495927cf9d336770f38efa307d705ef7fee0d2bc5bca4/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6963656e73652f7470657472792f6c61726176656c2d706f737467726573716c2d656e68616e6365643f636f6c6f723d626c7565266c6162656c3d4c6963656e7365)](https://camo.githubusercontent.com/2a7260aaec86a51e05f495927cf9d336770f38efa307d705ef7fee0d2bc5bca4/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6963656e73652f7470657472792f6c61726176656c2d706f737467726573716c2d656e68616e6365643f636f6c6f723d626c7565266c6162656c3d4c6963656e7365)[![PHP](https://camo.githubusercontent.com/86ca6e5e88500962e3dbd8701e28147eda20e15bcb2df5beaca13ad99dfd363c/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f7068702d762f7470657472792f6c61726176656c2d706f737467726573716c2d656e68616e6365643f636f6c6f723d626c7565266c6162656c3d504850)](https://camo.githubusercontent.com/86ca6e5e88500962e3dbd8701e28147eda20e15bcb2df5beaca13ad99dfd363c/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f7068702d762f7470657472792f6c61726176656c2d706f737467726573716c2d656e68616e6365643f636f6c6f723d626c7565266c6162656c3d504850)[![Latest Version on Packagist](https://camo.githubusercontent.com/7da8bfb52f69479493ca1fbdb93ba3fc94669daf8e45578d3fd0cef24a58f024/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f7470657472792f6c61726176656c2d706f737467726573716c2d656e68616e6365642e7376673f6c6162656c3d5061636b6167697374)](https://packagist.org/packages/tpetry/laravel-postgresql-enhanced)[![Downloads on Packagist](https://camo.githubusercontent.com/817e48691330a942e39314ff52e9b67110c30326a66e5487bece3c9fe955149b/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f7470657472792f6c61726176656c2d706f737467726573716c2d656e68616e6365642e7376673f636f6c6f723d6f72616e6765266c6162656c3d446f776e6c6f616473)](https://packagist.org/packages/tpetry/laravel-postgresql-enhanced/stats)[![GitHub PHPUnit Action Status](https://camo.githubusercontent.com/c8e87543d07acc96c7191df16387958e3ba611f206b070cc24c199f17a835bc9/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f7470657472792f6c61726176656c2d706f737467726573716c2d656e68616e6365642f706870756e69742e796d6c3f6c6162656c3d5465737473)](https://github.com/tpetry/laravel-postgresql-enhanced/actions/workflows/phpunit.yml)[![GitHub PHPStan Action Status](https://camo.githubusercontent.com/bbd542c532c596dfbf3bd021864f0e03a96ae6002f967be2dbc152e838994178/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f7470657472792f6c61726176656c2d706f737467726573716c2d656e68616e6365642f7068707374616e2e796d6c3f6c6162656c3d5048505374616e)](https://github.com/tpetry/laravel-postgresql-enhanced/actions/workflows/phpstan.yml)[![GitHub PhpCsFixer Action Status](https://camo.githubusercontent.com/570398d2356d10091249a936ba91dd0dfb2007d6a5affc0a811a2046c5d7976f/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f7470657472792f6c61726176656c2d706f737467726573716c2d656e68616e6365642f7068705f63735f66697865722e796d6c3f6c6162656c3d436f64652532305374796c65)](https://github.com/tpetry/laravel-postgresql-enhanced/actions/workflows/php_cs_fixer.yml) 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. Installation ============ [](#installation) You can install the package via composer: ``` composer require tpetry/laravel-postgresql-enhanced ``` Versioning Policy ================= [](#versioning-policy) **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](#breaking-changes). Features ======== [](#features) - [IDE Autocomplete](#ide-autocomplete) - [PHPStan](#phpstan) - [Migration](#migration) - [Zero Downtime Migration](#zero-downtime-migration) - [Extensions](#extensions) - [Functions](#functions) - [Triggers](#triggers) - [Views](#views) - [Materialized Views](#materialized-views) - [Foreign Keys](#foreign-keys) - [Indexes](#indexes) - [Concurrently](#concurrently) - [Nulls Not Distinct](#nulls-not-distinct) - [Partial Indexes](#partial-indexes) - [Include Columns](#include-columns) - [If Not Exists](#if-not-exists) - [Storage Parameters](#storage-parameters-index) - [Functional Indexes / Column Options](#functional-indexes--column-options) - [Fulltext Indexes](#fulltext-indexes) - [Temporal Indexes](#temporal-indexes) - [Domain Types](#domain-types) - [Table Options](#table-options) - [Unlogged](#unlogged) - [Storage Parameters](#storage-parameters-table) - [Column Options](#column-options) - [Compression](#compression) - [Initial](#initial) - [Using](#using) - [Column Types](#column-types) - [Arrays](#arrays) - [Ranges](#ranges) - [Bit Strings](#bit-strings) - [Case Insensitive Text](#case-insensitive-text) - [Full Text Search](#full-text-search) - [Hstore](#hstore) - [Identity](#identity) - [IP Networks](#ip-networks) - [International Product Numbers](#international-product-numbers) - [Label Tree](#label-tree) - [Vector](#vector) - [XML](#xml) - [Query](#query) - [Explain](#explain) - [Fulltext Search](#fulltext-search) - [Lateral Subquery Joins](#lateral-subquery-joins) - [Returning Data From Modified Rows](#returning-data-from-modified-rows) - [Common Table Expressions (CTE)](#common-table-expressions-cte) - [Lazy By Cursor](#lazy-by-cursor) - [Where Clauses](#where-clauses) - [Order By](#order-by) - [Eloquent](#eloquent) - [Casts](#casts) - [Refresh Data on Save](#refresh-data-on-save) - [Date Formats](#date-formats) - [Expressions](#expressions) - [Supported Extensions](#supported-extensions) - [Timescale](#timescale) IDE Autocomplete ---------------- [](#ide-autocomplete) 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](https://github.com/barryvdh/laravel-ide-helper) can detect all PostgreSQL features by running the `Generate Helper Code (Eloquent, Maco, Facades, etc.)` action from the `Laravel` -> `Code Generation` toolbar menu. PHPStan ------- [](#phpstan) This extension is adding a lot of missing PostgreSQL functionality to Laravel. A custom set of [PHPStan](https://phpstan.org/) extensions have been developed to get full static analysis support! All features provided this extension are automatically recognized by the [phpstan/extension-installer](https://github.com/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 ``` Migration --------- [](#migration) ### Zero-Downtime Migration [](#zero-downtime-migration) 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. ### Extensions [](#extensions) #### Create Extensions [](#create-extensions) 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'); ``` #### Dropping Extensions [](#dropping-extensions) 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'); ``` ### Functions [](#functions) #### Create Functions [](#create-functions) 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](https://www.postgresql.org/docs/current/sql-createfunction.html) for the exact meaning, some of them set enable or disable ways for PostgreSQL to optimize the execution. OptionValuesDescription`calledOnNull`boolDefines whether the function should be called for NULL values.`cost`integerDefines the cost for executing the function.`leakproof`boolInforms whether the function has side effects.`parallel``restricted`, `safe`, `unsafe`Defines whether the function can be executed in parallel.`security``definer`, `invoker`Defines that the function will be executed with the privileges of the current user or creator of the function.`volatility``immutable`, `stable`, `volatile`Informs whether the function changes database values.`searchPath`array<string>Defines the search path of the function.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; "); ``` #### Drop Functions [](#drop-functions) 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'); ``` ### Triggers [](#triggers) #### Create Triggers [](#create-triggers) 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: ModifierDescription`->forEachRow()`The trigger will be called for every row.`->forEachStatement()`The trigger will be called once for each statement *(default)*.`->transitionTables(` ` old: 'oldrows',` ` new: 'newrows',` `)`The forEachStatement-trigger will provide the before/after state of the affected rows in special tables. You can omit either option if not valid for this trigger.`->whenCondition('NEW.type = 4')` `->whenCondition(fn ($query) => $query->where('NEW.type', 4))`The trigger should only be called when the condition matches *(only with forEachRow)*.`->replace(true)`The trigger will replace an existing one defined with the same name.Note PostgreSQL always updates rows even if nothing changed, which may affect your performance. You can add the `suppress_redundant_updates_trigger()` trigger with a `BEFORE UPDATE` action to all tables. #### Drop Triggers [](#drop-triggers) 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'); }); ``` ### Views [](#views) #### Create Views [](#create-views) 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']); ``` #### Dropping Views [](#dropping-views) 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'); ``` #### Materialized Views [](#materialized-views) 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); ``` ### Foreign Keys [](#foreign-keys) 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()); }); ``` ### Indexes [](#indexes) #### Unique Indexes [](#unique-indexes) 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'); }); ``` #### Drop If Exists [](#drop-if-exists) 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. #### Concurrently [](#concurrently) 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. ```