Problem with ampersands in index name

[bbigras]

I have some indexes with ampersands in their names. I had to modify the output.sql from :

CREATE UNIQUE INDEX item_uom_conv&item_no&ix3 ON [...]

to :

CREATE UNIQUE INDEX "item_uom_conv&item_no&ix3" ON [...]

to make it work with psql (9.2.2).

[darold]

Hi Bruno,

Yes all objects name with non alphanumerics characters (and some other) need to be double-quoted by hand. But here I think thay in an index name you may rename the index instead and remove the ampersand.

[bbigras]

I wish the manual would have warned me about that but it's no big deal.

For now, since I'm only testing and I have about 50 indexes with an ampersand, I'm using the following python script:

import re
with open('output.sql', 'r') as f_in:
    with open('output2.sql', 'w') as f_out:
        for line in f_in:
            f_out.write(re.sub(r'(CREATE.*INDEX )(.*)( ON)', r'\1"\2"\3', line))

[darold]

You can also use this single command line:

perl -p -i -e 's/(CREATE.INDEX )(.)( ON)/\1"\2"\3/' output.sql