Re: select statement fails - Mailing list pgsql-general

Andrus wrote:
>> ALTER TABLE...ALTER COLUMN...TYPE will do it within one statement. It will
>> require a lock on the table though.
>
> 1. This is part of composite primary key. It is discriminator column and
> cannot contain empty string, only single char is allowed.

char(1) doesn't enforce that. It enforces a maximum of  1 character.
                                           ^
richardh=> CREATE TABLE chartest (c char(1) NOT NULL);
CREATE TABLE
richardh=> INSERT INTO chartest VALUES ('a'),(' '),('');
INSERT 0 3

What it will do is strip the space in the second value so the last two
values are the same (and have length()=0). Or rather, spaces are treated
as trailing the value which amounts to the same thing here.

If you want a single character you'll want to add a CHECK > '' to a
varchar or text column. That's because using the check on a char()
column will disallow a space-character too.

richardh=> ALTER TABLE chartest ADD CONSTRAINT ccheck CHECK (c > '');
ALTER TABLE
richardh=> INSERT INTO chartest VALUES ('a'),(' '),('');
ERROR:  new row for relation "chartest" violates check constraint "ccheck"
richardh=> INSERT INTO chartest VALUES ('a'),(' ');
ERROR:  new row for relation "chartest" violates check constraint "ccheck"

> 2. I'm afraid that this will broke existing applications.

Possible, particularly since the behaviour of the column as defined is
problematic anyway. It depends on how they expect a single space to
behave vs an empty string.

> So I'm not sure that it is reasonable to make such change.
>
>>> I is more reasonable to force npgsql driver to generate other code if no
>>> other solution.
>>> npgsql driver wants to add explicit casts to parameters.
>>> Is it reasonable to force driver to generate code
>>>
>>> select * from test where tc=' '::char(1);
>>>
>>> for char parameter type ?
>> I'd say so - I presume it just needs to be taught about different types of
>> text. Presumably it already knows that int8 is different from int4, so
>> it'll be doing something similar already.
>
> Should it cast to char(1) or varchar(1) ?

Well in your case char(1), obviously.

> Driver cannot determine this form .NET type char.

I'm guessing it doesn't rely on the .NET type, but rather on the
database types. Certainly there are plenty of types available to PG that
probably don't have a mapping in a standard .NET install.

> So this seems not possible without providing additional meta information to
> driver, i.e. standard ADO .NET interface cannot used.
>
> So I think that only solution is to create this query using string
> concatenation and possibly open it to sql injection attacks, parameter
> replacement is not possible.

Ask the .npgsql mailing lists. They'll be able to tell you. There must
be a way of handling "non-standard" types in any case.

--
   Richard Huxton
   Archonet Ltd