Mícéal Gallagher in P/sql 2 minutes

Postgres - Add/Create object if it doesn't already exist

The creation of database objects is fairly simple but the conditional creation of objects when they don’t exist can be a little convoluted with PostgreSQL.

Creating a table if it doesn’t exist

When creating a table, specify the IF NOT EXISTS key phrase.

```pgsqlCREATE TABLE IF NOT EXISTS customer (
    id      integer,
    name        varchar(100)
);
```

### Creating a table column when it doesn't exist

When adding a new column, we need to use the `DO` keyword and catch an error code which is specified after the `WHEN` keyword.

```defaultDO $$
    BEGIN
        BEGIN
            ALTER TABLE customer ADD COLUMN age int;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'age column already exists.';
        END;
    END;
$$
```