DEFAULT values and expressions in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the DEFAULT keyword in PostgreSQL?

The DEFAULT keyword sets a predefined value for a column when no value is provided during an INSERT operation.

intermediate

How can you use an expression as a DEFAULT value in PostgreSQL?

You can use expressions like functions or calculations as DEFAULT values, for example: DEFAULT CURRENT_TIMESTAMP or DEFAULT (10 * 0.9).

beginner

True or False: DEFAULT values are applied when the INSERT statement omits the column.

True. DEFAULT values are used if the INSERT statement does not specify a value for that column.

beginner

How do you define a DEFAULT value for a column in a CREATE TABLE statement?

Use the syntax: column_name data_type DEFAULT default_value, for example: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP.

intermediate

Can DEFAULT values be changed after table creation? If yes, how?

Yes, use ALTER TABLE to change DEFAULT values, for example: ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT new_value;

What happens if you insert a row without specifying a value for a column that has a DEFAULT value?

A The column value becomes NULL.

B The DEFAULT value is used for that column.

C The insert fails with an error.

D The column value is set to zero.

If a column has a DEFAULT value and no value is provided during insert, PostgreSQL uses the DEFAULT value.

Which of the following is a valid DEFAULT expression in PostgreSQL?

A DEFAULT NULL

B DEFAULT 'today'

C DEFAULT NOW() + '5'

D DEFAULT CURRENT_DATE

CURRENT_DATE is a valid function used as a DEFAULT expression. NOW() + '5' is invalid because you cannot add text directly to timestamp without casting.

How do you remove a DEFAULT value from a column?

A ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

B ALTER TABLE table_name DROP COLUMN column_name;

C UPDATE table_name SET column_name = NULL;

D DELETE DEFAULT FROM column_name;

To remove a DEFAULT value, use ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT.

Can a DEFAULT value be a subquery in PostgreSQL?

A Yes, any subquery can be used.

B Only if the subquery returns a single value.

C No, DEFAULT cannot be a subquery.

D Only if the subquery is in a function.

DEFAULT values cannot be subqueries; they must be constant expressions or functions.

Which statement correctly sets a DEFAULT value for a column after table creation?

A ALTER TABLE mytable ALTER COLUMN mycol SET DEFAULT 100;

B ALTER TABLE mytable SET DEFAULT mycol = 100;

C UPDATE mytable SET DEFAULT mycol = 100;

D ALTER TABLE mytable ADD DEFAULT mycol 100;

The correct syntax to set a DEFAULT value after creation is ALTER TABLE ... ALTER COLUMN ... SET DEFAULT.

Explain how DEFAULT values work in PostgreSQL and give an example of using an expression as a DEFAULT.

Think about what happens when you insert a row without specifying all columns.

Describe how to change or remove a DEFAULT value on an existing table column.

Focus on ALTER TABLE syntax for modifying columns.