Re: Join Problem - Mailing list pgsql-general

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