2 added 13 characters in body
source | link

I'm adding 2 new columns on big table. Those 2 new columns have no default value and no constraint. I also want to add a composite partial index on those 2 new columns.

CREATE UNIQUE INDEX CONCURRENTLY index_users_on_col1_and_col2 ON public.users USING btree (col1, col2) WHERE ((col1 IS NOT NULL) AND (col2 IS NOT NULL));

I need that to be done concurrently to avoid writes lock. The 2 new columns will start having value only a few days after I add them and I add the index.

So basically the index will be empty when creating.

Is there a way to create the index by "saying" to PostgreSQL it should create the index directly empty and not scan the table?

I'm adding 2 new columns on big table. Those 2 new columns have no default value and no constraint. I also want to add a composite partial index on those 2 new columns.

CREATE UNIQUE INDEX index_users_on_col1_and_col2 ON public.users USING btree (col1, col2) WHERE ((col1 IS NOT NULL) AND (col2 IS NOT NULL));

I need that to be done concurrently to avoid writes lock. The 2 new columns will start having value only a few days after I add them and I add the index.

So basically the index will be empty when creating.

Is there a way to create the index by "saying" to PostgreSQL it should create the index directly empty and not scan the table?

I'm adding 2 new columns on big table. Those 2 new columns have no default value and no constraint. I also want to add a composite partial index on those 2 new columns.

CREATE UNIQUE INDEX CONCURRENTLY index_users_on_col1_and_col2 ON public.users USING btree (col1, col2) WHERE ((col1 IS NOT NULL) AND (col2 IS NOT NULL));

I need that to be done concurrently to avoid writes lock. The 2 new columns will start having value only a few days after I add them and I add the index.

So basically the index will be empty when creating.

Is there a way to create the index by "saying" to PostgreSQL it should create the index directly empty and not scan the table?

1
source | link

Creating very quickly an composite partial index on 2 new columns with null values

I'm adding 2 new columns on big table. Those 2 new columns have no default value and no constraint. I also want to add a composite partial index on those 2 new columns.

CREATE UNIQUE INDEX index_users_on_col1_and_col2 ON public.users USING btree (col1, col2) WHERE ((col1 IS NOT NULL) AND (col2 IS NOT NULL));

I need that to be done concurrently to avoid writes lock. The 2 new columns will start having value only a few days after I add them and I add the index.

So basically the index will be empty when creating.

Is there a way to create the index by "saying" to PostgreSQL it should create the index directly empty and not scan the table?