Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
I think I've figured it out: EXECUTE 'CREATE TABLE ' || _tblVar || ' ( foo integer NOT NULL, bar varchar NOT NULL)'; – Jmoney38 Sep 6 '11 at 20:46
Thats right - execute ... using can only use substitutions where you could normally have bind variables - ie not for table names etc – Jack Douglas Sep 6 '11 at 20:50
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. – Frank Heikens Sep 7 '11 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 – Jack Douglas Sep 7 '11 at 15:49
@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. – Frank Heikens Sep 7 '11 at 16:09

In addition to what @filiprem wrote, here is how you do this 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() to avoid SQL injection or syntax errors. It will quote names 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.

share|improve this answer


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;

In other words:

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

Side note: probably this limitation applies to dynamic SQL in other DBMS, including Oracle:

share|improve this answer
For a CREATE statement you cannot use the cast tabname::regclass in the example because, obviously, the table does not exist, yet. – Erwin Brandstetter Jun 21 '12 at 13:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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