Problems with order of constraints in table creation

[tbossi]

Describe the bug

I tried to run the following migration using Typeorm:

await queryRunner.createTable(
      new Table({
        name: 'Products',
        columns: [
          {
            name: 'product_id',
            isPrimary: true,
            type: 'serial',
          },
          {
            name: 'name',
            type: 'varchar',
          },
          {
            name: 'main_product_id',
            type: 'int',
            isNullable: true,
          },
          {
            name: 'created_at',
            type: 'timestamp with time zone',
            default: 'now()',
          },
          {
            name: 'updated_at',
            type: 'timestamp with time zone',
            default: 'now()',
          },
          {
            name: 'deleted_at',
            type: 'timestamp with time zone',
            isNullable: true,
          },
        ],
        foreignKeys: [
          {
            name: 'fk_Products_Products',
            columnNames: ['main_product_id'],
            referencedTableName: 'Products',
            referencedColumnNames: ['product_id'],
            onUpdate: 'CASCADE',
            onDelete: 'CASCADE',
          }
        ]
      }),
      true,
    )

pg-mem fails with the error:

there is no unique constraint matching given keys for referenced table "Products"

while real postgres works.

Analyzing the query generated by typeorm, I noticed the problem may be the order of the contraints.

To Reproduce

The query generated by typeorm:

CREATE TABLE "public"."Products" (
	"product_id" serial NOT NULL,
	"name" varchar NOT NULL,
	"main_product_id" int,
	"created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
	"updated_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
	"deleted_at" TIMESTAMP WITH TIME ZONE,
	CONSTRAINT "fk_Products_Products"
		FOREIGN KEY ("main_product_id")
		REFERENCES "public"."Products" ("product_id")
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	CONSTRAINT "PK_6f8a5f1d7d708767d6945192758"
		PRIMARY KEY ("product_id")
);

The modified version that does not fail on https://oguimbal.github.io/pg-mem-playground/:

CREATE TABLE "public"."Products" (
	"product_id" serial NOT NULL,
	"name" varchar NOT NULL,
	"main_product_id" int,
	"created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
	"updated_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
	"deleted_at" TIMESTAMP WITH TIME ZONE,
	CONSTRAINT "PK_6f8a5f1d7d708767d6945192758"
		PRIMARY KEY ("product_id"),
	CONSTRAINT "fk_Products_Products"
		FOREIGN KEY ("main_product_id")
		REFERENCES "public"."Products" ("product_id")
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

Both versions work on a real postgres.

pg-mem version

3.0.4