[SQL] Load or Copy ??
Hi
What is wrong?
$result = pg_Exec($db,"COPY forum FROM {'datei.csv' | stdin} USING
DELIMITERS '|';");
I will load datei.csv into forum!
This is for MySQL:
$result = pg_Exec($db,"LOAD DATA INFILE 'datei.csv' INTO TABLE forum FIELDS
TERMINATED BY '|' LINES TERMINATED BY '\n'");
And for PostgreSQL:
Regards, Sebastian
Re: [SQL] SQL Join - MySQL/PostgreSQL difference?
Here's some more details:
The following query:
SELECT * FROM tblpemdruglink WHERE drugid = 'DG-5039';
returns the following results:
drugid | monographid
-+-
DG-5039 | 2008
(1 row)
the following query:
SELECT * FROM tblfdbmono WHERE
monographid='2008' AND
versionid='FDB-PE' AND
category='PEM';
returns the following results:
versionid | category | monographid | sequencenumber | sectioncode |
linetext
---+--+-++-+-
{content}
sorry, the {content} is what's under NDA :(
In any case, you can see the structure of the tables tblpemdruglink and
tblfdbmono from here.
Now the following query:
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';
Should return the same results as the second query above. However, this
is what I get:
sequencenumber | sectioncode | linetext
+-+--
(0 rows)
And it takes about 3-4 seconds to return with that prognosis. What's
going on here?
-Brice
Peter Eisentraut wrote:
>
> Brice Ruth writes:
>
> > 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.
>
> Without showing the tables and the data in it, it's fairly hard to tell.
> I think MySQL does case insensitive string comparisons; check that.
>
> > 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.
>
> This is not an outer join.
>
> --
> Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
Re: [SQL] SQL Join - MySQL/PostgreSQL difference?
David, About the case-sensitivity, I was under the impression that PostgreSQL was case-insensitive unless things were explicitly put in quotes. This is at least what I read in the book ... -Brice David Olbersen wrote: > > On Thu, 1 Feb 2001, Brice Ruth wrote: > > ->SELECT > -> a.Number, > -> a.Code, > -> a.Text > ->FROM > -> b, > -> a > ->WHERE > -> (b.Id = a.Id) AND > > These next two statements are very ambiguous. Make them explicit as you have > with "(b.Id = a.Id)" and "(b.d_Id = 'key3')" > > Also, be sure that 'key3' is how what you want looks in the database > > -> (VersionId = 'key1') AND > -> (Category = 'key2') AND > -> (b.d_Id = 'key3') > ->ORDER BY > -> a.Number; > > Also, make sure ( '\d b' ) that your columns are case-sensatively named 'Id' and > such as this does matter. > > -- Dave -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
[SQL] Returning a row from a function
Hi,
This is my first experinece in writing to u. Please excuse any obvious errors.
I have only recently started using the postgres database and i would like to know if
it is possible to return a record from the fucntion(both sql and plpgsql).
I tried this code.. but the reuslt i got was
create function ret_rec(char(10)) returns setof label as
'
select * from label where label_id = $1'
language 'sql';
hmv=# select ret_rec('L0001');
?column?
---
136449928
I also tried this..
create function ret_rec(char(10)) returns label as
'
declare
label_rec record;
begin
select into label_rec * from label where label_id = $1;
return label_rec;
end;'
language 'plpgsql';
hmv=# select ret_rec('L0001');
ret_rec
---
136469400
(1 row)
What is the problem and what is this value that is being returned? Is there any other
way in which i can handle this?
Please help.
Also, please tell me if there is any way in which i can pass values to a plpgsql
function that is being called by a trigger?(ie) this function exceutes when a trigger
fires.. can i pass arguments to this function?
Regards and Thanx in advance
Deepa.
Chequemail.com - a free web based e-mail service that also pays!!!
http://www.chequemail.com
[SQL] Datatype in SQL Server 2000
Is it possible to know why datetime datatype does not accept null value? It does at the beginning but if I enter a value and then decide to leave it blank it won't accept "WHY"? PLEASE if you can help write me back... THANK YOU
Re: [SQL] Postgres-HOWTO
> > Has been removed from the LDP website. > >>Good news indeed! >> >>Now what are we going to do with it? >> >>Can the original document's source be made available so that >>somebody can do the needed work without having to re-key. >> >>There is a _lot_ of very good information in there buried underneath the >>... um ... > >If there's any help with doing a rewrite let me know if I could >help. Writing/instruction has turned out to be a strong point for me... Of course, this is when I'm not writing fast and loose :)
Re: [SQL] Postgres-HOWTO
> Has been removed from the LDP website. >Good news indeed! > >Now what are we going to do with it? > >Can the original document's source be made available so that >somebody can do the needed work without having to re-key. > >There is a _lot_ of very good information in there buried underneath the >... um ... If there's any help with doing a rewrite let me know if I could help. Writing/instruction has turned out to be a strong point for me... Thomas
Re: [SQL] Postgres-HOWTO
the LDP project doesn't allow for 'taking over' someone elses work, but
Thomas is working the sites maintainer on an appropriate solution for the
problem, as even though its removed, it will come back again *groan*
On Tue, 6 Feb 2001, Thomas Swan wrote:
> > Has been removed from the LDP website.
>
> >Good news indeed!
> >
> >Now what are we going to do with it?
> >
> >Can the original document's source be made available so that
> >somebody can do the needed work without having to re-key.
> >
> >There is a _lot_ of very good information in there buried underneath the
> >... um ...
>
> If there's any help with doing a rewrite let me know if I could
> help. Writing/instruction has turned out to be a strong point for me...
>
> Thomas
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [SQL] SQL Join - MySQL/PostgreSQL difference?
Brice Ruth <[EMAIL PROTECTED]> writes: > About the case-sensitivity, I was under the impression that PostgreSQL > was case-insensitive unless things were explicitly put in quotes. Names in queries (of tables, fields, functions, etc) are case-insensitive. This has nothing to do with the behavior of data comparisons, however. regards, tom lane
Re: [SQL] SQL Join - MySQL/PostgreSQL difference?
That was my understanding as well, thank you for the clarification. :) Tom Lane wrote: > > Brice Ruth <[EMAIL PROTECTED]> writes: > > About the case-sensitivity, I was under the impression that PostgreSQL > > was case-insensitive unless things were explicitly put in quotes. > > Names in queries (of tables, fields, functions, etc) are > case-insensitive. This has nothing to do with the behavior of data > comparisons, however. > > regards, tom lane -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
Re: [SQL] Load or Copy ??
Hi
try
COPY forum FROM 'datei.csv' USING DELIMITERS '|';
You should use either 'filename' OR stdin, not both.
Hope it helps.
Regards,
Patrik Kudo
--
ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol
Känns det oklart? Fråga på!
On Tue, 6 Feb 2001, Sebastian wrote:
> Hi
>
> What is wrong?
>
> $result = pg_Exec($db,"COPY forum FROM {'datei.csv' | stdin} USING
> DELIMITERS '|';");
>
> I will load datei.csv into forum!
>
> This is for MySQL:
>
> $result = pg_Exec($db,"LOAD DATA INFILE 'datei.csv' INTO TABLE forum FIELDS
> TERMINATED BY '|' LINES TERMINATED BY '\n'");
>
> And for PostgreSQL:
>
>
> Regards, Sebastian
>
>
>
>
Re: [SQL] Load or Copy ??
Patrik Kudo <[EMAIL PROTECTED]> writes: > COPY forum FROM 'datei.csv' USING DELIMITERS '|'; > You should use either 'filename' OR stdin, not both. Check. You'll probably also find that you need to supply an absolute pathname, since the backend is not running in the same directory that your client is in. regards, tom lane
Re: [SQL] Is this a bug, or is it just me?
Technically you are not allowed to make an FK to non-unique values. What you're closer to looking for is MATCH PARTIAL which we don't support (because it's a real pain - although with the new memory management stuff in 7.1 it may be less of one - since the fundamental problem is storing values from other iterations of the trigger for this last update/delete for ref actions). 7.1 won't let you define such a constraint with the create table or alter table syntaxes (I guess theoretically it would let you create constraint trigger and bring the broken constraint from an older version). Right now we don't support constraining views because we don't have a mechanism in place to rewrite the constraint to actually work. On Tue, 6 Feb 2001, Josh Berkus wrote: > Tom et al. > > Discovered this quirk in foriegn keys: > > In the preliminary version of a database, I added foriegn > key constraints to a number of tables, linking them to a > column in a shared reference table (status.status) that was > only one-half of a composite primary key (and thus the > values were not unique). When I tried to delete a row > containing a "2" in the status column from the status > relation, I received a Foreign Key violation error event > though there were other "2"'s in the table still present. > > So ... is this a bug in forign key implementation, or just > my fault for keying off a non-unique value? > > And, if the latter, is there a way I can construct a foreign > key constraint that keys onto a view or query?
[SQL] Use of RETURN in pl/pgsql function
My script is below. I thought (based on recent posts) that this use of RETURN is allowed, but when trying an insert to report_table, I get the following error: ERROR: control reaches end of trigger procedure without RETURN I have solved several problems in getting to this point, but have now run out of ideas. I would appreciate any pointers. jeffe@kiyoko=> uname -a FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27 10:44:07 CDT 2000 jeffe@kiyoko=> psql -V psql (PostgreSQL) 7.0.0 Script: drop function mrr(); create function mrr() returns opaque as ' begin if NEW.billing_frequency = ''Monthly'' -- That's doubled single quotes (and below as well) then return NEW; else if NEW.billing_frequency = ''Yearly'' then NEW.rate := NEW.rate/12; NEW.rate_override := NEW.rate_override/12; return NEW; else if NEW.billing_frequency = ''Semi-Annual'' then NEW.rate := NEW.rate/6; NEW.rate_override := NEW.rate_override/6; return NEW; else if NEW.billing_frequency = ''Quarterly'' then NEW.rate := NEW.rate/3; NEW.rate_override := NEW.rate_override/3; return NEW; end if; end if; end if; end if; end; 'language 'plpgsql'; drop trigger mrr_set_trigger on report_table; create trigger mrr_set_trigger before insert on report_table for each row execute procedure mrr();
Re: [SQL] Use of RETURN in pl/pgsql function
Jeff Eckermann <[EMAIL PROTECTED]> writes: > I thought (based on recent posts) that this use of RETURN is allowed, but > when trying an insert to report_table, I get the following error: > ERROR: control reaches end of trigger procedure without RETURN Looks to me like you didn't cover the case where billing_frequency is not any of the values you tested for. Maybe you just want to raise an error in that case... regards, tom lane
[SQL] Bug reports for 7.1 beta?
Folks, Where do I send bug reports for 7.1 beta? I;'ve looked on the web site, and don't see an address or bugtraq forum. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
RE: [SQL] Use of RETURN in pl/pgsql function
I'll be hornswoggled: that works. I hadn't expected a problem, simply because I knew the range in advance: here is the distribution for the select that I was working with: extracts=# select billing_frequency, count (*) from report_table group by billing_frequency; billing_frequency | count ---+--- Monthly | 50431 Quarterly | 7742 Semi-Annual | 167 Yearly| 8573 (4 rows) Nevertheless, just adding an "...else return new;" did the trick. I don't understand the logic of this, but then, if I stopped to consider every mystery, I'd never get any work done:-). Thanks very much for your help (again). > -Original Message- > From: Tom Lane [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, February 07, 2001 12:48 PM > To: Jeff Eckermann > Cc: '[EMAIL PROTECTED]' > Subject: Re: [SQL] Use of RETURN in pl/pgsql function > > Jeff Eckermann <[EMAIL PROTECTED]> writes: > > I thought (based on recent posts) that this use of RETURN is allowed, > but > > when trying an insert to report_table, I get the following error: > > ERROR: control reaches end of trigger procedure without RETURN > > Looks to me like you didn't cover the case where billing_frequency is > not any of the values you tested for. Maybe you just want to raise > an error in that case... > > regards, tom lane
Re: [SQL] Use of RETURN in pl/pgsql function
Jeff Eckermann <[EMAIL PROTECTED]> writes: > I'll be hornswoggled: that works. > I hadn't expected a problem, simply because I knew the range in advance: Odd. Maybe there is a compile-time, rather than run-time, check in plpgsql for falling off the end without a RETURN? I haven't looked ... regards, tom lane
Re: [SQL] Bug reports for 7.1 beta?
On Wed, 7 Feb 2001, Josh Berkus wrote: > Folks, > > Where do I send bug reports for 7.1 beta? I;'ve looked on the web > site, and don't see an address or bugtraq forum. Probably the best is the pgsql-bugs mailing list at: [EMAIL PROTECTED]
Re: [SQL] Use of RETURN in pl/pgsql function
You may try like: if block end if; return somefakething; no matter this return can be reached or not. then compile will be no problem. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 7 Feb 2001, Jeff Eckermann wrote: > My script is below. > > I thought (based on recent posts) that this use of RETURN is allowed, but > when trying an insert to report_table, I get the following error: > > ERROR: control reaches end of trigger procedure without RETURN > > I have solved several problems in getting to this point, but have now run > out of ideas. I would appreciate any pointers. > > jeffe@kiyoko=> uname -a > FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27 > 10:44:07 CDT 2000 > jeffe@kiyoko=> psql -V > psql (PostgreSQL) 7.0.0 > > Script: > > drop function mrr(); > > create function mrr() returns opaque as ' > > begin > > if NEW.billing_frequency = ''Monthly'' -- That's doubled single > quotes (and below as well) > > then > > return NEW; > > else > > if NEW.billing_frequency = ''Yearly'' > > then > > NEW.rate := NEW.rate/12; > > NEW.rate_override := NEW.rate_override/12; > > return NEW; > > else > > if NEW.billing_frequency = ''Semi-Annual'' > > then > > NEW.rate := NEW.rate/6; > > NEW.rate_override := NEW.rate_override/6; > > return NEW; > > else > > if NEW.billing_frequency = ''Quarterly'' > > then > > NEW.rate := NEW.rate/3; > > NEW.rate_override := NEW.rate_override/3; > > return NEW; > > end if; > > end if; > > end if; > > end if; > > end; > > 'language 'plpgsql'; > > drop trigger mrr_set_trigger on report_table; > > create trigger mrr_set_trigger > > before insert on report_table > > for each row execute procedure mrr(); >
Re: [ADMIN] FATAL 1: btree: items are out of order (leftmost 0, stack 48, update 2)
"Michael Miyabara-McCaskey" <[EMAIL PROTECTED]> writes: > FATAL 1: btree: items are out of order (leftmost 0, stack 48, update 2) This appears to indicate that you've got a corrupted index, possibly due to carelessness about starting the postmaster always in the same locale (see past discussions about effects of locale on index sort order). If you can determine which index is broken, dropping and rebuilding it should get you out of trouble. regards, tom lane
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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 <[EMAIL PROTECTED]> 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/
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
Brice Ruth <[EMAIL PROTECTED]> 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
[GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
Brice Ruth <[EMAIL PROTECTED]> 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
[GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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 = '<>' AND fdb.category = '<>' AND
> > pem.drugid = '<>'
> >
> > 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,
> > > CATEGORYVARCHAR(10) NOT NULL,
> > > MONOGRAPHID VARCHAR(10) NOT NULL,
> > > SEQUENCENUMBER SMALLINT NOT NULL,
> > > SECTIONCODE VARCHAR(1),
> > > LINETEXTVARCHAR(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.
> > > > >
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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 <[EMAIL PROTECTED]> 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/
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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.
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
Brice Ruth <[EMAIL PROTECTED]> 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
[GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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 = '<>' AND fdb.category = '<>' AND pem.drugid = '<>' 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, > CATEGORYVARCHAR(10) NOT NULL, > MONOGRAPHID VARCHAR(10) NOT NULL, > SEQUENCENUMBER SMALLINT NOT NULL, > SECTIONCODE VARCHAR(1), > LINETEXTVARCHAR(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/ >
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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 = '<>' AND fdb.category = '<>' AND
> pem.drugid = '<>'
>
> 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,
> > CATEGORYVARCHAR(10) NOT NULL,
> > MONOGRAPHID VARCHAR(10) NOT NULL,
> > SEQUENCENUMBER SMALLINT NOT NULL,
> > SECTIONCODE VARCHAR(1),
> > LINETEXTVARCHAR(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, D
Re: [GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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/
[SQL] FATAL 1: btree: items are out of order (leftmost 0, stack 48, update 2)
FATAL 1: btree: items are out of order (leftmost 0, stack 48, update 2) pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. Anyone have any ideas? I was running a script that calls "uc" from Perl... It runs for a while then I get this error. PGSQL 7.0.3, Linux RH 6.2 (2.2.17-RAID), 1GB MEM (gets to about 300MB of RAM used then dies). Michael Miyabara-McCaskey Email: [EMAIL PROTECTED] Web: http://www.miyabara.com/mykarz/ Mobile: +1 408 504 9014
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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
Re: [GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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' 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.
Re: [GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
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' > > 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/
[HACKERS] PL/pgsql EXECUTE 'SELECT INTO ...'
I have looked a little bit at what it'd take to make SELECT INTO inside an EXECUTE work the same as it does in plain plpgsql --- that is, the INTO should reference plpgsql variables, not a destination table. It looks to me like this is possible but would require some nontrivial re-engineering inside plpgsql. What I'm visualizing is that EXECUTE should read its string argument not just as an SPI_exec() string, but as an arbitrary plpgsql proc_stmt. This would offer some interesting capabilities, like building a whole FOR-loop for dynamic execution. But there are a number of problems to be surmounted, notably arranging for the parsetree built by the plpgsql compiler not to be irretrievably memory-leaked. (That ties into something I'd wanted to do anyway, which is to have the plpgsql compiler build its trees in a memory context associated with the function, not via malloc().) This does not look like something to be tackling when we're already in late beta, unfortunately. So we have to decide what to do for 7.1. If we do nothing now, and then implement this feature in 7.2, we will have a backwards compatibility problem: EXECUTE 'SELECT INTO ...' will completely change in meaning. I am inclined to keep our options open by forbidding EXECUTE 'SELECT INTO ...' for now. That's more than a tad annoying, because that leaves no useful way to do a dynamically-built SELECT, but if we don't forbid it I think we'll regret it later. Comments? regards, tom lane
Re: [SQL] PL/pgsql EXECUTE 'SELECT INTO ...'
Tom, > I have looked a little bit at what it'd take to make > SELECT INTO inside > an EXECUTE work the same as it does in plain plpgsql --- > that is, the > INTO should reference plpgsql variables, not a > destination table. > It looks to me like this is possible but would require > some nontrivial > re-engineering inside plpgsql. What I'm visualizing is > (That ties into something I'd wanted to > do anyway, > which is to have the plpgsql compiler build its trees in > a memory > context associated with the function, not via malloc().) All of this sounds good, but as a *heavy* PL/pgSQL user, it's still going off on somewhat of a tangent. As far as I'm concerned, the EXECUTE method was just a workaround for the lack "object" variables. What I always would rather have had is simply being able to drop a variable ... or an OID ... into a SELECT statement and not bothering with EXECUTE at all. > This does not look like something to be tackling when > we're already > in late beta, unfortunately. I'd agree with that. :-) > I am inclined to keep our options open by forbidding > EXECUTE 'SELECT > INTO ...' for now. That's more than a tad annoying, > because that leaves > no useful way to do a dynamically-built SELECT, but if we > don't forbid > it I think we'll regret it later. Unfortunately, I have already used EXECUTE in several functions ... my search routines will be hard to run without it. Perhaps you could turn off EXECUTE by default, but allow it as a compile-time option for those of us wise enough to understand the dangers? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
[SQL] Error while restoring tables from pgdump
Hai,
I have taken pgdump of a table which contains "serial" datatype. When i
try to populate it into another database the sequence is not created.
Why it is so?
Please help me,
Ramesh
I took pgdump of the table using
pg_dump olddb -t grms_filter_table_list > tables.pgdump
i restored the table in another database using
cat tables.pgdump | psql newdb
i have the table in new database whose str is as follows
Table "grms_filter_table_list"
Attribute |Type | Modifier
+-+---
table_id | integer | not null default
nextval('grms_filter_table__table_id_seq'::text)
table_name | varchar(50) |
disp_as| varchar(50) |
Index: grms_filter_table__table_id_key
when i try to insert new record, i get following error
ERROR: Relation 'grms_filter_table__table_id_seq' does not exist
Please advice me as how to take the pgdump with serial data type (ie
table which has sequence)
Thanks in advance
Ramesh HR
begin:vcard
n:HR;Ramesh
tel;fax:6675274
tel;home:91-0821-498071
tel;work:91-080-6601086/ 91-080-6602365/ 91-080-6672984 Extn. 155
x-mozilla-html:FALSE
url:www.easi.soft.net
org:EASi Technologies;IT Services
version:2.1
email;internet:[EMAIL PROTECTED]
adr;quoted-printable:;;213, 3rd Main,=0D=0A4th Cross,=0D=0AChamrajpet ;Bangalore;Karnataka;560 018;India
x-mozilla-cpt:;20992
fn:Ramesh HR
end:vcard
[SQL] RE: [ADMIN] FATAL 1: btree: items are out of order (leftmost 0, stack 48, update 2)
Tom, Once again you are a lifesaver. Thank you. I ended up just dropping all the indexes on the table in question, and life is now good. -Michael > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 07, 2001 4:42 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [ADMIN] FATAL 1: btree: items are out of order > (leftmost 0, > stack 48, update 2) > > > "Michael Miyabara-McCaskey" <[EMAIL PROTECTED]> writes: > > FATAL 1: btree: items are out of order (leftmost 0, stack > 48, update 2) > > This appears to indicate that you've got a corrupted index, > possibly due > to carelessness about starting the postmaster always in the > same locale > (see past discussions about effects of locale on index sort order). > > If you can determine which index is broken, dropping and rebuilding it > should get you out of trouble. > > regards, tom lane >
