![]() We know that all the rows in that table should have the new default value but we know also that the table was not rewritten. The column we added has that set in pg_attribute: ![]() ![]() We can see that when we check for our current table. "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)Īs soon as a new column with a non null default value is added to a table these columns get populated. "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname) The catalog table pg_attribute got two new columns called “attmissingval” and “atthasmissing”:Ĭolumn | Type | Collation | Nullable | Default val text NOT NULL or with val text CHECK (val <> '') An empty string needs slightly more storage than a NULL value, but that should not be the deciding factor.select from tableA where ( case when 'columna' is null then '' else 'columna' end 'yes') It's pretty faster as well. The question is how does that work in the background? Actually the idea is quite simple. you can use case when in where clause: its treat null values as an empty string. No sequential scan at all and it only took 5 ms for the alter table to complete. Postgres=# insert into test (a,b,c) select aa.*, md5(aa::text), now() from generate_series ( 1, 1000000 ) aa Postgres=# alter table test add column d text default 'a' Īs you can see a sequential scan happened when the alter table was performed and it took more than a second for the alter table to complete. Postgres=# select seq_scan from pg_stat_user_tables where relid = 'test'::regclass This gave us 1’000’000 rows and what I want to do is to check the amount of sequential scans against the table before and after the alter table. Postgres=# create table test ( a int, b text, c timestamp ) We start by creating a test table in PostgreSQL 10: If expression is NOT a NULL value, the condition evaluates to TRUE. The value to test whether it is a non-NULL value. With PostgreSQL 11 this is not anymore the case and adding a column in such a way is almost instant. The PostgreSQL IS NOT NULL condition is used to test for a NOT NULL value in a SELECT, INSERT, UPDATE, or. Up to PostgreSQL 10 when you add a column to table which has a non null default value the whole table needed to be rewritten. As I am currently preparing my session for the Swiss PGDay which is about some of the new features for PostgreSQL 11, I though this one is worth a blog post as well.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |