DEFAULT values and expressions in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with a column that has a default value of 100.

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, stock INTEGER DEFAULT [1]);

Drag options to blanks, or click blank then click option'

ANULL

B'100'

C100

DDEFAULT 100

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the number 100 in quotes, which makes it a string.

Using the word DEFAULT inside the DEFAULT clause again.

The DEFAULT value should be a number without quotes for an integer column.

2fill in blank

medium

Complete the code to set the default value of a timestamp column to the current time.

PostgreSQL

CREATE TABLE events (id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT [1]);

Drag options to blanks, or click blank then click option'

ACURRENT_DATE

BNOW()

CCURRENT_TIME

D'NOW()'

Attempts:

3 left

💡 Hint

Common Mistakes

Using CURRENT_DATE which returns only the date without time.

Putting NOW() in quotes, making it a string.

NOW() returns the current date and time, suitable for a TIMESTAMP default.

3fill in blank

hard

Fix the error in the code to set a default value for a column that stores the current year.

PostgreSQL

CREATE TABLE reports (id SERIAL PRIMARY KEY, year INTEGER DEFAULT [1]);

Drag options to blanks, or click blank then click option'

ANOW()

BYEAR(NOW())

CCURRENT_YEAR

DEXTRACT(YEAR FROM NOW())

Attempts:

3 left

💡 Hint

Common Mistakes

Using YEAR() which is not valid in PostgreSQL.

Using NOW() directly for an integer column.

PostgreSQL uses EXTRACT(YEAR FROM timestamp) to get the year as a number.

4fill in blank

hard

Fill both blanks to create a table where a column has a default value of the current date plus 7 days.

PostgreSQL

CREATE TABLE tasks (id SERIAL PRIMARY KEY, due_date DATE DEFAULT [1] + INTERVAL '[2]');

Drag options to blanks, or click blank then click option'

ACURRENT_DATE

B7 days

C7 day

DNOW()

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOW() which includes time, not just date.

Writing '7 days' with an 's' which is not accepted by PostgreSQL.

CURRENT_DATE returns today's date, and adding INTERVAL '7 day' adds 7 days to it.

5fill in blank

hard

Fill all three blanks to create a table with a column that defaults to the uppercase username or 'GUEST' if none is provided.

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, username TEXT DEFAULT COALESCE(UPPER([1]), [2]) [3]);

Drag options to blanks, or click blank then click option'

Ausername

B'GUEST'

CNOT NULL

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Putting username in quotes, making it a string literal.

Using NOT NULL which conflicts with COALESCE default.

COALESCE returns the uppercase username or 'GUEST' if username is NULL. The column allows NULLs.