Hi John
On 17/01/15 12:39, John McKown wrote:
I read the above. As I understand it, you can have a unique index on
a column which is NULL-able. That will guarantee that all the non-NULL
values are unique. What it will not guarantee is that there will be at
most one NULL value in the indexed column. Are you saying that what
you want is a column with a unique index where you cannot have two or
more rows with NULL in the indexed column?
That's correct - the application reads in a very old-format of
fixed-length file and, if any field in there is just a single space,
replaces it with a NULL, since a single space implies a null. However,
only one of the records needs a constraint on one of these fields.
If so, then you will need to have a value to indicate the equivalent
of NULL. Personally, I use a zero length string "" instead of a single
blank ' '. This is value since you say this column is a "character
varying(1)". Which seems a bit strange to me, but I don't know your
application.
OK, that makes sense and it was more-or-less along the lines of what I
expected. I like the idea of a zero-length string versus a single
space, so I'll go implement that.
I believe the column type a Rails-ism, which creates 'string' fields
with a length constraint of 1 as 'character varying(1)'. Probably not
ideal, but there's usually a trade-off somewhere.
Thanks very much for your help and quick response!
Peter
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
