0

A new unique column is being added to a table. This column's value comes from a C# random code generator method, but for existing rows I need to rely on Postgres.

I have the following EF Core migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    var randomCodeSql = @"
        SELECT string_agg (substr('ABCDEFGHJKLMNPQRSTUVWXYZ123456789', ceil (random() * 33)::integer, 1), '')
        FROM generate_series(1, 6)";

    migrationBuilder.AddColumn<string>(
        name: "reference_code",
        table: "invitations",
        type: "character varying(12)",
        maxLength: 12,
        nullable: false,
        defaultValueSql: randomCodeSql);

    migrationBuilder.CreateIndex(
        name: "ix_invitations_reference_code",
        table: "invitations",
        column: "reference_code",
        unique: true);
}

The raw SQL is from this answer.

I get this error:

0A000: cannot use subquery in DEFAULT expression

Is there a code-first solution to this problem?

Parsa99
  • 307
  • 1
  • 13
  • Well, what the error message tells you. You will need to wrap your code into a function, then use that function as the column's default –  Aug 01 '22 at 07:10
  • But why not not just declare that column as `uuid` and use `gen_random_uuid()`? –  Aug 01 '22 at 07:13
  • for performance and user readability. I can't tell customers to enter a UUID! – Parsa99 Aug 01 '22 at 07:14
  • The chance of a collision with your code is quite high though. –  Aug 01 '22 at 07:37
  • Currently I have 1.2 billion possibilities, the code length will be increased as needed. – Parsa99 Aug 01 '22 at 07:54
  • An insert of only 250.000 rows already fails with a unique key violation: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7d642e1d186767eeaba06f2e5bb08a41 –  Aug 01 '22 at 07:55
  • There is duplicate error handling in the code which retries up to (n) times. But thank you anyway for your warning, I will increase the length to 8 or 10. – Parsa99 Aug 01 '22 at 08:06

0 Answers0