Altering a column if it exists
| От | Thara Vadakkeveedu |
|---|---|
| Тема | Altering a column if it exists |
| Дата | |
| Msg-id | 1389216298.48861.YahooMailNeo@web125003.mail.ne1.yahoo.com обсуждение исходный текст |
| Ответы |
Re: Altering a column if it exists
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | 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
В списке pgsql-novice по дате отправления: