Thread: char(19) to varchar(32)
I have created a rule to update a char(19) field from a varchar(32) field. I don't mind if half the field is cut out, I just want to copy the first 19 characters. Here's what I have ... update products set company=NEW.company; company is char(19); NEW.company is varchar(32); Any suggestions on making this work? Marc
<tt>You have to cast the varchar field as char. Example: CAST (company AS CHAR)</tt><br
/><tt></tt> <tt></tt><p><tt>Forexample:</tt><tt></tt><p><tt>CREATE TABLE test (</tt><br /><tt> company
varchar(32)</tt><br/><tt>);</tt><br /><tt>insert into test
values('12345678901234567890123456789012');</tt><tt></tt><p><tt>CREATETABLE test1 (</tt><br /><tt> company
char(19)</tt><br/><tt>);</tt><br /><tt>insert into test1 (company) select cast(test.company as
char);</tt><tt></tt><p><tt>select* from test;</tt><br /><tt> company</tt><br
/><tt>--------------------------------</tt><br/><tt>12345678901234567890123456789012</tt><br /><tt>(1
row)</tt><tt></tt><p><tt>select* from test1;</tt><br /><tt> company</tt><br
/><tt>-------------------</tt><br/><tt>1234567890123456789</tt><br /><tt>(1 row)</tt><br
/><tt></tt> <tt></tt><p><tt>José</tt><br/> <p>Marc Tardif wrote: <blockquote type="CITE">I have created a rule to
updatea char(19) field from a varchar(32) field. <br />I don't mind if half the field is cut out, I just want to copy
thefirst <br />19 characters. Here's what I have <br />... update products set company=NEW.company; <p>company is
char(19);<br />NEW.company is varchar(32); <p>Any suggestions on making this work? <br />Marc
<p>************</blockquote>
Jose Soares <jose@sferacarta.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
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@sferacarta.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
>
Marc Tardif <admin@wtbwts.com> writes:
> 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);
Interesting. You can get the same error from just doing the UPDATE
by hand --- so it's not got anything to do with the rule environment:
create table prod_base (company char(19));
create table company_base (company varchar(32));
update prod_base set company = company_base.company;
ERROR: Type of 'company' does not match target column 'company'
but
update prod_base set company = company_base.company::char;
is accepted. Even more interesting, so is
update prod_base set company = company_base.company::text;
so it's not simply a matter of UPDATE missing automatic coercion
support; it's willing to do a coercion if you hand it an expression,
but seemingly not if you hand it a simple field reference.
This sure looks like a bug to me... I recommend a CAST as a workaround
for now, but I'll try to fix it for 7.0.
regards, tom lane
I wrote:
> create table prod_base (company char(19));
> create table company_base (company varchar(32));
> update prod_base set company = company_base.company;
> ERROR: Type of 'company' does not match target column 'company'
> This sure looks like a bug to me... I recommend a CAST as a workaround
> for now, but I'll try to fix it for 7.0.
Actually, it seems it's fixed in current sources already.
regards, tom lane