George George - 5 months ago 10
Node.js Question

add column only if table not exists

I am creating a column in a table:

client.query("ALTER TABLE mytable ADD theField text", function(err, result) {


Now, I want to perform a test ,so if the column already exists , don't execute the statement.

I tried:

client.query("ALTER TABLE mytable IF NOT EXISTS ADD theField text", function(err, result) {


client.query("ALTER TABLE mytable ADD theField text IF NOT EXISTS", function(err, result) {


client.query("ALTER TABLE mytable WHERE EXISTS (SELECT 1 FROM mytable ADD theField text)", function(err, result) {


but all giving syntax error.

Answer

your code ALTER TABLE mytable IF NOT EXISTS ADD theField text will add column thefield. if you want it to be called precisely theField you should run this: ALTER TABLE mytable IF NOT EXISTS ADD "theField" text This is why I check if column exists ignoring the case: lower(blaH-BLah) equal to lower(blAh-bLAh), cos it makes blah-bLah value. This is why your query should be:

client.query("do"+

   " $$"+
    "begin"+
     " if (select count(*) from information_schema.columns" +
      "     where table_schema = 'public' " +
       "    and table_name = 'mytable' "+
       "    and lower(column_name) = lower('theField')) < 1 " + 
      " then "+
      "ALTER TABLE mytable ADD \"theField\" text;"+
      "end if;"+
    "end;"+
    "$$"+
    ";", function(err, result)  {