how to create laravel migration for postgresql array data type

Question

I have the following laravel migration up method

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('email')->unique();
        $table->string('mobile')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        // $table->string('sports');
        $table->date('dob');
        $table->string('height');
        $table->rememberToken();
        $table->timestamps();
    });

    DB::statement('ALTER TABLE users ADD COLUMN sports TYPE text[] AFTER password');
}

when I run migration it will show SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "text" LINE 1: ALTER TABLE users ADD COLUMN sports TYPE text[] AFTER passwo... ^ (SQL: ALTER TABLE users ADD COLUMN sports TYPE text[] AFTER password). I don't know what's a problem there?

OMR · Accepted Answer · 2021-03-22 14:15:14Z

1

please try your Sql statement like this:

 DB::statement('alter table users alter sports drop default');
    DB::statement('alter table users alter sports type text[] using array[sports]');
    DB::statement("alter table users alter sports set default '{}'");

edited Mar 22, 2021 at 14:15

answered Mar 22, 2021 at 12:52

OMR

12.3k5 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Divyesh Jesadiya Over a year ago

it is working but it will add column at last, i need it after 'password' column only

OMR Over a year ago

what about: DB::statement('ALTER TABLE users ALTER COLUMN sports TEXT[]');

OMR Over a year ago

or DB::statement('ALTER TABLE users MODIFY COLUMN sports TEXT[]');

Divyesh Jesadiya Over a year ago

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "MODIFY" LINE 1: ALTER TABLE users MODIFY COLUMN sports TEXT[] ^ (SQL: ALTER TABLE users MODIFY COLUMN sports TEXT[])

Divyesh Jesadiya Over a year ago

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "TEXT" LINE 1: ALTER TABLE users ALTER COLUMN sports TEXT[] ^ (SQL: ALTER TABLE users ALTER COLUMN sports TEXT[])

|

user3096856 · Accepted Answer · 2022-04-12 16:03:41Z

0

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        // ...
        $table->string('sports');
        // ...
        $table->timestamps();
    });

    DB::statement('ALTER TABLE users ALTER COLUMN sports TYPE text[] USING ARRAY[sports]');
}

answered Apr 12, 2022 at 16:03

user3096856

911 silver badge1 bronze badge

Collectives™ on Stack Overflow

how to create laravel migration for postgresql array data type

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related