Re: Altering a column if it exists

Thara Vadakkeveedu <tharagv(at)yahoo(dot)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