I have a simple function (tested on Postgres 9.4) that I need to implement on Postgres 8.4. The error I get is that function format does not exist. I've been trying to find resources but 8.4 is 6 years old at this stage and it's hard to find relevant information apart from the docs!

    rows RECORD;

    FOR rows IN SELECT column as tablename from table
            EXECUTE format('
               CREATE TABLE IF NOT EXISTS %I (
                date date,
                number integer
               )', rows.tablename);

        END LOOP ;

Here's the error message:

ERROR:  function format(unknown, text) does not exist
LINE 1: SELECT  format('
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Any suggestions would be greatly appreciated!

EDIT: I've amended the loop in my function to this:

            EXECUTE 'CREATE TABLE IF NOT EXISTS ' || rows || ' (
                date date,
                number integer
               )', rows.tablename;

        END LOOP ;

However now I'm getting this error on quote_ident():

ERROR:  function quote_ident(record) does not exist
  • 1
    Well, the format( is missing from your post - otherwise, you have to concatenate and quote with quote_ident(). But the best suggestion would be upgrading ;) Jul 10, 2015 at 10:51
  • Sorry, fixed that there! Unfortunately upgrading is not an option. I'll try your suggestion now.
    – Dan
    Jul 10, 2015 at 10:55
  • I cannot understand why, but now I'm getting an error saying "ERROR: function quote_ident(record) does not exist"! I've edited my question to include how I've changed the function so far.
    – Dan
    Jul 10, 2015 at 11:14
  • Just out of curiosity, why cannot you upgrade? Jul 10, 2015 at 12:44

1 Answer 1


The pre-format solution used to be the good old concatenation, made safe by using quote_ident().

In your case, it would look like

    EXECUTE $$ 
        CREATE TABLE IF NOT EXISTS $$ || quote_ident(rows.tablename) || $$ (
             date date,
             number integer
        ) $$;

Note that here I am using dollar quoting when building the dynamic query. This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example). This way most editors will highlight the statements nicely.

Another shortcoming of version 8.4 is that the CREATE TABLE IF NOT EXISTS syntax does not exist there - it was introduced with 9.1. You can still do a test for it:

IF EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relname = 'rows.tablename' AND nspname = 'your_schema')
    -- do the EXECUTE

If you have only one schema around, you can omit the join to pg_namespace. pg_class and pg_namespace are part of the system catalogs - you may want to check out what else can be found there.

  • Thanks a million. I hadn't seen this syntax anywhere. This is now correctly beginning the loop, however now I'm getting an even weirder error than before! [Err] ERROR: syntax error at or near "NOT" LINE 2: CREATE TABLE IF NOT EXISTS
    – Dan
    Jul 10, 2015 at 13:26
  • According to postgresql.org/docs/8.4/static/sql-createtable.html, the IF NOT EXISTS syntax didn't exist in 8.4 (appeared in 9.1) Jul 10, 2015 at 13:34
  • Thanks once again. I wish I could use up to date versions!
    – Dan
    Jul 10, 2015 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.