June 18th, 2008 by depesz | Tags: , , , , , | 7 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

Every now and then I see people ask the question – how to create table if it doesn't exist yet, how to drop it, but only if it does exist and so on.

Well, starting from 8.2 dropping should be not a problem anymore. But what about create? Alter?

Let's try to do it…

First let's state the goal:

  • we want to have the ability to create object that will not raise exception/rollback in case the object already exists
  • we want to have the ability to alter object (add column to table for example) that will not raise exception/rollback in case the object doesn't exist

Now, having the goal defined, let's do some work.

First thing to be done is function that will make adding conditions possible:

CREATE OR REPLACE FUNCTION execute(TEXT) RETURNS VOID AS $$
BEGIN EXECUTE $1; END;
$$ LANGUAGE plpgsql STRICT;

Whoa, that was a big piece of code.

What this function does? Well, it basically evaluates given string as SQL. Test:

# select execute('create table depesz (x int4)');
execute
---------
 
(1 row)
# \d depesz
Table "public.depesz"
Column | Type | Modifiers
--------+---------+-----------
x | integer |

OK. Remember that SQL can contain literal new-line-characters, so if your .sql file contains:

CREATE TABLE some_important_table (
id SERIAL,
important_field TEXT NOT NULL DEFAULT '',
some_date DATE NOT NULL DEFAULT now(),
client_id INT4 NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);

You can simply add “SELECT execute($$" in the line before, and “$$);" afterwards:

SELECT execute($$
CREATE TABLE some_important_table (
id SERIAL,
important_field TEXT NOT NULL DEFAULT '',
some_date DATE NOT NULL DEFAULT now(),
client_id INT4 NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
$$);

And it will work.

OK. So, now we have a way to wrap our DDL into function call. But what good it is? It's simple – now I can add “WHERE" to the function call:

# SELECT execute($$CREATE TABLE xxx (i INT4)$$) WHERE NOT exists (SELECT * FROM information_schema.tables WHERE table_name = 'xxx');
execute
---------
 
(1 row)
# \d xxx
Table "public.xxx"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
# SELECT execute($$CREATE TABLE xxx (i INT4)$$) WHERE NOT exists (SELECT * FROM information_schema.tables WHERE table_name = 'xxx');
execute
---------
(0 rows)

You should notice that the second time I called execute – it returned 0 rows. So there is a way to distinguish whether it did the job (create table in this example) or not. But the exception was not raised.

Now, that's pretty cool, but writing these “where not exists (select ..)" gets old pretty fast. So, let's make it a bit simpler. Let's add some helper functions:

CREATE OR REPLACE FUNCTION schema_exists(TEXT) RETURNS bool as $$
SELECT exists(SELECT 1 FROM information_schema.schemata WHERE schema_name = $1);
$$ language sql STRICT;

CREATE OR REPLACE FUNCTION table_exists(TEXT, TEXT) RETURNS bool as $$
SELECT exists(SELECT 1 FROM information_schema.tables WHERE (table_schema, table_name, table_type) = ($1, $2, 'BASE TABLE'));
$$ language sql STRICT;

CREATE OR REPLACE FUNCTION view_exists(TEXT, TEXT) RETURNS bool as $$
SELECT exists(SELECT 1 FROM information_schema.views WHERE (table_schema, table_name) = ($1, $2));
$$ language sql STRICT;

CREATE OR REPLACE FUNCTION column_exists(TEXT, TEXT, TEXT) RETURNS bool as $$
SELECT exists(SELECT 1 FROM information_schema.columns WHERE (table_schema, table_name, column_name) = ($1, $2, $3));
$$ language sql STRICT;

I think the functions don't need big descriptions – what they do is rather simple. So simple, that you can easily modify them to suit your needs better, or simply add new ones if you will find something missing (like index_exists).

And how do I use it now?

# SELECT execute($$CREATE TABLE yyy (i INT4)$$) WHERE NOT table_exists('public', 'yyy');
execute
---------
 
(1 row)
# SELECT execute($$CREATE TABLE yyy (i INT4)$$) WHERE NOT table_exists('public', 'yyy');
execute
---------
(0 rows)

Of course it's not perfect. Built-in syntax to handle it would be much better, but I think it is pretty acceptable for a quick hack :)

  1. 7 comments

  2. Jun 19, 2008

    NOT NULL DEFAULT ” is MySQL cruft. Get rid of it. :)

  3. Jun 19, 2008

    @David Fetter:
    Well, I’m not really MySQL user, so it’s hard for me to say it’s something I got from it.

  4. # wulczer
    Jun 26, 2008

    You could also wrap the EXECUTE in a BEGIN / EXCEPTION WHEN OTHERS NULL; END block and call your creates and alters through that.

  5. Jun 26, 2008

    @wulczer:
    technically – yes. but in this way it would silently ignore other errors as well – like “bad data type”, bad syntax and so on.

  6. # wulczer
    Jun 27, 2008

    OK, so instead of EXCEPTION WHEN OTHERS you’d use WHEN duplicate_table OR duplicate_function OR …

  7. # gregj
    Jul 9, 2008

    David, so what’s wrong with “NOT NULL DEFAULT ” “?? I don’t get it.

  8. Aug 26, 2008

    One single thing is not possible: you can’t add the plpgsql language itself if you don’t know if it’s already installed or not.
    I wrote a small workaround for this little problem:
    http://andreas.scherbaum.la/blog/archives/346-create-language-if-not-exist.html

Leave a comment