if a table have default value on a column for e.g.
create table emp
(
flag smallint default 1
)
so is there any impact of this default column in bulk import , if I am not using in insert statement.
2 Answers
According to the docs:
Before 11:
Adding a column with a default requires updating each row of the table (to store the new column value). However, if no default is specified, PostgreSQL is able to avoid the physical update. So if you intend to fill the column with mostly nondefault values, it's best to add the column with no default, insert the correct values using UPDATE, and then add any desired default as described below.
11 and after:
From PostgreSQL 11, adding a column with a constant default value no longer means that each row of the table needs to be updated when the ALTER TABLE statement is executed. Instead, the default value will be returned the next time the row is accessed, and applied when the table is rewritten, making the ALTER TABLE very fast even on large tables. However, if the default value is volatile (e.g., clock_timestamp()) each row will need to be updated with the value calculated at the time ALTER TABLE is executed. To avoid a potentially lengthy update operation, particularly if you intend to fill the column with mostly nondefault values anyway, it may be preferable to add the column with no default, insert the correct values using UPDATE, and then add any desired default as described below.
2 Comments
Pedro Rolo
this is pleasant stuff
If you take a look into the source for the generator/planner/optimizer/rewriter
( postgresql/src/backend/rewrite/rewriteHandler.c around line#1112, function build_column_default() ) :
The default value(or function, e.g. for serials) is fetched from the catalogs and added once to the query tree. So, the DEFAULT may even be more efficient than fetching separate values for all affected rows from one of the tables in query.
But you would have to measure the difference to be sure.
1or anullwhen creating a row either way so I doubt there would be much impact from an IO perspective, and performance wise that's typically the lion's share of the work.