Re: [SQL] char(19) to varchar(32)

Actually, here's my complete rule and error message:

CREATE RULE prod_company AS ON UPDATE
TO company_base WHERE OLD.company <> NEW.company
DO UPDATE prod_base set company = NEW.company
WHERE prod_base.cid = OLD.oid;
ERROR: Type of 'company' does not match target column 'company'

company in prod_base is char(19);
company in company_baase is varchar(32);

I have solved my little problem by using substr(NEW.company, 0, 20).
Please let me know if the above error message is correct or if postgresql
should actually be casting to char(19) automatically, ie do whatever it
takes to copy whatever it can to prod_base.company.

Marc

On Wed, 19 Jan 2000, Tom Lane wrote:

> Jose Soares <jose(at)sferacarta(dot)com> writes:
> > You have to cast the varchar field as char. Example: CAST (company AS CHAR)
>
> > For example:
>
> > CREATE TABLE test (
> > company varchar(32)
> > );
> > insert into test values('12345678901234567890123456789012');
> >
> > CREATE TABLE test1 (
> > company char(19)
> > );
> > insert into test1 (company) select cast(test.company as char);
>
> Actually, you should get the same results with or without a cast,
> because the system implicitly inserts a cast to char(19) before storing
> data into test1.company.
>
> Or at least it's supposed to. There have been bugs in that area in
> past versions. Jose's example works as I expect --- same result
> with or without cast --- but I wonder whether Marc has found a problem
> in pl/pgsql's handling of this situation. Marc, you didn't actually
> say why you thought that
>
> >> company is char(19);
> >> NEW.company is varchar(32);
>
> didn't work; what behavior are you seeing?
>
> regards, tom lane
>