Laravel + PostgreSQL Ltree

Question

How to write the migration file to add the field type 'ltree' (PostgreSQL)

Schema::create('table', function (Blueprint $table) {
     ....
$table->ltree('path');
}

Does not work.

Thanks!

Answer 1

Look to the manual of the functions available: https://laravel.com/docs/5.1/migrations#creating-columns

Laravel's goal is compatibility, so unless there is equivalent structures in all supported databases, they aren't likely to support it natively.

You can run SQL statements manually using DB::statement('CREATE TABLE ...')

Just keep in mind that your application will be locked to postgres which may not be ideal.

Answer 2

Here business_category is my ltree datatype

public function up()
{
    Schema::create('business_categories', function (Blueprint $table) {
        $table->increments('business_category_id')->generatedAs();
        $table->string('business_category')->nullable();
        $table->timestamps();
        
    });
    
    DB::statement("ALTER TABLE business_categories ADD COLUMN business_category_path ltree");
    DB::statement("CREATE INDEX business_categories_business_category_path_gist_idx ON business_categories USING gist(business_category_path)");
    DB::statement("CREATE INDEX business_categories_business_category_path_idx ON business_categories USING btree(business_category_path)");
}

also you must enable the ltree extension if it not enabled.

try {
    DB::statement("create extension ltree");
} catch (\Throwable $e) {
    print "ltree extension already exist";
}

Answer 3

As a quick solution use this method in your migrations:

public function up ()
{
    Schema::create('locations', function (Blueprint $table) {
        $table->increments('id');
        $table->uuid('uuid')->unique();
        $table->string('path', 255); // <--- my ltree field

        $table->timestamps();
    });

    $query = 'ALTER TABLE locations ALTER COLUMN path TYPE "ltree" USING "path"::"ltree";';
    \Illuminate\Support\Facades\DB::connection()->getPdo()->exec($query);
}