Postgres `generatedAs` and `storedAs` in Laravel migaration

I have this migration. I am using Postgres, I found that generatedAs and storedAs are supported by Postgres.. I am a beginner in Postgres.

Schema::create('users', function (Blueprint $table) {
       $table->bigIncrements('id');
       $table->string('first_name');
       $table->string('last_name');
       $table->string('full_name')->generatedAs("first_name || ' ' || last_name"); // error
       $table->boolean('active')->default(false);
       $table->string('status')->storedAs("CASE WHEN active = 1 THEN 'Active' ELSE 'Inactive' END"); // error
        });

The error is

 Illuminate\Database\QueryException 

  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "first_name"
LINE 1: ...r(255) not null generated by default as identity (first_name...
                                                             ^ (Connection: pgsql, SQL: create table "users" ("id" bigserial not null primary key, "first_name" varchar(255) not null, "last_name" varchar(255) not null, "full_name" varchar(255) not null generated by default as identity (first_name || ' ' || last_name), "active" boolean not null default '0', "status" varchar(255) not null generated always as (CASE WHEN active = 1 THEN 'Active' ELSE 'Inactive' END) stored))

martinbean

1 year ago

Level 80

@watheq I’d just be using accessors for full name and status values in your model, rather than storing them as virtual columns like that.

1 like

Watheq

1 year ago

Level 14

@martinbean

I am with you.. but here I want to apply this for logic I want to implement later.. for backup postgres tables even with virtual columns..

tykus

1 year ago

Best Answer

Level 104

@Watheq I believe what you want actually is the storedAs method only; the full_name column is not an identity column where the Expression should be a Sequence:

->generatedAs($expression) Create an identity column with specified sequence options (PostgreSQL).

Instead, using storedAs, you will get the expected calculated value from the existing columns

$table->string('full_name')->storedAs("first_name || ' ' || last_name");

1 like

LaryAI

1 year ago

Level 58

To use generated columns in PostgreSQL with Laravel migrations, you need to ensure that the syntax is correct and that the database supports the features you're trying to use. PostgreSQL supports generated columns, but the syntax in your migration needs to be adjusted.

Here's how you can define generated columns in a Laravel migration for PostgreSQL:

Virtual (Generated) Columns: These are computed on the fly and not stored in the database.
Stored Columns: These are computed and stored in the database.

In your case, you want to use both types. Here’s how you can adjust your migration:

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

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('first_name');
            $table->string('last_name');
            // Use 'generated always as' for virtual columns
            $table->string('full_name')->virtualAs("first_name || ' ' || last_name");
            $table->boolean('active')->default(false);
            // Use 'generated always as' for stored columns
            $table->string('status')->storedAs("CASE WHEN active THEN 'Active' ELSE 'Inactive' END");
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

Explanation:

Virtual Column: The full_name column is defined as a virtual column using virtualAs. This means it is computed on the fly and not stored in the database.
Stored Column: The status column is defined as a stored column using storedAs. This means it is computed and stored in the database.

Important Notes:

Ensure you are using a version of PostgreSQL that supports generated columns (PostgreSQL 12 or later).
Ensure your Laravel version supports these methods (virtualAs and storedAs).

Running the Migration:

After defining the migration, you can run it using the following Artisan command:

php artisan migrate

This should create the users table with the appropriate generated columns in your PostgreSQL database.

tpetry

1 year ago

Level 1

generatedAs is not supported by PostgreSQl. You can only use storedAs ;)

1 like

wivaku

1 year ago

Level 1

Laravel documentation says both are supported by Postgres: https://laravel.com/docs/11.x/migrations

PostgreSQL documentation states generatedAs is supported (only stored): https://www.postgresql.org/docs/current/ddl-generated-columns.html

Please or to participate in this conversation.