Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as:

    tblVar varchar := "myTable";
             foo integer NOT NULL, 
             bar varchar NOT NULL)'
 USING _tblVar;

However, I continue to receive the error message

ERROR: syntax error at or near "$1"

If I don't use the $1 token and, instead, write the string myTable it works just fine.

Is there a limitation on using dynamic statements for CREATE calls?

  • I think I've figured it out: EXECUTE 'CREATE TABLE ' || _tblVar || ' ( foo integer NOT NULL, bar varchar NOT NULL)';
    – Jmoney38
    Sep 6, 2011 at 20:46
  • Thats right - execute ... using can only use substitutions where you could normally have bind variables - ie not for table names etc Sep 6, 2011 at 20:50
  • 2
    Use quote_ident() to avoid SQL injection and other problems with dynamic object names. You might need lower() as well, to create only lower case objects. Sep 7, 2011 at 5:56
  • @Frank only if the table name is coming from an untrusted source, and then IMO he should do more than quote_ident - such as restrict to ~'^[a-z]{3,10}$' and add a prefix Sep 7, 2011 at 15:49
  • 1
    @Jack: It's a variable so you have to protect your database against major problems. The example already shows issues with casing, myTable is going to be mytable in lower case. quote_ident works fine, no restrictions needed. A maximum length might be handy, 63 characters is the max. Sep 7, 2011 at 16:09

3 Answers 3


In addition to what @filiprem wrote, this is how you do it properly:

   tbl_var text := 'myTable';   -- I would not use mixed case names ..
CREATE TABLE ' || quote_ident(tbl_var) || '( 
   foo integer NOT NULL, 
   bar text NOT NULL)';

Use quote_ident() (or format()) to defend against SQL injection and syntax errors. It double-quotes identifiers with non-standard characters or reserved words.

I also replaced the double-quotes you had around the string value in your example with single-quotes.



Yes, there is such limitation. You cannot use parameters for table/column names - that's because Postgres needs to be able to parse query on compiling the dynamic SQL statement. Parser must be able to identify used relations.

Quote from PL/pgSQL docs about dynamic SQL commands:

Note that parameter symbols can only be used for data values — if you want to use dynamically determined table or column names, you must insert them into the command string textually. For example, if the preceding query needed to be done against a dynamically selected table, you could do this:

    || tabname::regclass
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

As noted in comments below, the cast method is not always feasible, especially for CREATE statements. Consider format function, for example:

EXECUTE format(
  'CREATE TABLE %I (%I %I, %I %I)',
  v_col1name, v_col1type,
  v_col2name, v_col2type);

Side note: probably this limitation applies to dynamic SQL in other DBMS, including Oracle: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDHGHIF

  • 1
    For a CREATE statement you cannot use the cast tabname::regclass in the example because, obviously, the table does not exist, yet. Jun 21, 2012 at 13:18

In the code above, you are using $1 which is an argument which you got as a parameter from the function. In this case, you can't use $1 inside the string. So, the code should be like this

EXECUTE 'CREATE TABLE ' || $1 || ' ( 
         foo integer NOT NULL, 
         bar varchar NOT NULL)'

In here || this mark indicates that joining a string like + sign

  • Sorry, no, $1 is not a parameter in PL/pgSQL. Apr 3, 2020 at 7:41
  • 1
    then, may I know what is that
    – Mehdi
    Apr 3, 2020 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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