pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
char(19) to varchar(32)
6+ messages / 3 participants
[nested] [flat]

* char(19) to varchar(32)
@ 2000-01-18 14:04 Marc Tardif <admin@wtbwts.com>
  2000-01-19 14:01 ` Re: [SQL] char(19) to varchar(32) Jose Soares <jose@sferacarta.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Marc Tardif @ 2000-01-18 14:04 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] char(19) to varchar(32)
  2000-01-18 14:04 char(19) to varchar(32) Marc Tardif <admin@wtbwts.com>
@ 2000-01-19 14:01 ` Jose Soares <jose@sferacarta.com>
  2000-01-19 15:41   ` Re: [SQL] char(19) to varchar(32) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Jose Soares @ 2000-01-19 14:01 UTC (permalink / raw)
  To: Marc Tardif <admin@wtbwts.com>; +Cc: pgsql-sql

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);

select * from test;
                         company
--------------------------------
12345678901234567890123456789012
(1 row)

select * from test1;
            company
-------------------
1234567890123456789
(1 row)


José


Marc Tardif wrote:

> 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
>
> ************

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] char(19) to varchar(32)
  2000-01-18 14:04 char(19) to varchar(32) Marc Tardif <admin@wtbwts.com>
  2000-01-19 14:01 ` Re: [SQL] char(19) to varchar(32) Jose Soares <jose@sferacarta.com>
@ 2000-01-19 15:41   ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-19 10:56     ` Re: [SQL] char(19) to varchar(32) Marc Tardif <admin@wtbwts.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2000-01-19 15:41 UTC (permalink / raw)
  To: Jose Soares <jose@sferacarta.com>; +Cc: Marc Tardif <admin@wtbwts.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] char(19) to varchar(32)
  2000-01-18 14:04 char(19) to varchar(32) Marc Tardif <admin@wtbwts.com>
  2000-01-19 14:01 ` Re: [SQL] char(19) to varchar(32) Jose Soares <jose@sferacarta.com>
  2000-01-19 15:41   ` Re: [SQL] char(19) to varchar(32) Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-19 10:56     ` Marc Tardif <admin@wtbwts.com>
  2000-01-19 17:24       ` Re: [SQL] char(19) to varchar(32) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Marc Tardif @ 2000-01-19 10:56 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jose Soares <jose@sferacarta.com>; pgsql-sql

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
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] char(19) to varchar(32)
  2000-01-18 14:04 char(19) to varchar(32) Marc Tardif <admin@wtbwts.com>
  2000-01-19 14:01 ` Re: [SQL] char(19) to varchar(32) Jose Soares <jose@sferacarta.com>
  2000-01-19 15:41   ` Re: [SQL] char(19) to varchar(32) Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-19 10:56     ` Re: [SQL] char(19) to varchar(32) Marc Tardif <admin@wtbwts.com>
@ 2000-01-19 17:24       ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-19 18:11         ` Re: [SQL] char(19) to varchar(32) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2000-01-19 17:24 UTC (permalink / raw)
  To: Marc Tardif <admin@wtbwts.com>; +Cc: Jose Soares <jose@sferacarta.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] char(19) to varchar(32)
  2000-01-18 14:04 char(19) to varchar(32) Marc Tardif <admin@wtbwts.com>
  2000-01-19 14:01 ` Re: [SQL] char(19) to varchar(32) Jose Soares <jose@sferacarta.com>
  2000-01-19 15:41   ` Re: [SQL] char(19) to varchar(32) Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-19 10:56     ` Re: [SQL] char(19) to varchar(32) Marc Tardif <admin@wtbwts.com>
  2000-01-19 17:24       ` Re: [SQL] char(19) to varchar(32) Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-19 18:11         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2000-01-19 18:11 UTC (permalink / raw)
  To: Marc Tardif <admin@wtbwts.com>; +Cc: Jose Soares <jose@sferacarta.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2000-01-19 18:11 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-18 14:04 char(19) to varchar(32) Marc Tardif <admin@wtbwts.com>
2000-01-19 14:01 ` Jose Soares <jose@sferacarta.com>
2000-01-19 15:41   ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-19 10:56     ` Marc Tardif <admin@wtbwts.com>
2000-01-19 17:24       ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-19 18:11         ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox