Vbulletin bulk importer: null value in column "pinned_globally" of relation "topics" violates not-null constraint

[pfaffman (Jay Pfaffman)]

Aha. That explains why it’s not in the code, I think.

But it doesn’t solve the mystery.

pinned_globally    | boolean   |     | not null | false

EDIT:

Random AI says:

• Bulk Inserts or Data Ingestion: If you use bulk insert tools or frameworks that always specify all columns (even with their default values), PostgreSQL will not use the column’s default, but rather the value provided by the tool710.

Perhaps this a “bulk insert tool”?

When using the COPY command in PostgreSQL, which is what put_copy_data in Ruby interfaces with, the command does not automatically apply default values defined in the table schema for columns that are not explicitly provided in the data being copied. This means that if you are using put_copy_data to import data and you do not provide a value for a column that has a default value, the default value will not be applied. Instead, the column will be set to NULL unless you explicitly provide a value for it in the data being copied.

I am unable to find a source that credibly says what the above quote does, but it does make sense, but in that the point of this is to go fast, so skipping defaults seems like a thing that it would do, and it does explain what is happending, and, I hope, the solution that I am still waiting to see if it worked.

Here is actual documentation! PostgreSQL: Documentation: 18: COPY

If a column list is specified, COPY TO copies only the data in the specified columns to the file. For COPY FROM , each field in the file is inserted, in order, into the specified column. Table columns not specified in the COPY FROM column list will receive their default values.

So, if I’m reading that right, if a field is in the field list, then postgres blindly copies what you give it, and blank/null gets inserted instead of the desired default.

It’s going slower and slower. Is there a reason not to use LIMIT 1000 like the regular importer does? Seems like maybe 885K topics is a lot to bite off at once?