Postgres Pro
Downloads
Home   >   mailing lists

Thread: Join Problem

Join Problem

From
"Rob Sell"
Date:
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

Re: Join Problem

From
"scott.marlowe"
Date:
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...

Re: Join Problem

From
"Rob Sell"
Date:
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...

Re: Join Problem

From
"scott.marlowe"
Date:
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

Re: Join Problem

From
"Rob Sell"
Date:
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...

Re: Join Problem

From
Tom Lane
Date:
I'm wondering about trailing blanks having crept into one table or
the other ...

            regards, tom lane

Re: Join Problem

From
"Rob Sell"
Date:
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)