Обсуждение: Altering a column if it exists
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
Thara Vadakkeveedu <tharagv@yahoo.com> writes: > 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$$; If you dig down through all the Java noise, the problem reported by the database server is: > org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string So apparently something on the client side is splitting this into more than one command to be sent to the server; it looks like that something thinks the first semicolon terminates the command, even though it's inside a quoted string. Most likely, that code doesn't understand dollar-quoting at all. This might be the fault of the JDBC driver, if you're using an old one; I'm not sure when that code got taught about dollar-quoted strings. It could be something further up the stack, though, too. If you can't identify and fix the culprit, you could switch to using a plain string literal for the DO (and then doubling all the quote marks inside the literal...) regards, tom lane
Tom Lane-2 wrote >> END$$; > > If you dig down through all the Java noise, the problem reported by the > database server is: > >> org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted >> string > > So apparently something on the client side is splitting this into more > than one command to be sent to the server; it looks like that something > thinks the first semicolon terminates the command, even though it's inside > a quoted string. Most likely, that code doesn't understand dollar-quoting > at all. More likely it is the fact that you do not have a space between "END" and the "$$". In certain situations there can be a problem using dollar-quoting with JDBC - mainly in regards to JDBC-compliant escaping but I do not recall the specifics at the moment - but a straight literal $$...$$ block works just fine. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Altering-a-column-if-it-exists-tp5785924p5785949.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
David Johnston wrote > > Tom Lane-2 wrote >>> END$$; >> >> If you dig down through all the Java noise, the problem reported by the >> database server is: >> >>> org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted >>> string >> >> So apparently something on the client side is splitting this into more >> than one command to be sent to the server; it looks like that something >> thinks the first semicolon terminates the command, even though it's >> inside >> a quoted string. Most likely, that code doesn't understand >> dollar-quoting >> at all. > More likely it is the fact that you do not have a space between "END" and > the "$$". > > In certain situations there can be a problem using dollar-quoting with > JDBC - mainly in regards to JDBC-compliant escaping but I do not recall > the specifics at the moment - but a straight literal $$...$$ block works > just fine. > > David J. Not sure what I was thinking here but the space-issue cannot be right though the "END" does need a semi-colon to be valid pl/pgsql. Normally after the semi-colon you would have a newline and then the closing dollar-quotes. That layout is what I was looking for but mis-translated it into a white-space error which is obviously wrong since $$somevalue$$ is indeed valid. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Altering-a-column-if-it-exists-tp5785924p5785954.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
David Johnston <polobo@yahoo.com> writes: > Not sure what I was thinking here but the space-issue cannot be right though > the "END" does need a semi-colon to be valid pl/pgsql. Normally after the > semi-colon you would have a newline and then the closing dollar-quotes. > That layout is what I was looking for but mis-translated it into a > white-space error which is obviously wrong since $$somevalue$$ is indeed > valid. I think years ago we required a semicolon after the last END of a plpgsql function body, but we don't anymore. In any case, the error is being thrown before the plpgsql parser ever gets to look at the string, so whether that's true or not isn't the immediate problem. At this point I'm betting the OP is using an old JDBC driver version that doesn't understand dollar-quoted strings, or that there's some layer above it in his client software stack that doesn't understand them (but thinks it knows how to pull apart multi-statement SQL strings). A quick look into the pgsql-jdbc archives says that dollar quote parsing was implemented in 2006, with some bug fixes (for cases this example wouldn't hit) in late 2007. I don't know how that relates to JDBC driver version numbers unfortunately, but certainly anything less than about five years old ought to be OK. regards, tom lane