NUHMAN.com

Article:

In Oracle Database 12c and later, the GENERATED BY DEFAULT AS IDENTITY clause provides a cleaner alternative to sequence + trigger-based auto-increment fields. But what if you need to add or update a column to use this identity feature in an existing table?

This article explains:

• How to update a column to use identity
• Examples of adding or altering identity columns
• Related utility functions to check for numeric values in both PL/SQL and PostgreSQL

🔧 Step-by-Step: Updating a Column to Use GENERATED BY DEFAULT AS IDENTITY

Oracle doesn’t allow altering an existing column in place to add the identity property. So the general process is:

✅ Option 1: Add a New Identity Column

sql

ALTER TABLE employees
ADD employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY;

This is the simplest way when the column doesn’t already exist.

✅ Option 2: Modify an Existing Column (Drop and Recreate)

If the column already exists and you want to make it an identity column:

sql

-- Step 1: Drop the existing column (backup data if needed)
ALTER TABLE employees
DROP COLUMN employee_id;

-- Step 2: Add the column with identity
ALTER TABLE employees
ADD employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY;

Notes:

• Drop constraints before dropping the column (like PRIMARY KEY).
• Make sure you back up any critical data before doing this.

🔎 PL/SQL Utility: Function to Check if a String is Numeric

Here’s a simple is_numeric function in Oracle PL/SQL:

sql

CREATE OR REPLACE FUNCTION is_numeric(p_string IN VARCHAR2) RETURN BOOLEAN IS
  l_number NUMBER;
BEGIN
  BEGIN
    l_number := TO_NUMBER(p_string);
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END;
END is_numeric;
/

Usage:

sql

BEGIN
  IF is_numeric('12345') THEN
    DBMS_OUTPUT.PUT_LINE('It is numeric');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not numeric');
  END IF;
END;

🐘 PostgreSQL Version of is_numeric

Here’s the same idea, implemented in PostgreSQL using PL/pgSQL:

sql

CREATE OR REPLACE FUNCTION is_numeric(p_string TEXT) RETURNS BOOLEAN AS $$
DECLARE
    l_number NUMERIC;
BEGIN
    BEGIN
        l_number := p_string::NUMERIC;
        RETURN TRUE;
    EXCEPTION
        WHEN others THEN
            RETURN FALSE;
    END;
END;
$$ LANGUAGE plpgsql;

Usage:

sql

SELECT is_numeric('123.45');   -- TRUE
SELECT is_numeric('ABC123');   -- FALSE

✅ Summary

• Use ALTER TABLE to add identity columns. If the column already exists, you’ll need to drop and recreate it.
• The GENERATED BY DEFAULT AS IDENTITY feature simplifies auto-increment behavior in Oracle.
• Bonus: Utility functions like is_numeric can help validate data before transformations, both in PL/SQL and PostgreSQL.