Обсуждение: Join Problem
We are migrating to a new database server and one piece of code doesn't work from the old to new. Old server is 7.2.1 New server is 7.3.2 The sql in question is "Select * from inven as i Left Outer Join conn_xr as c on i.partnum = c.facpn" This produces the desired effects on the 7.2.1 server but doesn't work as expected on 7.3.2 I have looked at the release notes from 7.2.1 through 7.3.2 and didn't see any mention of anything that would affect that particular command. TIA for any help you can provide. Rob
On Wed, 23 Jul 2003, Rob Sell wrote: > We are migrating to a new database server and one piece of code doesn't work > from the old to new. > Old server is 7.2.1 > New server is 7.3.2 > > The sql in question is "Select * from inven as i Left Outer Join conn_xr as > c on i.partnum = c.facpn" > > This produces the desired effects on the 7.2.1 server but doesn't work as > expected on 7.3.2 > > I have looked at the release notes from 7.2.1 through 7.3.2 and didn't see > any mention of anything that would affect that particular command. I'm not sure you've given us enough info to help you. Is there anything odd about your table structure? Maybe the two databases were inited with different locales? Maybe some sample schema and data might help...
Ok I was going to send in the schema from both db's and when trying to get the schema through phppgadmin it works fine on the old db, but on the new db I get this error "PostgreSQL said: ERROR: Relation "pg_relcheck" does not exist" What is that? And could that be related to my problem? Rob -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Wednesday, July 23, 2003 2:55 PM To: Rob Sell Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Join Problem On Wed, 23 Jul 2003, Rob Sell wrote: > We are migrating to a new database server and one piece of code doesn't work > from the old to new. > Old server is 7.2.1 > New server is 7.3.2 > > The sql in question is "Select * from inven as i Left Outer Join conn_xr as > c on i.partnum = c.facpn" > > This produces the desired effects on the 7.2.1 server but doesn't work as > expected on 7.3.2 > > I have looked at the release notes from 7.2.1 through 7.3.2 and didn't see > any mention of anything that would affect that particular command. I'm not sure you've given us enough info to help you. Is there anything odd about your table structure? Maybe the two databases were inited with different locales? Maybe some sample schema and data might help...
On Wed, 23 Jul 2003, Rob Sell wrote: > Ok I was going to send in the schema from both db's and when trying to get > the schema through phppgadmin it works fine on the old db, but on the new db > I get this error "PostgreSQL said: ERROR: Relation "pg_relcheck" does not > exist" > > What is that? And could that be related to my problem? Nah, likely just a version issue (i.e. the new database doesn't have the system tables that phppgadmin expects) you can get a dump of the schema / tables with pg_dump on each box: pg_dump -s dbname -t tablename <- this will dump the schema
Sorry I didn't want my first email to be to big :-)
Schema from old db
--
-- TOC Entry ID 6 (OID 352504688)
--
-- Name: conn_xr Type: TABLE Owner: postgres
--
CREATE TABLE "conn_xr" (
"facpn" character varying(20),
"fappn" character varying(20),
"facvendnum" character varying(25),
"fapvendnum" character varying(25)
);
--
-- TOC Entry ID 15 (OID 419387295)
--
-- Name: inven Type: TABLE Owner: postgres
--
CREATE TABLE "inven" (
"partnum" character varying(13),
"description" character varying(57),
"instock" integer,
"onorder" integer,
"targetforonhand" integer,
"averageunitcost" real,
"lastprice" real,
"inprocess" integer,
"pledged" integer,
"mousage" integer,
"avemousage" integer,
"vendor" character varying(3),
"vendornum" character varying(26),
"lastrecieved" character varying(6),
"leadtime" character varying(2),
"vcertdt" character varying(7),
"manufacturer" character varying(3),
"notefile" character varying(14),
"lbsperunit" character varying(8)
);
-------------------------------------------------------------
Schema from new db
--
-- TOC entry 33 (OID 70842866)
-- Name: conn_xr; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE conn_xr (
facpn character varying(20),
fappn character varying(20),
facvendnum character varying(25),
fapvendnum character varying(25)
);
--
-- TOC entry 25 (OID 69732128)
-- Name: inven; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE inven (
partnum character varying(13),
description character varying(57),
instock integer,
onorder integer,
targetforonhand integer,
averageunitcost real,
lastprice real,
inprocess integer,
pledged integer,
mousage integer,
avemousage integer,
vendor character varying(3),
vendornum character varying(26),
lastrecieved character varying(6),
leadtime character varying(2),
vcertdt character varying(7),
manufacturer character varying(3),
notefile character varying(14),
lbsperunit character varying(8)
);
------------------------------------------
Sample data.
From inven table
CN-01F 01HFem56SerNon-Lock 2505 0 100 0.017 0.0168 579 1972 472 616 PIO
02962793 010201 16 010201 PAC _ 0.0018
From conn_xr table
CN-01F CN-01F 2962793 02962793
Old server join returns this
CN-01F 01HFem56SerNon-Lock 2505 0 100 0.017
0.0168 579 1972 472 616 PIO 02962793
010201 16 010201 PAC 0.0018 CN-01F
CN-01F 2962793 02962793
-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Wednesday, July 23, 2003 2:55 PM
To: Rob Sell
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Join Problem
On Wed, 23 Jul 2003, Rob Sell wrote:
> We are migrating to a new database server and one piece of code doesn't
work
> from the old to new.
> Old server is 7.2.1
> New server is 7.3.2
>
> The sql in question is "Select * from inven as i Left Outer Join conn_xr
as
> c on i.partnum = c.facpn"
>
> This produces the desired effects on the 7.2.1 server but doesn't work as
> expected on 7.3.2
>
> I have looked at the release notes from 7.2.1 through 7.3.2 and didn't see
> any mention of anything that would affect that particular command.
I'm not sure you've given us enough info to help you. Is there anything
odd about your table structure? Maybe the two databases were inited with
different locales? Maybe some sample schema and data might help...
I'm wondering about trailing blanks having crept into one table or
the other ...
regards, tom lane
Bingo. But not in the way I would've expected there were trailing blanks on
the old one but not in the new one. In the conn_xr table that is.
I feel stupid for overlooking something so simple.
Thanks to all who gave me input.
Rob
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, July 24, 2003 12:24 AM
To: Rob Sell
Cc: 'scott.marlowe'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Join Problem
I'm wondering about trailing blanks having crept into one table or
the other ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)