Npgsql.Bulk
Npgsql.Bulk copied to clipboard
Postgres temp tables being are being created with column names larger than maximum
When running an insert on a table called yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy
with 2 fields called xxxxx_xxxxxxxxxxxxx_xxxxxxx_answer_id
and xxxxx_xxxxxxxxxxxxx_xxxxxxx_id
Npgsql.Bulk will run the query:
CREATE TEMP TABLE _temp__a85e0e06_a254_4805_b172_28627fbcfd4d_54 ON COMMIT DROP AS
SELECT
"yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy"."xxxxx_xxxxxxxxxxxxx_xxxxxxx_answer_id" AS
yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy_xxxxx_xxxxxxxxxxxxx_xxxxxxx_answer_id,
"yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy"."xxxxx_xxxxxxxxxxxxx_xxxxxxx_id" AS
yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy_xxxxx_xxxxxxxxxxxxx_xxxxxxx_id
FROM
"yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy"
LIMIT
0
However, postgres caps column names at 63 characters and discards all the characters after that limit, resulting in the error:
ERROR: column "yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy_xxxxx_xxxxxxxxxxxxx_xxxxxxx_" specified more than once
Call stack:
2023-08-03 09:49:11 Npgsql.PostgresException (0x80004005): 42701: column "yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy_xxxxx_xxxxxxxxxxxxx_xxxxxxx_" specified more than once
2023-08-03 09:49:11 at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|233_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
2023-08-03 09:49:11 at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
2023-08-03 09:49:11 at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
2023-08-03 09:49:11 at Npgsql.NpgsqlDataReader.NextResult()
2023-08-03 09:49:11 at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
2023-08-03 09:49:11 at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
2023-08-03 09:49:11 at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
2023-08-03 09:49:11 at Npgsql.NpgsqlCommand.ExecuteNonQuery()
2023-08-03 09:49:11 at Npgsql.Bulk.NpgsqlBulkUploader.ExecuteNonQuery(NpgsqlConnection connection, String command)
2023-08-03 09:49:11 at Npgsql.Bulk.NpgsqlBulkUploader.Insert[T](IEnumerable`1 entities, InsertConflictAction onConflict)
Could some UUID be used instead of simply concatenating the table and column names?