PostgreSQL

The DEFAULT in PostgreSQL allows you to set a default value for a column in a table. When you insert a new row into the table without specifying a value for a column, PostgreSQL automatically uses the default value defined for that column. This feature is useful for ensuring that a column always has a valid value, even if the user or application does not provide one. Below is a guide on using the DEFAULT constraint with practical examples for a banking system, including tables for customers, accounts, and transactions.

Using DEFAULT Constraint in PostgreSQL

1. Basic Syntax for DEFAULT Constraint

   CREATE TABLE table_name (
       column_name data_type DEFAULT default_value
   );
   

   • table_name: The name of the table.
   • column_name: The name of the column where the default value is set.
   • data_type: The data type of the column.
   • default_value: The value to be used if no value is provided during insertion.

   Example: Creating a customers Table with DEFAULT Constraints

   CREATE TABLE customers (
       customer_id SERIAL PRIMARY KEY,
       first_name VARCHAR(50) NOT NULL,
       last_name VARCHAR(50) NOT NULL,
       email VARCHAR(100) UNIQUE,
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Automatically sets the current timestamp
   );
   

2. DEFAULT Constraints with Different Data Types
   • Numeric Types: Default values can be numbers or calculations.
   • String Types: Default values can be text strings.
   • Date/Time Types: Default values can be dates or timestamps.

   Example: Creating an accounts Table with Various DEFAULT Values

   CREATE TABLE accounts (
       account_id SERIAL PRIMARY KEY,
       customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
       account_type VARCHAR(20) DEFAULT 'checking',  -- Default to 'checking' if not specified
       balance DECIMAL(15, 2) DEFAULT 0.00,  -- Default balance is 0.00
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Default to current timestamp
   );
   

3. Adding DEFAULT Constraints to Existing Tables
   • You can add a DEFAULT constraint to an existing column using ALTER TABLE.
   • Syntax:

   ALTER TABLE table_name
   ALTER COLUMN column_name SET DEFAULT default_value;
   

   Example: Adding a DEFAULT Constraint to transactions Table

   ALTER TABLE transactions
   ALTER COLUMN description SET DEFAULT 'No description provided';
   

4. Removing DEFAULT Constraints
   • To remove a DEFAULT constraint, you can set the column to DROP DEFAULT.
   • Syntax:

   ALTER TABLE table_name
   ALTER COLUMN column_name DROP DEFAULT;
   

   Example: Removing DEFAULT Constraint from accounts Table

   ALTER TABLE accounts
   ALTER COLUMN account_type DROP DEFAULT;
   

5. Using DEFAULT with Expressions
   • You can use expressions to set a default value. For example, you can use functions or calculations.
   • Syntax:

   CREATE TABLE table_name (
       column_name data_type DEFAULT expression
   );
   

   Example: Creating transactions Table with Expression-Based DEFAULT Values

   CREATE TABLE transactions (
       transaction_id SERIAL PRIMARY KEY,
       account_id INTEGER NOT NULL REFERENCES accounts(account_id),
       transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       amount DECIMAL(15, 2) DEFAULT 0.00,
       description TEXT DEFAULT 'No description provided'
   );
   

6. Considerations When Using DEFAULT Constraints
   • Consistency: Ensure default values are appropriate for the column's data and business logic.
   • Application Design: Use defaults to simplify data insertion and avoid explicit NULL handling.
   • Database Integrity: Verify that default values meet data integrity requirements.

This guide should help beginners understand and effectively use the DEFAULT constraint in PostgreSQL. By setting appropriate default values, you can simplify data management and ensure that your database handles missing data gracefully.

To gain complete access, login with gmail or outlook, no need of signup. click here