[SQL] error dropping operator
Hi there, I don't have experience with operators. I built one and when I tried to drop it I got th efollowing error: cannot drop operator ... because it is required by the database system. How do I drop it please ? TIA, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Table transform query
On 9/18/07, Philippe Lang <[EMAIL PROTECTED]> wrote: > ... into this: > > > serial dateL dateR > > 1 1 2 > 1 4 > 2 1 2 > 3 1 3 > 4 2 3 > 5 3 SELECT t1.serial, t1.DATE AS datel, t2.DATE AS dater FROM t t1 LEFT JOIN t t2 ON(t1.serial = t2.serial AND t1.DATE < t2.DATE) WHERE t1.delivery = 'L' AND ( t2.delivery = 'R' OR t2.delivery IS NULL) ORDER BY t1.serial ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Table transform query
Hi, I'm trying to find out how to transform this kind of table data (history of rental data in a firm): date serial delivery -- 1 1 L 1 2 L 1 3 L 2 1 R 2 2 R 2 4 L 3 5 L 3 3 R 3 4 R 4 1 L ... into this: serial dateL dateR 1 1 2 1 4 2 1 2 3 1 3 4 2 3 5 3 Basically, data on table 1 means: - on date 1, product with serial 1 is sent to the customer - on date 1, product with serial 2 is sent to the customer - on date 1, product with serial 3 is sent to the customer - on date 2, product with serial 1 comes back ... On table 2, data means: - Product with serial 1 is sent to the customer on date 1, and comes back on date 2 - Product with serial 1 is sent to the customer on date 4, and hasn't come back yet ... Do you think there is a generic SQL solution to this problem, like crosstab or pivot table? I'm thinking of doing things in a "procedural" plpgsql manner... Any idea is welcome. Thanks! Philippe ---(end of broadcast)--- TIP 1: 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
Re: [SQL] Table transform query
[EMAIL PROTECTED] wrote: > Hi, > > I'm trying to find out how to transform this kind of table data > (history of rental data in a firm): ... I have answred my own question: yes, there is a pure SQL solution, with a subselect: CREATE TABLE foo ( serial integer, delivery character(1), date integer ); INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (2, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (3, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (1, 'R', 2); INSERT INTO foo (serial, delivery, date) VALUES (2, 'R', 2); INSERT INTO foo (serial, delivery, date) VALUES (4, 'L', 2); INSERT INTO foo (serial, delivery, date) VALUES (5, 'L', 3); INSERT INTO foo (serial, delivery, date) VALUES (3, 'R', 3); INSERT INTO foo (serial, delivery, date) VALUES (4, 'R', 3); INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 4); - select f.serial, f.date as dateL, ( select f2.date from foo as f2 where f2.serial = f.serial and f2.date > f.date and f2.delivery = 'R' order by f2.date asc limit 1 ) as dateR from foo as f where f.delivery = 'L' order by f.serial, f.date - I'm not sure if we could use a self-join here... Cheers, Philippe Lang ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] error dropping operator
"Sabin Coanda" <[EMAIL PROTECTED]> writes: > I don't have experience with operators. I built one and when I tried to drop > it I got th efollowing error: cannot drop operator ... because it is > required by the database system. > How do I drop it please ? Well, you need to drop *your* operator, not the similarly named built-in one which is evidently what you tried to do. You have to watch out for search_path gotchas here --- most likely, you need to specify the schema containing your operator explicitly. regards, tom lane ---(end of broadcast)--- TIP 1: 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
Re: [SQL] Extracting hostname from URI column
Paul Lambert wrote: Paul Lambert wrote: chester c young wrote: I'm trying to use substr() and position() functions to extract the full host name (and later a domain) from a column that holds URLs. substring( href from '.*://\([^/]*)' ); Ok, your solution looks better than mine... but I have no idea how to interpret that, time to consult some manuals. OK - following on from this, I'm doing a similar thing to the OP to analyze my proxy's traffic (never occured to me to do it in a db until that post) I've done the above regex to pull out the domain part of the URL and am left with results such as: "acvs.mediaonenetwork.net" "profile.ak.facebook.com" "www.bankwest.com.au" What I want to do next is pull out the major domain part of the URL I.e. for the above three records I should end up with "mediaonenetwork.net" "facebook.com" "bankwest.com.au" What would be the best way to do something like that? I assume it won't be a simple regex like the above due to the country codes on the end of some domains. My thought is look at the last portion of the domain, if it's 2 characters long then assume it's a country code and grab the last three sections, if it's not three characters long then assume it's an international domain and grab the last two... but that sounds a bit dodgy. and csiro.au? There may be a few others in .au too, legacies from before we went commercial. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
