Re: Altering a column if it exists

Thara Vadakkeveedu  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