[SQL] inconsistent functionality with LIKE operator
I'm using PostgreSQL version 7.1, and I'm having trouble with the LIKE statement. How would I find the value "a\bc"? I tried using the backslash to escape it: LIKE 'a\\b%'; If I specify: LIKE 'a\\bc', then it works, but if I wanted it to look for consecetive characters after the c, using the %, then it returns other values besides what I'm looking for. I also have the same problem with percent (%). I have a test string with the value "ab%c", and I want to look for that using LIKE. I tried: LIKE 'ab\%c', LIKE 'ab%%', and LIKE 'ab\%_', but these statements not only returned what I was looking for, but also other values: "abc","ABC", and "Abc". Phuong ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] performance inconsistency
Good day,
We're running a week-old CVS snapshot of PostgreSQL 7.1, and I'm not
sure
if this performance inconsistency is specific to it, or if this is just
something in PostgreSQL in general, but it seems kind of odd, and I
could
use some help here. ;)
I have run two queries in a table full of invoices where the only
difference
between the queries is the where clause:
SELECT COUNT(DISTINCT l."ORDER_NUM") AS line_orders,
SUM(l."GROSS_PROFIT" + "REPLACEMENT_COST") AS grand_total,
SUM(l."LIST_PRICE" * l."SHIPPED_QUANTITY") AS
grand_total_alternative,
SUM(l."GROSS_PROFIT") AS grand_total_profit
FROM cu_invoice_li1_tbl l INNER JOIN cu_invoice_03_tbl h
ON l."ORDER_NUM"=h."ORDER_NUM"
WHERE substr("ORDER_NUM", 1, 1) != 'W'; # (Orders NOT prefixed with W.)
OR...
WHERE substr("ORDER_NUM", 1, 1) = 'I'; # (Orders prefixed with I.)
The first query returns more rows than the second (because it includes
orders prefixed with I, C, F, etc), whereas the second only returns
orders
prefixed with I. It seems that the logic of identifying a single
character
as *not* being a single value would be identical or at least similar in
efficiency to the logic of a single character *being* being a single
value.
However, the first query runs in about 10-15 seconds, and the second
query
ran for over 40 minutes before I cancelled it.
Now, I can easily use a series of "!=" statements to get it down so that
it's only the 'I' orders (which runs even faster, at about 5-6
seconds!),
but can anyone explain this to me? :)
Are "!=" substring evaluations inherently faster by an obscene order of
magnitude, or is something really wrong here?
J.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Shortcut?
I'm running Postgres 7.1. I read in the documentation that the from clause can be omitted from a select statement. The example given in the documentation is: SELECT distributors.* WHERE name = 'Westwood'; I tried it on tables in our database, but it doesn't work. Is that part of earlier versions of Postgres? Thanks ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] restoring a dump
Hello, I have been trying to restore my dump and it seems like it is not working. I put the dump in a tar file and when I untarred it, there are several .dat files, a toc.dat file, and a restore.sql file. I used psql to restore these files into a database. I first started restoring the restore.sql file, then the .dat files, and lastly, the toc.dat. When I did the toc.dat file, I received an error message. These are the pg_dump -C -D -F t > test.tar tar -xvf test.tar psql restore.sql psql test < 19.dat psql test < 20.dat .. and so on psql test < toc.dat This is the error message: ERROR: parser: parse error at or near "pgdmp" CHANGE ERROR: non-existent group "test" ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ I also tried using the pg_restore, but I don't know how that would work on these separate files. Is there another way I can restore the dump? Maybe an easier way than what I have been using? And do I need to restore what is in the toc.dat? Thank you for your help. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] restoring a dump
Peter Eisentraut wrote: > > Phuong Ma writes: > > > pg_dump -C -D -F t > test.tar > > tar -xvf test.tar > > psql restore.sql > > psql test < 19.dat > > psql test < 20.dat .. and so on > > psql test < toc.dat > > I believe you're supposed to use pg_restore on the tar file itself. > > -- > Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter I tried using pg_restore, but it did not restore the dump successfully. Maybe it is the syntax I'm using, but I remember reading that you cannot use the pg_restore to restore a dump that uses INSERT. Does anyone know of a way I could undump my db? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] trigger on DELETE
Hello everyone, I'm trying to define a trigger that copies the row to be deleted into another table (which is the inventory_audit table) before it does the delete from the original table (which is the inventory table). CREATE FUNCTION inv_audit_mod () RETURNS OPAQUE AS ' BEGIN NEW.user_aud := current_user; NEW.mod_time := ''NOW''; INSERT INTO inventory_audit SELECT * FROM inventory WHERE id=NEW.id; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER inv_audit_mod BEFORE DELETE ON inventory FOR EACH ROW EXECUTE PROCEDURE inv_audit_mod(); Ok, the function works only with a trigger that is defined as ON INSERT OR UPDATE. If I try to define a trigger for ON DELETE and then delete a row from the table, there is nothing in the 'NEW' variable to return. I get an error message. If I define the function to return NULL, 0, or nothing, then it comes up with a type mis-match error. Is there anyone who can help? Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
