Re: Non-unique values problem after 'add column'

On Nov 26, 2007 3:06 AM, <dima(dot)kagan(at)gmail(dot)com> wrote:
> Hello!
>
> I've encountered a very strange problem with Postgresql 7.3.
> I have the following set of queries running in transaction, using
> libpqxx 2.6.9:

7.3 is getting pretty old. I can't say anything though, I've still
got a 7.4 db hanging around somewhere too. :)

> ALTER TABLE my_table ADD new_column TEXT
> UPDATE my_table SET new_column = 'disable'
> ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 'disable'
> CREATE UNIQUE INDEX my_index ON my_table (old_column)
>
> The last query fails with:
> ERROR: Cannot create unique index. Table contains non-unique values
>
> Running the same set of queries manually causes no problems. Moreover,
> running the last query in a separate transaction works as well.

I'm guessing the difference here is NOT libpqxx, but the data in the
db. Are you sure you're running this against the exact same data set?
If so, you need to make a self-contained test that shows this
happening only to libpqxx. I.e. table def, data, sql executed, etc.

Often times, while making such a self-contained proof, you'll figure
out what's really going on, and it usually isn't a bug in libpqxx at
that point.