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-enhancedThe 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 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'));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::createView('viewname', 'SELECT id, col1, col2 FROM ....', ['id', 'col1', 'col2']);
Schema::createViewOrReplace('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::dropExtension('myview1', 'myview2');
Schema::dropExtensionIfExists('myview1', 'myview2');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 compatability 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 dropIndexIfExists, dropPrimaryIfExists, dropSpatialIndexIfExists and dropSpatialIndexIfExists match the semantics of their laravel originals.
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')->partial("deleted_at IS NULL");
// or:
$table->uniqueIndex('email')->partial(fn (Builder $condition) => $condition->whereNull('deleted_at'));
});Partial Indexes are created with the partial method on an index created by index(), spatialIndex or uniqueIndex.
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.
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 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->unique('(LOWER(email))', 'users_email_unique');
$table->index(['firstname ASC NULLS FIRST', 'lastname ASC NULLS FIRST'])
$table->index('attributes jsonb_path_ops')->algorithm('gin');
});PostgreSQL 14 introduced the possibility to specify the compression method for toast-able data types.
You can choose between the default method pglz, the recently added lz4 algorithm and the value default to use the server default setting.
// @see https://www.postgresql.org/docs/current/storage-toast.html
$table->string('col')->compression('lz4');The bit string data types store strings of 0s and 1s. They can be used to e.g. store bitmaps.
// @see https://www.postgresql.org/docs/current/datatype-bit.html
$table->bit(string $column, int $length = 1);
$table->varbit(string $column, ?int $length = null);The case insensitive text type is used to store a text that will be compared case insensitive. It can be used to e.g. store and compare e-mail addresses.
// @see https://www.postgresql.org/docs/current/citext.html
$table->caseInsensitiveText(string $column);The tsvector type is used to store a processed dictionary for full text searching.
// @see https://www.postgresql.org/docs/10/datatype-textsearch.html
$table->tsvector(string $column);The ip network datatype stores an ip network in cidr notation.
// @see https://www.postgresql.org/docs/current/datatype-net-types.html
$table->ipNetwork(string $column);The hstore data type is used store key/value pairs within a single PostgreSQL value. The new json data type is better in all aspects, so hstore should only be used for compatibility with old applications.
// @see https://www.postgresql.org/docs/current/hstore.html
$table->hstore(string $column);The identity data type is the new PostgreSQL standard for automatic generated values. You can even specify whether the database should be the only one generating them (always = true) preventing accidental overwrites.
They are used to define primary keys managed by the database or any other kind of automatically generated identification that needs to be unique.
$table->identity(always: true)->primary();
$table->identity('uniqid');The international product number data types are used to store common product numbers types and validate them before saving.
// @see https://www.postgresql.org/docs/current/isn.html
$table->europeanArticleNumber13(string $column);
$table->internationalStandardBookNumber(string $column);
$table->internationalStandardBookNumber13(string $column);
$table->internationalStandardMusicNumber(string $column);
$table->internationalStandardMusicNumber13(string $column);
$table->internationalStandardSerialNumber(string $column);
$table->internationalStandardSerialNumber13(string $column);
$table->universalProductNumber(string $column);The ltree data type stores a label as its position in a tree. This provides an easy way to manage a tree without performance and complexity disadvantages compared to alternative solutions.
// @see https://www.postgresql.org/docs/current/ltree.html
$table->labelTree(string $column);The range data types store a range of values with optional start and end values. They can be used e.g. to describe the duration a meeting room is booked.
// @see https://www.postgresql.org/docs/current/rangetypes.html
$table->bigIntegerRange(string $column);
$table->dateRange(string $column);
$table->decimalRange(string $column);
$table->integerRange(string $column);
$table->timestampRange(string $column);
$table->timestampTzRange(string $column);The xml data type can be used to store an xml document.
// @see https://www.postgresql.org/docs/current/datatype-xml.html
$table->xml(string $column);If you want to contribute code to this package, please open an issue first. To avoid unnecessary effort for you it is very beneficial to first discuss the idea, the functionality and its API.
Please see CHANGELOG for more information on what has changed recently.
If you discover any security related issues, please email github@tpetry.me instead of using the issue tracker.
The MIT License (MIT). Please see License File for more information.