Composite insert statement fails for text array column

[krumft]

Our application is using typeorm version 0.3.16 against a Postgres database, and we maintain our DB schema in TypeScript migrations. These are getting executed successfully by the ORM framework against the 'real' Postgres db.

We are using pg-mem version 2.6.12 to create some unit and integration tests for our application. We have recently noticed following interesting behaviour about this database table test that has a column of type text array NOT NULL DEFAULT '{}':

db.public.query(`CREATE TABLE "test" ("id" character varying NOT NULL, "col" text array NOT NULL DEFAULT '{}', CONSTRAINT "PK" PRIMARY KEY ("id"))`);

Scenario 1: We insert two rows in the table, one specifies the column value as '{}' and the other uses the keyword DEFAULT:

db.public.query(`INSERT INTO "test"("id", "col") VALUES ('0e6781e9-0cc6-4a85-b159-8e0fa44d86ee', '{}')`);
db.public.query(`INSERT INTO "test"("id", "col") VALUES ('1657979c-ff44-41d6-b9dc-a403d16f3055', DEFAULT)`);

Result: execution is successful.

Scenario 2: We insert the same two rows in the table but within a single INSERT statement:

db.public.query(`INSERT INTO "test"("id", "col") VALUES ('0e6781e9-0cc6-4a85-b159-8e0fa44d86ee', '{}'), ('1657979c-ff44-41d6-b9dc-a403d16f3055', DEFAULT)`);

Result: execution fails with:

Error: null value in column "col" violates not-null constraint

🐜 This seems to be an execution error, which means that your request syntax seems okay,
    but the resulting statement cannot be executed → Probably not a pg-mem error.

*️⃣ Failed SQL statement: INSERT INTO "test"("id", "col") VALUES ('0e6781e9-0cc6-4a85-b159-8e0fa44d86ee', '{}'), ('1657979c-ff44-41d6-b9dc-a403d16f3055', DEFAULT);

👉 You can file an issue at https://github.com/oguimbal/pg-mem along with a way to reproduce this error (if you can), and  the stacktrace:

    at ColRef.checkConstraints (...node_modules/pg-mem/src/column.ts:214:19)
    at MemoryTable.doInsert (...node_modules/pg-mem/src/table.ts:364:15)
    at Insert.performMutation (...node_modules/pg-mem/src/execution/records-mutations/insert.ts:182:33)
    at Insert._doExecuteOnce (...node_modules/pg-mem/src/execution/records-mutations/mutation-base.ts:61:29)
    at Insert.enumerate (...node_modules/pg-mem/src/execution/records-mutations/mutation-base.ts:73:31)
    at enumerate.next (<anonymous>)
    at SelectExec.execute (...node_modules/pg-mem/src/execution/select.ts:280:54)
    at ...pg-mem/src/execution/statement-exec.ts:203:42
    at pushExecutionCtx (...node_modules/pg-mem/src/utils.ts:391:16)
    at ...pg-mem/src/execution/statement-exec.ts:192:54
    at StatementExec.niceErrors (...node_modules/pg-mem/src/execution/statement-exec.ts:221:20)
    at StatementExec.executeStatement (...node_modules/pg-mem/src/execution/statement-exec.ts:192:21)
    at DbSchema.queries (...node_modules/pg-mem/src/schema/schema.ts:130:45)
    at queries.next (<anonymous>)
    at DbSchema.query (...pg-mem/src/schema/schema.ts:79:20)
    at ...test/storage/ContentStorageService.spec.ts:23:13
    at Generator.next (<anonymous>)
    at ...test/storage/ContentStorageService.spec.ts:8:71
    at new Promise (<anonymous>)
    at Object.<anonymous>.__awaiter (...test/storage/ContentStorageService.spec.ts:4:12)
    at Object.<anonymous> (...test/storage/ContentStorageService.spec.ts:21:57)
    at Promise.then.completed (...node_modules/jest-circus/build/utils.js:293:28)
    at new Promise (<anonymous>)
    at callAsyncCircusFn (...node_modules/jest-circus/build/utils.js:226:10)
    at _callCircusTest (...node_modules/jest-circus/build/run.js:297:40)
    at _runTest (...node_modules/jest-circus/build/run.js:233:3)
    at _runTestsForDescribeBlock (...node_modules/jest-circus/build/run.js:135:9)
    at _runTestsForDescribeBlock (...node_modules/jest-circus/build/run.js:130:9)
    at run (...node_modules/jest-circus/build/run.js:68:3)
    at runAndTransformResultsToJestFormat (...node_modules/jest-circus/build/legacy-code-todo-rewrite/jestAdapterInit.js:122:21)
    at jestAdapter (...jest-circus/build/legacy-code-todo-rewrite/jestAdapter.js:79:19)
    at runTestInternal (...node_modules/jest-runner/build/runTest.js:367:16)
    at runTest (...jest-runner/build/runTest.js:444:34)

Using two rows inside the single INSERT statement is done by the typeorm framework, for optimisation purposes I believe.

[krumft]

A simple test to reproduce the problem:

it('can insert rows with differently specified default values in a single statement', () => {
    none(`create table test(id character varying NOT NULL, col text array NOT NULL DEFAULT '{}');
          insert into test(id, col) values ('0', '{}'), ('1', default);
    `);
});