Thread: SQL Join - MySQL/PostgreSQL difference?
Greetings. I'm working with a product provided by a third part that interfaces to data housed in a database of your choice. Previously, my choice was MySQL - apparently it handled certain queries too slowly, so I'm giving PostgreSQL a shot. Here's the query: SELECT a.Number, a.Code, a.Text FROM b, a WHERE (b.Id = a.Id) AND (VersionId = 'key1') AND (Category = 'key2') AND (b.d_Id = 'key3') ORDER BY a.Number; (my apologies: I had to 'mangle' the table/column names because of NDA) So my question is this: would this query operate differently in MySQL than in PostgreSQL? The reason I ask is that this query in MySQL returns results, yet in PostgreSQL it does not. I read a post about PostgreSQL not supporting outer joins, but I don't have enough experience with SQL to determine if this is such a query or not. Please advise. Any help will be (as always) sincerely appreciated. -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
Brice Ruth wrote: > Greetings. > > I'm working with a product provided by a third part that interfaces to > data housed in a database of your choice. Previously, my choice was > MySQL - apparently it handled certain queries too slowly, so I'm giving > PostgreSQL a shot. Here's the query: > > SELECT > a.Number, > a.Code, > a.Text > FROM > b, > a > WHERE > (b.Id = a.Id) AND > (VersionId = 'key1') AND > (Category = 'key2') AND > (b.d_Id = 'key3') > ORDER BY > a.Number; > > (my apologies: I had to 'mangle' the table/column names because of NDA) > > So my question is this: would this query operate differently in MySQL > than in PostgreSQL? The reason I ask is that this query in MySQL > returns results, yet in PostgreSQL it does not. I read a post about > PostgreSQL not supporting outer joins, but I don't have enough > experience with SQL to determine if this is such a query or not. Please > > advise. > > Any help will be (as always) sincerely appreciated. > > -- > Brice Ruth > WebProjkt, Inc. > VP, Director of Internet Technology > http://www.webprojkt.com/ It should work the same in both. The only thing I notice is that not all the field names are qualified with table names or aliases. That can lead to ambiguity, but the query would blow up on both databases if that were a problem. Ian
Here's something to chew on ...
The following snippet of SQL produces a result:
--
CREATE TABLE TBLPEMDRUGLINK
(
DRUGID VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
);
CREATE TABLE TBLFDBMONO
(
VERSIONID VARCHAR(10) NOT NULL,
CATEGORY VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
SEQUENCENUMBER INT2 NOT NULL,
SECTIONCODE VARCHAR(1),
LINETEXT VARCHAR(255),
CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
MONOGRAPHID, SEQUENCENUMBER)
);
insert into tblpemdruglink values ('DG-5039','2008');
insert into tblfdbmono values ('FDB-PE','PEM','2008',8,'A','some info to display');
SELECT sequencenumber,sectioncode,linetext
FROM tblfdbmono fdb, tblpemdruglink pem WHERE
fdb.monographid=pem.monographid AND
fdb.versionid='FDB-PE' AND
fdb.category='PEM' AND
pem.drugid='DG-5039';
--
This puts some 'fake' data into the tables, but the fake data resembles
the true data. Now, when I load the tables with the true data using:
COPY tblpemdruglink FROM '/tmp/Data/db_files/tblPEMDrugLink.txt' USING
DELIMITERS '|';
COPY tblfdbmono FROM '/tmp/Data/db_files/tblFDBMono.txt' USING
DELIMITERS '|';
and then run the same SELECT as before:
SELECT sequencenumber,sectioncode,linetext
FROM tblfdbmono fdb, tblpemdruglink pem WHERE
fdb.monographid=pem.monographid AND
fdb.versionid='FDB-PE' AND
fdb.category='PEM' AND
pem.drugid='DG-5039';
I get 0 results. So the logical conclusion: the dataset doesn't contain
a record that satisfies the query. However, the following queries
represent the 'manual join' that the join above automates:
SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';
> drugid | monographid
>---------+-------------
> DG-5039 | 2008
>(1 row)
SELECT sequencenumber, sectioncode, linetext FROM tblfdbmono fdb WHERE
fdb.monographid='2008' AND
fdb.versionid='FDB-PE' AND
fdb.category='PEM';
> sequencenumber | sectioncode | linetext
>----------------+-------------+---------------
>{************** results omitted *************)
>(64 rows)
So if the 'manual join' produces data yet the actual join does not, then
the logical conclusion above doesn't hold and something is wrong
(besides the data set). That's where I need help. I can stare at the
SQL all day & all night long and not figure anything out ... I'm not an
expert at SQL and this is the first time I'm using PostgreSQL. One
thing I can say, however, is that mirroring this in MySQL (and allowing
for case-sensitivity) produces results (it takes a few seconds because
the tblFDBMono table is actually quite large). What's going on?? Any
help at all is most sincerely appreciated. Hell, I wouldn't mind
coughin' up the $$ for a support incident if I thought that would help
... but I'm not entirely sure where the problem is to warrant a support incident.
Please respond to this newsgroup, the mailing list (which should be
mirrored on the newsgroup), and/or to me personally. I'm twiddlin' my
thumbs until I can figure this one out.
Regards,
Brice Ruth
Brice - What's the result of select monographid,length(monographid) from tblpemdruglink where monographid ~ '^2008'; It occurs to me that your delimited text file may have padded values, and "=" insists on exact matches for VARCHAR. update tblpemdruglink set monographid=btrim(monographid); might help, if the first query returns anything but 4. Ross On Tue, Feb 06, 2001 at 05:22:59PM -0600, Brice Ruth wrote: > Here's something to chew on ... > > The following snippet of SQL produces a result: > <SNIP> > Please respond to this newsgroup, the mailing list (which should be > mirrored on the newsgroup), and/or to me personally. I'm twiddlin' my > thumbs until I can figure this one out. > > Regards, > Brice Ruth
Ross, Thanx for the heads up on this. The select did indeed return something other than four: 5. I updated as you suggested, but that alone didn't fix the problem. I'm updating tblFDBMono now with the same type of 'fix' to see if this is the root of the problem. Is '=' handled differently between PostgreSQL and MySQL in this case? -Brice "Ross J. Reedstrom" wrote: > > Brice - > What's the result of > > select monographid,length(monographid) from tblpemdruglink where > monographid ~ '^2008'; > > It occurs to me that your delimited text file may have padded values, > and "=" insists on exact matches for VARCHAR. > > update tblpemdruglink set monographid=btrim(monographid); > > might help, if the first query returns anything but 4. > > Ross > > On Tue, Feb 06, 2001 at 05:22:59PM -0600, Brice Ruth wrote: > > Here's something to chew on ... > > > > The following snippet of SQL produces a result: > > > > <SNIP> > > > Please respond to this newsgroup, the mailing list (which should be > > mirrored on the newsgroup), and/or to me personally. I'm twiddlin' my > > thumbs until I can figure this one out. > > > > Regards, > > Brice Ruth
Unfortunately ... that didn't seem to help :( I used btrim on all the fields that were part of an equals (=) statement and reran the select and got the same result (0 rows). After I was in the process of updating the tables, I thought that this may fail ... since again, the 'manual join' of these two tables returns the correct results ... and it uses the same equality tests as the SQL join. I'm more than happy to keep tryin' different things, though ... anything to get to the bottom of this. Regards, Brice Ruth "Ross J. Reedstrom" wrote: > > Brice - > What's the result of > > select monographid,length(monographid) from tblpemdruglink where > monographid ~ '^2008'; > > It occurs to me that your delimited text file may have padded values, > and "=" insists on exact matches for VARCHAR. > > update tblpemdruglink set monographid=btrim(monographid); > > might help, if the first query returns anything but 4. > > Ross > > On Tue, Feb 06, 2001 at 05:22:59PM -0600, Brice Ruth wrote: > > Here's something to chew on ... > > > > The following snippet of SQL produces a result: > > > > <SNIP> > > > Please respond to this newsgroup, the mailing list (which should be > > mirrored on the newsgroup), and/or to me personally. I'm twiddlin' my > > thumbs until I can figure this one out. > > > > Regards, > > Brice Ruth
FYI: Here are the table definitions:
CREATE TABLE TBLPEMDRUGLINK
(
DRUGID VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
);
CREATE TABLE TBLFDBMONO
(
VERSIONID VARCHAR(10) NOT NULL,
CATEGORY VARCHAR(10) NOT NULL,
MONOGRAPHID VARCHAR(10) NOT NULL,
SEQUENCENUMBER SMALLINT NOT NULL,
SECTIONCODE VARCHAR(1),
LINETEXT VARCHAR(255),
CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
MONOGRAPHID, SEQUENCENUMBER)
);
Running the following query:
Query1: SELECT sequencenumber,sectioncode,linetext
Query1: FROM tblfdbmono fdb, tblpemdruglink pem WHERE
Query1: fdb.monographid=pem.monographid AND
Query1: fdb.versionid='FDB-PE' AND
Query1: fdb.category='PEM' AND
Query1: pem.drugid='DG-5039';
returns 0 rows.
However, the following two queries produce results:
Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';
Query3: SELECT * FROM tblfdbmono WHERE
Query3: monographid='2008' AND
Query3: versionid='FDB-PE' AND
Query3: category='PEM';
To my knowledge, Query1 is the join that should produce the same results
as the manual join represented by queries 2 & 3.
What's going on?
-Brice
Ian Harding wrote:
>
> Brice Ruth wrote:
>
> > Greetings.
> >
> > I'm working with a product provided by a third part that interfaces to
> > data housed in a database of your choice. Previously, my choice was
> > MySQL - apparently it handled certain queries too slowly, so I'm giving
> > PostgreSQL a shot. Here's the query:
> >
> > SELECT
> > a.Number,
> > a.Code,
> > a.Text
> > FROM
> > b,
> > a
> > WHERE
> > (b.Id = a.Id) AND
> > (VersionId = 'key1') AND
> > (Category = 'key2') AND
> > (b.d_Id = 'key3')
> > ORDER BY
> > a.Number;
> >
> > (my apologies: I had to 'mangle' the table/column names because of NDA)
> >
> > So my question is this: would this query operate differently in MySQL
> > than in PostgreSQL? The reason I ask is that this query in MySQL
> > returns results, yet in PostgreSQL it does not. I read a post about
> > PostgreSQL not supporting outer joins, but I don't have enough
> > experience with SQL to determine if this is such a query or not. Please
> >
> > advise.
> >
> > Any help will be (as always) sincerely appreciated.
> >
> > --
> > Brice Ruth
> > WebProjkt, Inc.
> > VP, Director of Internet Technology
> > http://www.webprojkt.com/
>
> It should work the same in both. The only thing I notice is that not all
> the field names are qualified with table names or aliases. That can lead
> to ambiguity, but the query would blow up on both databases if that were a
> problem.
>
> Ian
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
Run the following query: SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2, 3; is anything returned? If not, that is your problem (no rows exists with matching monographid's). If information is returned, however, pick an arbitrary row, and plug the data into the following query (you will have a valid where clause then): SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND fdb.versionid = '<<VERSION ID>>' AND fdb.category = '<<CATEGORY>>' AND pem.drugid = '<<DRUG ID>>' Also, you may want to try qualifying your table names, i.e.: SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039'; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 6 Feb 2001, Brice Ruth wrote: > FYI: Here are the table definitions: > > CREATE TABLE TBLPEMDRUGLINK > ( > DRUGID VARCHAR(10) NOT NULL, > MONOGRAPHID VARCHAR(10) NOT NULL, > CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID) > ); > > CREATE TABLE TBLFDBMONO > ( > VERSIONID VARCHAR(10) NOT NULL, > CATEGORY VARCHAR(10) NOT NULL, > MONOGRAPHID VARCHAR(10) NOT NULL, > SEQUENCENUMBER SMALLINT NOT NULL, > SECTIONCODE VARCHAR(1), > LINETEXT VARCHAR(255), > CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY, > MONOGRAPHID, SEQUENCENUMBER) > ); > > Running the following query: > > Query1: SELECT sequencenumber,sectioncode,linetext > Query1: FROM tblfdbmono fdb, tblpemdruglink pem WHERE > Query1: fdb.monographid=pem.monographid AND > Query1: fdb.versionid='FDB-PE' AND > Query1: fdb.category='PEM' AND > Query1: pem.drugid='DG-5039'; > > returns 0 rows. > > However, the following two queries produce results: > > Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039'; > > Query3: SELECT * FROM tblfdbmono WHERE > Query3: monographid='2008' AND > Query3: versionid='FDB-PE' AND > Query3: category='PEM'; > > To my knowledge, Query1 is the join that should produce the same results > as the manual join represented by queries 2 & 3. > > What's going on? > > -Brice > > Ian Harding wrote: > > > > Brice Ruth wrote: > > > > > Greetings. > > > > > > I'm working with a product provided by a third part that interfaces to > > > data housed in a database of your choice. Previously, my choice was > > > MySQL - apparently it handled certain queries too slowly, so I'm giving > > > PostgreSQL a shot. Here's the query: > > > > > > SELECT > > > a.Number, > > > a.Code, > > > a.Text > > > FROM > > > b, > > > a > > > WHERE > > > (b.Id = a.Id) AND > > > (VersionId = 'key1') AND > > > (Category = 'key2') AND > > > (b.d_Id = 'key3') > > > ORDER BY > > > a.Number; > > > > > > (my apologies: I had to 'mangle' the table/column names because of NDA) > > > > > > So my question is this: would this query operate differently in MySQL > > > than in PostgreSQL? The reason I ask is that this query in MySQL > > > returns results, yet in PostgreSQL it does not. I read a post about > > > PostgreSQL not supporting outer joins, but I don't have enough > > > experience with SQL to determine if this is such a query or not. Please > > > > > > advise. > > > > > > Any help will be (as always) sincerely appreciated. > > > > > > -- > > > Brice Ruth > > > WebProjkt, Inc. > > > VP, Director of Internet Technology > > > http://www.webprojkt.com/ > > > > It should work the same in both. The only thing I notice is that not all > > the field names are qualified with table names or aliases. That can lead > > to ambiguity, but the query would blow up on both databases if that were a > > problem. > > > > Ian > > -- > Brice Ruth > WebProjkt, Inc. > VP, Director of Internet Technology > http://www.webprojkt.com/ >
I believe we're getting closer to the problem here. When I run the
first query below, I get 0 results, which should be wrong. When I run
the query:
SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
I get 5 returned to me, even though the string is only 4 ('2008').
However, after doing:
UPDATE tblpemdruglink SET monographid=trim(monographid);
and rerunning the query above, I still get 5, not 4. Is something being
imported incorrectly by the COPY ... FROM? Any way for me to tell if
there are other non-printing characters in there?
Thank you all for the continued support on this.
Regards,
Brice Ruth
Michael Fork wrote:
>
> Run the following query:
>
> SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
> tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
> 3;
>
> is anything returned? If not, that is your problem (no rows exists with
> matching monographid's). If information is returned, however, pick an
> arbitrary row, and plug the data into the following query (you will have
> a valid where clause then):
>
> SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND
> fdb.versionid = '<<VERSION ID>>' AND fdb.category = '<<CATEGORY>>' AND
> pem.drugid = '<<DRUG ID>>'
>
> Also, you may want to try qualifying your table names, i.e.:
>
> SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND
> fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039';
>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Toledo Ohio
>
> On Tue, 6 Feb 2001, Brice Ruth wrote:
>
> > FYI: Here are the table definitions:
> >
> > CREATE TABLE TBLPEMDRUGLINK
> > (
> > DRUGID VARCHAR(10) NOT NULL,
> > MONOGRAPHID VARCHAR(10) NOT NULL,
> > CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
> > );
> >
> > CREATE TABLE TBLFDBMONO
> > (
> > VERSIONID VARCHAR(10) NOT NULL,
> > CATEGORY VARCHAR(10) NOT NULL,
> > MONOGRAPHID VARCHAR(10) NOT NULL,
> > SEQUENCENUMBER SMALLINT NOT NULL,
> > SECTIONCODE VARCHAR(1),
> > LINETEXT VARCHAR(255),
> > CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
> > MONOGRAPHID, SEQUENCENUMBER)
> > );
> >
> > Running the following query:
> >
> > Query1: SELECT sequencenumber,sectioncode,linetext
> > Query1: FROM tblfdbmono fdb, tblpemdruglink pem WHERE
> > Query1: fdb.monographid=pem.monographid AND
> > Query1: fdb.versionid='FDB-PE' AND
> > Query1: fdb.category='PEM' AND
> > Query1: pem.drugid='DG-5039';
> >
> > returns 0 rows.
> >
> > However, the following two queries produce results:
> >
> > Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';
> >
> > Query3: SELECT * FROM tblfdbmono WHERE
> > Query3: monographid='2008' AND
> > Query3: versionid='FDB-PE' AND
> > Query3: category='PEM';
> >
> > To my knowledge, Query1 is the join that should produce the same results
> > as the manual join represented by queries 2 & 3.
> >
> > What's going on?
> >
> > -Brice
> >
> > Ian Harding wrote:
> > >
> > > Brice Ruth wrote:
> > >
> > > > Greetings.
> > > >
> > > > I'm working with a product provided by a third part that interfaces to
> > > > data housed in a database of your choice. Previously, my choice was
> > > > MySQL - apparently it handled certain queries too slowly, so I'm giving
> > > > PostgreSQL a shot. Here's the query:
> > > >
> > > > SELECT
> > > > a.Number,
> > > > a.Code,
> > > > a.Text
> > > > FROM
> > > > b,
> > > > a
> > > > WHERE
> > > > (b.Id = a.Id) AND
> > > > (VersionId = 'key1') AND
> > > > (Category = 'key2') AND
> > > > (b.d_Id = 'key3')
> > > > ORDER BY
> > > > a.Number;
> > > >
> > > > (my apologies: I had to 'mangle' the table/column names because of NDA)
> > > >
> > > > So my question is this: would this query operate differently in MySQL
> > > > than in PostgreSQL? The reason I ask is that this query in MySQL
> > > > returns results, yet in PostgreSQL it does not. I read a post about
> > > > PostgreSQL not supporting outer joins, but I don't have enough
> > > > experience with SQL to determine if this is such a query or not. Please
> > > >
> > > > advise.
> > > >
> > > > Any help will be (as always) sincerely appreciated.
> > > >
> > > > --
> > > > Brice Ruth
> > > > WebProjkt, Inc.
> > > > VP, Director of Internet Technology
> > > > http://www.webprojkt.com/
> > >
> > > It should work the same in both. The only thing I notice is that not all
> > > the field names are qualified with table names or aliases. That can lead
> > > to ambiguity, but the query would blow up on both databases if that were a
> > > problem.
> > >
> > > Ian
> >
> > --
> > Brice Ruth
> > WebProjkt, Inc.
> > VP, Director of Internet Technology
> > http://www.webprojkt.com/
> >
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
Brice Ruth <brice@webprojkt.com> writes:
> SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
> I get 5 returned to me, even though the string is only 4 ('2008').
Okay, so you've got some invisible character in there, but not a space
(else the trim() woulda got rid of it). Tab maybe? Might want to go
back and examine your original data file more carefully, with an editor
that will show you control characters and such.
regards, tom lane
You could try this to see if it makes a difference
UPDATE tblpemdruglink SET monographid = substr(monographid, 1,
length(monographid) - 1)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Wed, 7 Feb 2001, Brice Ruth wrote:
> I believe we're getting closer to the problem here. When I run the
> first query below, I get 0 results, which should be wrong. When I run
> the query:
>
> SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
>
> I get 5 returned to me, even though the string is only 4 ('2008').
> However, after doing:
>
> UPDATE tblpemdruglink SET monographid=trim(monographid);
>
> and rerunning the query above, I still get 5, not 4. Is something being
> imported incorrectly by the COPY ... FROM? Any way for me to tell if
> there are other non-printing characters in there?
>
> Thank you all for the continued support on this.
>
> Regards,
> Brice Ruth
>
> Michael Fork wrote:
> >
> > Run the following query:
> >
> > SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
> > tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
> > 3;
> >
> > is anything returned? If not, that is your problem (no rows exists with
> > matching monographid's). If information is returned, however, pick an
> > arbitrary row, and plug the data into the following query (you will have
> > a valid where clause then):
> >
> > SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> > fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND
> > fdb.versionid = '<<VERSION ID>>' AND fdb.category = '<<CATEGORY>>' AND
> > pem.drugid = '<<DRUG ID>>'
> >
> > Also, you may want to try qualifying your table names, i.e.:
> >
> > SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> > fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND
> > fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039';
> >
> > Michael Fork - CCNA - MCP - A+
> > Network Support - Toledo Internet Access - Toledo Ohio
> >
> > On Tue, 6 Feb 2001, Brice Ruth wrote:
> >
> > > FYI: Here are the table definitions:
> > >
> > > CREATE TABLE TBLPEMDRUGLINK
> > > (
> > > DRUGID VARCHAR(10) NOT NULL,
> > > MONOGRAPHID VARCHAR(10) NOT NULL,
> > > CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
> > > );
> > >
> > > CREATE TABLE TBLFDBMONO
> > > (
> > > VERSIONID VARCHAR(10) NOT NULL,
> > > CATEGORY VARCHAR(10) NOT NULL,
> > > MONOGRAPHID VARCHAR(10) NOT NULL,
> > > SEQUENCENUMBER SMALLINT NOT NULL,
> > > SECTIONCODE VARCHAR(1),
> > > LINETEXT VARCHAR(255),
> > > CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
> > > MONOGRAPHID, SEQUENCENUMBER)
> > > );
> > >
> > > Running the following query:
> > >
> > > Query1: SELECT sequencenumber,sectioncode,linetext
> > > Query1: FROM tblfdbmono fdb, tblpemdruglink pem WHERE
> > > Query1: fdb.monographid=pem.monographid AND
> > > Query1: fdb.versionid='FDB-PE' AND
> > > Query1: fdb.category='PEM' AND
> > > Query1: pem.drugid='DG-5039';
> > >
> > > returns 0 rows.
> > >
> > > However, the following two queries produce results:
> > >
> > > Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';
> > >
> > > Query3: SELECT * FROM tblfdbmono WHERE
> > > Query3: monographid='2008' AND
> > > Query3: versionid='FDB-PE' AND
> > > Query3: category='PEM';
> > >
> > > To my knowledge, Query1 is the join that should produce the same results
> > > as the manual join represented by queries 2 & 3.
> > >
> > > What's going on?
> > >
> > > -Brice
> > >
> > > Ian Harding wrote:
> > > >
> > > > Brice Ruth wrote:
> > > >
> > > > > Greetings.
> > > > >
> > > > > I'm working with a product provided by a third part that interfaces to
> > > > > data housed in a database of your choice. Previously, my choice was
> > > > > MySQL - apparently it handled certain queries too slowly, so I'm giving
> > > > > PostgreSQL a shot. Here's the query:
> > > > >
> > > > > SELECT
> > > > > a.Number,
> > > > > a.Code,
> > > > > a.Text
> > > > > FROM
> > > > > b,
> > > > > a
> > > > > WHERE
> > > > > (b.Id = a.Id) AND
> > > > > (VersionId = 'key1') AND
> > > > > (Category = 'key2') AND
> > > > > (b.d_Id = 'key3')
> > > > > ORDER BY
> > > > > a.Number;
> > > > >
> > > > > (my apologies: I had to 'mangle' the table/column names because of NDA)
> > > > >
> > > > > So my question is this: would this query operate differently in MySQL
> > > > > than in PostgreSQL? The reason I ask is that this query in MySQL
> > > > > returns results, yet in PostgreSQL it does not. I read a post about
> > > > > PostgreSQL not supporting outer joins, but I don't have enough
> > > > > experience with SQL to determine if this is such a query or not. Please
> > > > >
> > > > > advise.
> > > > >
> > > > > Any help will be (as always) sincerely appreciated.
> > > > >
> > > > > --
> > > > > Brice Ruth
> > > > > WebProjkt, Inc.
> > > > > VP, Director of Internet Technology
> > > > > http://www.webprojkt.com/
> > > >
> > > > It should work the same in both. The only thing I notice is that not all
> > > > the field names are qualified with table names or aliases. That can lead
> > > > to ambiguity, but the query would blow up on both databases if that were a
> > > > problem.
> > > >
> > > > Ian
> > >
> > > --
> > > Brice Ruth
> > > WebProjkt, Inc.
> > > VP, Director of Internet Technology
> > > http://www.webprojkt.com/
> > >
>
> --
> Brice Ruth
> WebProjkt, Inc.
> VP, Director of Internet Technology
> http://www.webprojkt.com/
>
Here's my latest tactic: I'm guessing that the data 'corruption' has
something to do with the way the data was exported from the original
database by the third party ... probably something with the cr/lf
linebreaks or something to that effect (the data field in question
happens to be the last field in a line in the data file). So, I loaded
up all the files in my text editor (BBedit) and saved 'em back out w/
UNIX linebreaks, then transferred the files as a bzipped tar over to the
server. I deleted all the content from the db and am reimporting via
'COPY ... FROM' as I write this. Its about 100MB of data so it'll take
a few minutes to import.
I'll report back any successes ... if things fail again, I'll follow all
the advice I've gotten to this point then report back with any
discoveries. Thank you SO much for all the help with this. I really
appreciate it tremendously.
-Brice
Tom Lane wrote:
>
> Brice Ruth <brice@webprojkt.com> writes:
> > SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
> > I get 5 returned to me, even though the string is only 4 ('2008').
>
> Okay, so you've got some invisible character in there, but not a space
> (else the trim() woulda got rid of it). Tab maybe? Might want to go
> back and examine your original data file more carefully, with an editor
> that will show you control characters and such.
>
> regards, tom lane
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
Brice Ruth <brice@webprojkt.com> writes:
> Here's my latest tactic: I'm guessing that the data 'corruption' has
> something to do with the way the data was exported from the original
> database by the third party ... probably something with the cr/lf
> linebreaks or something to that effect (the data field in question
> happens to be the last field in a line in the data file).
Ooooh ... the queries you were showing us made it look like the column
was not the last one, so I hadn't thought of that. Almost certainly,
your extra character is a CR. Postgres expects plain LF as newline in
COPY data files; if the newlines are actually CR/LF then the CRs will
be taken as part of the last data field.
regards, tom lane
Is there a simple (unix) command I can run on text files to convert cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to mention time consuming). -Brice Tom Lane wrote: > > Brice Ruth <brice@webprojkt.com> writes: > > Here's my latest tactic: I'm guessing that the data 'corruption' has > > something to do with the way the data was exported from the original > > database by the third party ... probably something with the cr/lf > > linebreaks or something to that effect (the data field in question > > happens to be the last field in a line in the data file). > > Ooooh ... the queries you were showing us made it look like the column > was not the last one, so I hadn't thought of that. Almost certainly, > your extra character is a CR. Postgres expects plain LF as newline in > COPY data files; if the newlines are actually CR/LF then the CRs will > be taken as part of the last data field. > > regards, tom lane -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
Brice Ruth <brice@webprojkt.com> writes:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf?
You could strip out CRs entirely with
tr -d '\015'
regards, tom lane
heya there are a couple of good example on how to do this in the perl cookbook the trim function in chapter one might help the source code from the book is avail at ora.com
On Wed, 7 Feb 2001, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).
perl -pi -e 's/\cM\\g' <filename>
will do the trick, assuming you have Perl instaleld on your system.
-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Money will say more in one moment than the most eloquent lover can in years.
I believe the cr/lf was the problem. I reran the original query: SELECT tblFDBMono.SequenceNumber, tblFDBMono.SectionCode, tblFDBMono.LineText FROM tblPEMDrugLink, tblFDBMono WHERE (tblPEMDrugLink.MonographId = tblFDBMono.MonographId) AND (VersionId = 'FDB-PE') AND (Category = 'PEM') AND (tblPEMDrugLink.DrugId = 'DG-5039') ORDER BY tblFDBMono.SequenceNumber And was returned the results I expected. Sweetness. Damned stupid that it took me this long to figure this out ... I have the PostgreSQL book, but I must have missed the section where it talked about COPY ... FROM only taking the LF and leaving the CR. I knew the files were exported in 'DOS' format (according to vi) ... and in the back of my mind I thought that could screw things up. Hrmpf. At least I got it figured out. Thanx a ton, guys. "Brett W. McCoy" wrote: > > On Wed, 7 Feb 2001, Brice Ruth wrote: > > > Is there a simple (unix) command I can run on text files to convert > > cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to > > mention time consuming). > > perl -pi -e 's/\cM\\g' <filename> > > will do the trick, assuming you have Perl instaleld on your system. > > -- Brett > http://www.chapelperilous.net/~bmccoy/ > --------------------------------------------------------------------------- > Money will say more in one moment than the most eloquent lover can in years. -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
On Thu, 08 Feb 2001 05:38, Brice Ruth wrote: > Is there a simple (unix) command I can run on text files to convert > cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to > mention time consuming). This little heiroglyph will convert all text files rescued from that man's system in a single directory to the format required by the one true way. for f in `echo *`; # Change this to select files by regex if needed do echo $f; cat $f | tr -d "\015\032" > $f.unix; mv $f.unix $f; done Don't forget to remove the control Z file terminator as well as the superfluous CRs. It could cause very obtuse problems. Assumes you have write permission to the directory.
On Wed, Feb 07, 2001 at 10:38:53AM -0600, Brice Ruth wrote: > Is there a simple (unix) command I can run on text files to convert > cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to > mention time consuming). > > -Brice On many systems (linux at least) there is a command dos2unix, which is actually an alias for `recode ibmpc:`. But that will take care of more than just CR, e.g. umlauts, diacritics, etc.. Albert. -- -------------------------------------------------------------------------- Albert Reiner <areiner@tph.tuwien.ac.at> Deutsch * English * Esperanto * Latine --------------------------------------------------------------------------
On Wed, 7 Feb 2001, Brice Ruth wrote:
> Is there a simple (unix) command I can run on text files to convert
> cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to
> mention time consuming).
perl -pi~ -e 's/\r//g' file1 file2 ... fileN
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/