Array type default value doesnt work. PostgreSQL

[CocaColaBear]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] sqlite
[ ] sqljs
[ ] websql

TypeORM version:

[ ] latest
[x] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:
My entity column:

@Column("float4", {
	array: true,
	default: [ 0.0, 0.0, 0.0 ],
	nullable: false
})
public testFloatArray: number[];

This generate query:

ALTER TABLE "public"."test" ADD "testFloatArray" real array NOT NULL DEFAULT '[0,0,0]'

And i got an error: error: malformed array literal: "[0,0,0]
The PostgresSQL syntax should be:

ALTER TABLE "public"."test" ADD "testFloatArray" real array NOT NULL DEFAULT '{0,0,0}'

[pleerock]

You can do:

@Column("float4", {
	array: true,
	default: () => [ 0.0, 0.0, 0.0 ],
	nullable: false
})
public testFloatArray: number[];

[CocaColaBear]

@pleerock your decision gives an error.
Error:

error: syntax error at or near "0"

Query:

ALTER TABLE "public"."test" ADD "testFloatArray" real array NOT NULL DEFAULT 0,0,0

[CocaColaBear]

default: "{0.0, 0.0, 0.0}",

Works fine

[pleerock]

👍

[thematan]

If someone comes here to search for initializing empty array
this works:

default: ()=>'array[]::integer[]'

[thematan]

the '::' is cast syntax in postgres. It might be redundant

…

On Wed, Feb 6, 2019 at 5:25 AM Mitch Downey ***@***.***> wrote: Could someone please explain why @thematan <https://github.com/thematan>'s answer works, even when I am using a type of string[]? The integer[] part doesn't make sense to me... — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1532 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AL-7q5OsC3b35tjfIh78MP5JrNKX5Sw-ks5vKkssgaJpZM4RzkQy> .

[vlapo]

Hi @thematan. Typeorm from version 0.2.12 support define default enum array as array of typescript enum #3414:

@Column({
    type: "enum",
    enum: StringEnum,
    array: true,
    default: [StringEnum.ADMIN]
})
stringEnums: StringEnum[];

[joekendal]

@thematan what about for an array of type string? 'array[]::string[]' doesn't work 🤨

[jtrein]

@joekendal Try this:

default: () => 'array[]::text[]'

[LyricL-Gitster]

While it's nice to have a workaround, it still feels like default: [] should work and this should be open.

[fr1sk]

@pleerock please reopen this. I agree that default: [] should work, it's super intuitive.

[gabimor]

any update on this ?
We have a thing with our migrations regarding this.
Our column:

@Column({ type: 'text', nullable: false, array: true, default: 'array[]' })
someColumnName: string[]

and although the column is creted on the DB just fine, the migrations are keep being generated for this column like so:

await queryRunner.query(`COMMENT ON COLUMN "event"."someColumnName" IS NULL`);
await queryRunner.query(`ALTER TABLE "event" ALTER COLUMN "someColumnName" SET DEFAULT 'array[]'::text[]`);

we have to manually delete them from the migration file each time, it's frustrating.

We tried also default: '{}', default: 'ARRAY[]' nothing works. the column migration keep being generated each time

[nebkat]

I will try to incorporate this into #7282, but could someone give me a few examples of the expected inputs/outputs? What happens if the array contains mixed types, nulls, etc?

@gabimor default values defined as strings are treated as string values. You must use the () => 'array[]' syntax if you want it to be treated as a raw value.

[edeesis]

I have submitted a PR with a couple of test cases that I would expect would work. If anyone has any others they'd like to add, I can add them to my PR.

[c1moore]

Has this reverted? I'm seeing this issue in v0.2.34

[edeesis]

724d80b this commit was added to the method that i changed, and there's also a couple of commits tagged against this ticket. We're still using 0.2.32, so haven't seen the issue yet.

[mashirozx]

Hi @thematan. Typeorm from version 0.2.12 support define default enum array as array of typescript enum #3414:

@Column({
    type: "enum",
    enum: StringEnum,
    array: true,
    default: [StringEnum.ADMIN]
})
stringEnums: StringEnum[];

Not working now:

[Nest] 4856  - 12/30/2021, 2:08:13 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (5)...
QueryFailedError: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'array ('user', 'admin') NOT NULL DEFAULT 'user'' at line 1

[Jbarget]

Just in case it helps anyone...
This seems to work for me, setting the default as an empty string when using simple-array type:

@Column({ type: 'simple-array', default: '' })
pushTokens: string[];

[ngouy]

what worked for us was:

default: () => '{"value1", "value2"}'

[jonfreeland]

For a text array default value, we had to account for whether a value in the array had any spaces.

`'{${defaultValues.map((value) => (value.includes(" ") ? `"${value}"` : value)).join(",")}}'`;

PostgreSQL will strip unnecessary "s, which will cause the migration to be continually generated if you don't account for this.