Altering a column if it exists

From: Thara Vadakkeveedu <tharagv(at)yahoo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Altering a column if it exists
Date: 2014-01-08 21:24:58
Message-ID: 1389216298.48861.YahooMailNeo@web125003.mail.ne1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

The following piece of code throws a plsql exception when run through jdbc... 
DO $$
               
BEGIN
                               
IF EXISTS (SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name
= 'position' and column_name='org_role_id')
                               
THEN
                                               
ALTER TABLE POSITION RENAME COLUMN org_role_id TO job_id;
                               
END IF;
END$$;

SEVERE: Exception sending
context initialized event to listener instance of class
org.springframework.web.context.ContextLoaderListener
org.springframework.beans.factory.BeanCreationException:
Error creating bean with name
'org.springframework.jdbc.datasource.init.DataSourceInitializer#0': Invocation
of init method failed; nested exception is
org.springframework.dao.DataAccessResourceFailureException: Failed to execute
database script; nested exception is
org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed
to execute SQL script statement at line 3 of resource class path resource
[db-upgradeSchema.sql]: DO $$ BEGIN IF EXISTS (SELECT COLUMN_NAME FROM
information_schema.columns WHERE table_name = 'position' and
column_name='org_role_id') THEN ALTER TABLE POSITION RENAME COLUMN org_role_id
TO job_id
               
at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1548)
...
...
...
Caused by:
org.springframework.dao.DataAccessResourceFailureException: Failed to execute
database script; nested exception is
org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed
to execute SQL script statement at line 3 of resource class path resource
[db-upgradeSchema.sql]: DO $$ BEGIN IF EXISTS (SELECT COLUMN_NAME FROM
information_schema.columns WHERE table_name = 'position' and
column_name='org_role_id') THEN ALTER TABLE POSITION RENAME COLUMN org_role_id
TO job_id
               
at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:56)
...
...
Caused by:
org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed
to execute SQL script statement at line 3 of resource class path resource
[db-upgradeSchema.sql]: DO $$ BEGIN IF EXISTS (SELECT COLUMN_NAME FROM
information_schema.columns WHERE table_name = 'position' and
column_name='org_role_id') THEN ALTER TABLE POSITION RENAME COLUMN org_role_id
TO job_id
               
at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:202)
 ...
 ...
org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:47)
               
... 28 more
Caused by:
org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string at
or near "$$ BEGIN IF EXISTS (SELECT COLUMN_NAME FROM
information_schema.columns WHERE table_name = 'position' and
column_name='org_role_id') THEN ALTER TABLE POSITION RENAME COLUMN org_role_id
TO job_id"
  Position: 4
               
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
               
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
               
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
               
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
               
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
               
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
               
at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
               
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
               
at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
               
at
org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:187)

Is there  a way to do this ?
thanks,
TG

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2014-01-08 21:34:12 Re: Altering a column if it exists
Previous Message Chris Campbell 2014-01-08 20:23:52 Re: Time-Out Issue