All Questions
Tagged with alter-table null
8 questions
7
votes
1
answer
20k
views
Is it safe to use default value with not null when adding a new column?
We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below:
ALTER TABLE "blogs" ADD "published" ...
- 275
17
votes
2
answers
11k
views
How can I set a column to NOT NULL without locking the table during a table scan?
(former question was: Will Postgres use a multi-column index when setting multiple colums not null?)
Typically, when I set a column not null, if it doesn't have an index then I add it first, so that ...
- 867
15
votes
3
answers
3k
views
Increase speed altering column on large table to NON NULL
I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time ...
- 293
13
votes
1
answer
31k
views
How to set a NOT NULL column into NULL in PostgreSQL?
I'm new to PostgreSQL and that is why I'm asking this question. I have a table app_client_users and a column named client_id. Initially I set it as NOT NULL and now I would like to change it to allow ...
0
votes
2
answers
1k
views
How to get the second string in the ORA-02296 error to show something meaningful
Whenever I run the following script*:
ALTER TABLE ANIMAL.MONKEY MODIFY TAIL_COUNT NOT NULL;
I receive the following error:
ORA-02296: cannot enable (ANIMAL.) - null values found
I have a script ...
- 157
12
votes
2
answers
16k
views
Quickly change NULL column to NOT NULL
I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I ...
- 532
0
votes
1
answer
3k
views
H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not
I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition.
However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the ...
- 395
61
votes
5
answers
43k
views
Why does ALTER COLUMN to NOT NULL cause massive log file growth?
I have a table with 64m rows taking 4.3 GB on disk for its data.
Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text.
I added a a NULLABLE column with data-...
- 713