A table column in YSQL can have a default value.
If an INSERT statement does not specify a value for the column, then the default value is used. If no default is specified for a column, then the default is NULL.
Example:
yugabyte=# CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR DEFAULT 'A');
CREATE TABLE
yugabyte=# INSERT INTO test (c1) VALUES (1);
INSERT 0 1
yugabyte=# INSERT INTO test (c1) VALUES (1), (2), (3);
INSERT 0 3
yugabyte=# SELECT * FROM test ORDER BY c1;
c1 | c2
----+----
1 | A
2 | A
3 | A
(3 rows)
We can also ALTER a table to add a new column that has a DEFAULT value.
yugabyte=# ALTER TABLE test ADD COLUMN c3 VARCHAR DEFAULT 'X';
ALTER TABLE
yugabyte=# SELECT * FROM test ORDER BY c1;
c1 | c2 | c3
----+----+----
1 | A |
2 | A |
3 | A |
(3 rows)
Note that current rows were not updated automatically with the default value.
Todo that you’ll need to run an UPDATE statement setting the new column equal to its DEFAULT value.
yugabyte=# UPDATE test SET c3 = DEFAULT;
UPDATE 3
yugabyte=# SELECT * FROM test ORDER BY c1;
c1 | c2 | c3
----+----+----
1 | A | X
2 | A | X
3 | A | X
(3 rows)
P.S. A new feature is coming to YugabyteDB that will eliminate the need for the additional update. I will update this tip when the feature GA!
Have Fun!
