[email protected] writes:
> I get a couple of issues - there are commands that relate to to
> postgres' which isn't installed by the port (which installs pgsql) -
> and is easily fixed by defining the role - but the other is baffling.
Can you submit patches, perhaps?
> I get:
>
> psql:archiveopteryx.backup:1316974: ERROR: could not create unique
> index "addresses_nld_key"
> DETAIL: Key (name, localpart, lower(domain))=(0amamacint, alert,
> date.com) is duplicated.
>
> Now I can understand getting an error like this when messing around
> normally using DML, but this is a backup and the index only exists
> because the original PostgreSQL thought it did. :-(
>
> Is this a difference in lower(domain) behaviour between the older
> Ubuntu and the newer FreeBSD UTF8? Any hint on how to fix?
Baffling indeed.
The constraint is from 2007, but there was another before it,
practically identical. I am not aware of any changes in how lower()
works, at least not with ASCII letters.
As a first hypothesis, could there be a problem in the old database? Can
you check
select a1.id, a1.localpart, a1.domain, a2.id from addresses a1
join addresses a2 on (a1.name=a2.name and a1.localpart=a2.localpart
and lower(a1.domain)=lower(a2.domain))
where a1.id<a2.id;
and see how many rows you get?
The fix is probably going to be something like this:
update address_fields set address=1234 where address=2354;
for each of the pairs you get from that select. But I would like to
understand how this made it into the database in the first place.
Arnt
