Re: [SQL] skip weekends
On Thu, 2002-06-20 at 23:08, Rudi Starcevic wrote: > Hello, > > Nice reply Josh. > I wouldn't call your solution 'ugly' at all. > > It's an excellent example of a real world need for Postgresql functions. > I've also been looking at other functions at > http://www.brasileiro.net/postgres/cookbook/. > I noticed your name amongst the author's -- nice one -- keep up the good > work. > My only problem is trying to decide on whether to use PL/pgSQL or PLPerl. You might also consider PL/Python. I haven't used it, but I use Python a lot outside of PG and it's an excellent language. http://developer.postgresql.org/docs/postgres/plpython.html Regards, Cliff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to sort a birthday list ?
Dear Josh, if you sort by AGE then the order will be young -> old or vice versa. I'd like to have the list sorted as interval birthDAY, birthMONTH and DAY from NOW() and MONTH from NOW(). example: 22.06.64 Person-1 26.06.50 Person-2 01.08.69 Person-3 02.08.71 Person-4 ... of course you could sort by DOY but then you'll have a problem w/ the next year: if it's let's say december and you select the list for the next 60 days, persons having birthday in december will appear after persons having birthday in january. I tried to use CASE WHEN... THEN in ORDER BY but it doesn't seem to work (syntax error at '') M. Josh Berkus wrote: > Michael, > > >>SELECT * >>FROM Persons >>WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( >>CURRENT_DATE+60, dateofbirth ) ) >> >>... but how do I sort the list ? >> > > Easy: > > SELECT person_name, person_department, EXTRACT( YEAR FROM AGE(dateofbirth) ) > as their_age > FROM Persons > WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( > CURRENT_DATE+60, dateofbirth ) ) > ORDER BY their_age, person_name > > As an example. > > > > > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to sort a birthday list ?
David Stanaway wrote: > > How about: > ORDER BY dateofbirth > > > doesn't work: it's sorted by YEAR ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] date_ge and time_ge
Hello I have a question regarding date_ge() and time_ge(). This statement works : select * from userlog where date_ge(ul_timestamp, '20.06.2002') This statement doesn't work : select * from userlog where time_ge(ul_timestamp, '08:00:00') Here are some records from userlog : (See attached file: userlog.htm) __ PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland internet : www.pup.ch phone : +4141 790 4040 fax : +4141 790 2545 mobile: +4179 211 0315 __ Title: Query Results Query Results Executed: 21.06.2002 11:34:29 Query: select * from userlog limit 10 pk_userlog ul_timestamp ul_benutzer ul_access ul_ipadr ul_bereich ul_auftrag ul_zylinder ul_text 1001 19.06.2002 08:53:26 basler 10 172.16.30.7 OrderActionAdd 70045291 /IKE/0017/4/00/40/800/02/s1 /5/ 1002 19.06.2002 09:33:26 basler 10 172.16.30.7 Logout User logged out ! 1003 19.06.2002 09:33:36 basler 10 172.16.30.7 Login User logged in ! 1004 19.06.2002 09:33:53 basler 10 172.16.30.7 OrderCylinderAction 8828 Cylinder removed from order ! 1005 19.06.2002 09:33:55 basler 10 172.16.30.7 OrderCylinderAction 8804 Cylinder removed from order ! 1006 19.06.2002 09:33:56 basler 10 172.16.30.7 OrderCylinderAction 8805 Cylinder removed from order ! 1007 19.06.2002 09:33:57 basler 10 172.16.30.7 OrderCylinderAction 8806 Cylinder removed from order ! 1008 19.06.2002 09:33:59 basler 10 172.16.30.7 OrderCylinderAction 8822 Cylinder removed from order ! 1009 19.06.2002 09:34:00 basler 10 172.16.30.7 OrderCylinderAction 8808 Cylinder removed from order ! 1010 19.06.2002 09:34:01 basler 10 172.16.30.7 OrderCylinderAction 8817 Cylinder removed from order ! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] date_ge and time_ge
On Fri, 21 Jun 2002 [EMAIL PROTECTED] wrote: > Hello > > I have a question regarding date_ge() and time_ge(). > > This statement works : > > select * from userlog where date_ge(ul_timestamp, '20.06.2002') > > > This statement doesn't work : > > select * from userlog where time_ge(ul_timestamp, '08:00:00') > See the large list of functions of pgsql. Use date_part, castings, etc... > > Here are some records from userlog : > > (See attached file: userlog.htm) > > __ > > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > internet : www.pup.ch > phone : +4141 790 4040 > fax : +4141 790 2545 > mobile: +4179 211 0315 > __ > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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
Re: [SQL] how to sort a birthday list ?
On Fri, Jun 21, 2002 at 10:30:54 +0200, Michael Agbaglo <[EMAIL PROTECTED]> wrote: > > of course you could sort by DOY but then you'll have a problem w/ the > next year: > > if it's let's say december and you select the list for the next 60 days, > persons having birthday in december will appear after persons having > birthday in january. > > I tried to use CASE WHEN... THEN in ORDER BY but it doesn't seem to work > (syntax error at '') You also need to worry about leap years. If a birthday is February 29 and there isn't one this year, what do you want to happen? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to sort a birthday list ?
Bruno Wolff III wrote: > > On Fri, Jun 21, 2002 at 10:30:54 +0200, > Michael Agbaglo <[EMAIL PROTECTED]> wrote: > > > > of course you could sort by DOY but then you'll have a problem w/ the > > next year: > > > > if it's let's say december and you select the list for the next 60 days, > > persons having birthday in december will appear after persons having > > birthday in january. > > > > I tried to use CASE WHEN... THEN in ORDER BY but it doesn't seem to work > > (syntax error at '') > > You also need to worry about leap years. If a birthday is February 29 > and there isn't one this year, what do you want to happen? You can create a little PL/pgSQL function like this: CREATE FUNCTION next_birthday(date) RETURNS date AS ' DECLARE p_dob ALIAS FOR $1; v_age integer; v_birthday date; BEGIN -- First we get the age in years v_age := EXTRACT (YEAR FROM CURRENT_DATE) - EXTRACT (YEAR FROM p_dob); -- We add that to the DOB to get this years birthday v_birthday := p_dob + (v_age::text || '' years'')::interval; -- If that is in the past, we add another year IF v_birthday < CURRENT_DATE THEN v_birthday := v_birthday + ''1 year''::interval; END IF; RETURN v_birthday; END;' LANGUAGE plpgsql; It just calculates the next birthday of a person relative from today. Then query with SELECT next_birthday(birthday), name FROM person ORDER BY 1; Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Optimizer question with equivalent joins
Hello, say I have a join which says t.a = t.b and t.b = t.c do I need to give the optimizer a hint by saying it more redundantly t.a = t.b and t.b = t.c and t.c = t.a or is this just counter productive because there is one more join? In the real world I have 10-20 equivalent joins which would really blow up if I have to choose the second option. Dirk ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Optimizer question with equivalent joins
Dirk Lutzebaeck <[EMAIL PROTECTED]> writes: > say I have a join which says > t.a = t.b and t.b = t.c > do I need to give the optimizer a hint by saying it more redundantly > t.a = t.b and t.b = t.c and t.c = t.a Not since about 7.0.3 ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [ADMIN] Incredible..
On Fri, 21 Jun 2002 09:57:20 -0500 Luis Andaluz P, wrote: > Hello, > see this interesting file. > Bye. > And see this interesting URL: http://www.brocku.ca/its/helpdesk/virusalerts/viruses.phtml?vid=75 -- Cliff Wells, Software Engineer Logiplex Corporation (www.logiplex.net) (503) 978-6726 x308 (800) 735-0555 x308 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [ADMIN] Incredible..
On Fri, 2002-06-21 at 11:02, Cliff Wells wrote: > On Fri, 21 Jun 2002 09:57:20 -0500 > Luis Andaluz P, wrote: > > > Hello, > > see this interesting file. > > Bye. > > > > And see this interesting URL: > > http://www.brocku.ca/its/helpdesk/virusalerts/viruses.phtml?vid=75 And this mail from my virus scanner: > A virus was found in an email from: <[EMAIL PROTECTED]> The message was addressed to: -> <[EMAIL PROTECTED]> The message has been quarantined as: /var/virusmails/virus-20020621-104030-30552 Here is the output of the scanner: Scanning /var/amavis/amavis-milter-xZtvqxe2/parts/* Scanning file /var/amavis/amavis-milter-xZtvqxe2/parts/msg-30552-1.txt Scanning file /var/amavis/amavis-milter-xZtvqxe2/parts/msg-30552-2.html Scanning file /var/amavis/amavis-milter-xZtvqxe2/parts/msg-30552-3.exe /var/amavis/amavis-milter-xZtvqxe2/parts/msg-30552-3.exe Found the W32/Higuy@MM virus !!! Summary report on /var/amavis/amavis-milter-xZtvqxe2/parts/* File(s) Total files: ... 3 Clean: . 2 Possibly Infected: . 1 Here are the headers: - BEGIN HEADERS - Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 20A31476F2A; Fri, 21 Jun 2002 11:40:13 -0400 (EDT) Received: from postgresql.org (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with SMTP id D5B67477260; Fri, 21 Jun 2002 11:31:30 -0400 (EDT) Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 44B42477003 for <[EMAIL PROTECTED]>; Fri, 21 Jun 2002 11:30:43 -0400 (EDT) Received: from proxserv.orbis-corp.com (unknown [64.35.169.125]) by postgresql.org (Postfix) with ESMTP id 1CB81476339 for <[EMAIL PROTECTED]>; Fri, 21 Jun 2002 11:14:24 -0400 (EDT) Received: from jparrao ([192.168.1.40]) by proxserv.orbis-corp.com (8.11.0/8.8.7) with SMTP id g5LEvH218071; Fri, 21 Jun 2002 09:57:20 -0500 Date: Fri, 21 Jun 2002 09:57:20 -0500 Message-Id: <[EMAIL PROTECTED]> From: "Luis Andaluz P," <[EMAIL PROTECTED]> To: Subject: [ADMIN] Incredible.. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_NextPart_000_000C_01A516B1.1F066B30" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Frali' ViRii v.3 by 4nt4R35 (June2002) X-MimeOLE: Don'tWorry:It'sNotDangerous.ILoveTheWorldAndThePeople.Bye. Precedence: bulk Sender: [EMAIL PROTECTED] -- END HEADERS -- > -- > Cliff Wells, Software Engineer > Logiplex Corporation (www.logiplex.net) > (503) 978-6726 x308 (800) 735-0555 x308 > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] rowtype and ecpg
I have a function that takes as a parameter ROWTYPE: create or replace function test_func(test_table) returns varchar as ' declare lv_return varchar; begin .. return lv_return; end; ' LANGUAGE 'plpgsql'; How do I call this function from the C program (ecpg)? How my declaration should look like? I trued structure and got error: Too many arguments Thanks for your help ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Joining three data sources.
Am Mittwoch, 19. Juni 2002 16:09 schrieb Masaru Sugawara: > On Wed, 19 Jun 2002 12:33:47 +0200 > > Janning Vygen <[EMAIL PROTECTED]> wrote: > > - > > Result Inter Mailand vs. AC ROM 2:1 > > team1_id|team2_id|goals1|goals2 > > 1 2 2 1 > > SELECT go1.game_id, go1.team1_id, go1.team2_id, > SUM(CASE WHEN go2.team_id = go1.team1_id > THEN go2.n ELSE 0 END) AS goals1, > SUM(CASE WHEN go2.team_id = go1.team2_id > THEN go2.n ELSE 0 END) AS goals2 > FROM (SELECT game_id, > min(team_id) AS team1_id, > max(team_id) AS team2_id > FROM goal > GROUP BY 1) AS go1, > (SELECT game_id, team_id, count(*) AS n >FROM goal > GROUP BY 1, 2) AS go2 > WHERE go1.game_id = go2.game_id > GROUP BY 1, 2, 3; Oh thanks a lot. You pushed me in the right direction. i still get headache when trying to write complicated selects. there was something wrong in your statement but i was able to correct it by myself. Thanks for your help!! Are you able to type those queries in minutes?? It seems so ... amazing! > As for Goal table, if it has a large number of the rows, you maybe > need to create a unique index on it. of course. it was just an example... kind regards janning ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] is it easy to change the create sequence algorithm?
I see in the docs that when I create a column that is of type SERIAL, the engine automatically creates the sequence for me, named TABLE_COLUMN_seq. That's great until the table name + column name lengths are > 27 chars, then it starts chopping, and you guessed it, I have multiple table/column combinations that don't differ until after that length. Is there a way to influence the "create sequence" generator with a directive, hint, set value, whatever, to be something else? (e.g. COLUMN_seq if I guarantee all the columns are unique) Yes I know that I could create the sequence myself, but the engine does such a good job. :-) Thanks, Kevin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Presenting consistent data
Hi all, Suppose I have two tables, pluses and minuses, and for any given id I maintain the invariant sum(pluses.plus) - sum(minuses.minus) = 0 At a given point in time the tables may be like this: pluses idplus desc 1 100 'a' 1 150 'b' 1 25 'c' 2 80 'a' 2 70 'c' minuses id minus desc 1 120 'd' 1 80 'e' 1 75 'f' 2 60 'd' 2 50 'e' 2 40 'f' I want to ultimately produce HTML output that looks like this: 1275a=100,b=150,c=25d=120,e=80,f=75 2150a=80,c=70 d=60,e=50,f=40 The problem is that the obvious (to me!) way to do it involves multiple separate queries, so with the read committed isolation level it is possible that the data presented (for a given id) will not be consistent. I believe serializable isolation level would get around this, but at the expense of me having to retry "manually" any data-altering queries. Alternatively I could lock the tables with (I think) ShareLock, but this may not be the best solution from a performance point of view. Does anyone have any other suggestions? Thanks Ian ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL Challenge: Skip Weekends
here is the algorithm: date := now - day_of_the_week interval := interval + day_of_the_week date := date + int( interval/5)x7 + ( interval mod 5) Josh Berkus wrote: > > Folks, > > Hey, I need to write a date calculation function that calculates the date > after a number of *workdays* from a specific date. I pretty much have the > "skip holidays" part nailed down, but I don't have a really good way to skip > all weekends in the caluclation. Here's the ideas I've come up with: > > Idea #1: Use a reference table > 1. Using a script, generate a table of all weekends from 2000 to 2050. > 2. Increase the interval by the number of weekends that fall in the relevant > period. > > Idea #2: Some sort of calculation using 5/7 of the interval, adjusted > according to the day of the week of our starting date. My head hurts trying > to figure this one out. > > -- > -Josh Berkus > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] FW: RESTORE A TABLE
I have serveral tables that they have more then 2 millions, I want dump they out from one server and then restore them back on another server every day, the questions are: 1. What is the fastest way to dump/restore my data, I am try to use: pg_dump -aRt mytable -Fc -f mytable dbname pg_restore -aRt mytable -d dbname mytable but this takes too long to restore. 2. Is any way to disable unique index checking when loading, then enable the index after restored? Jie Liang ---(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
Re: [SQL] SQL Challenge: Skip Weekends
Jean-Luc, > date := now - day_of_the_week > interval := interval + day_of_the_week > date := date + int( interval/5)x7 + ( interval mod 5) Merci, merci, merci! -Josh ---(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
Re: [SQL] SQL Challenge: Skip Weekends
Joe, > How about this (a bit ugly, but I think it does what you want -- > minus the holidays, which you said you already have figured out): > > create or replace function > get_future_work_day(timestamp, int) Thank you. Once again, you come to the rescue when I'm stuck. I'll try your solution and Jean-Luc's, and see which works better/faster. And report back. -Josh Berkus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] skip weekends
Rudi, > Nice reply Josh. > I wouldn't call your solution 'ugly' at all. Actually I posed te question, and Joe Conway offered the solution. I'll be testing and reporting back. > It's an excellent example of a real world need for Postgresql > functions. > I've also been looking at other functions at > http://www.brasileiro.net/postgres/cookbook/. > I noticed your name amongst the author's -- nice one -- keep up the > good work. Yes. Sadly, Roberto seems to have lost interest in PostgreSQL, so the cookbook is frozen.For example, There's a couple of bugs in name_alike I'd like to fix, but I can't correct them and Roberto doesn't answer his e-mail. Anybody wanna take over the Cookbook? > My only problem is trying to decide on whether to use PL/pgSQL or > PLPerl. Use them both. PL/Perl is better at text parsing, loops and arrays. PL/pgSQL is faster for data operations. Use the best tool for the job! One thing I'd love to see is a generic address tokenizer, so that I can write an "address_alike" function. My Perl isn't up to it. Heck, a generic string tokenizer would be even more useful. Can a PL/Perl function return an array? -Josh ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] is it easy to change the create sequence algorithm?
Kevin, > I see in the docs that when I create a column that is of type SERIAL, > the engine automatically creates the sequence for me, named > TABLE_COLUMN_seq. That's great until the table name + column name > lengths are > 27 chars, then it starts chopping, and you guessed it, > I have multiple table/column combinations that don't differ until > after that length. This would require hacking the Postgres source code. Sure you don't wanna just create the sequences manually? If you're sure, post your question to psql-Hackers to find out where the SERIAL code is located. -Josh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Can somebody help me to optimize this huge query?
Hi, here is a query on two tables whith lots of self joins which just takes hours to complete on 7.2.1. I use multi dimensional indices which are shown in the explain comments. My question is how can I use explicit join syntax to let the planner do better. I think Geoq does not match yet because there are only 2 tables. The schema behind models abtract document objects. Here is the query: SELECT DISTINCT t_sek.docindex, t_sek.envelope, bt.oid, bt.time FROM boxinfo bt, boxinfo bd, boxinfo bo, docobj t_sek, docobj t_pgr, docobj t_sta, docobj t_sol, docobj d_pnr, docobj d_sta, docobj o_sek, docobj o_pgr, docobj o_pnr WHERE t_sek.docspec=124999684 and t_pgr.docspec=124999684 and t_sol.docspec=124999684 and t_sta.docspec=124999684 and d_pnr.docspec=15378692 and d_sta.docspec=15378692 and o_sek.docspec=125075754 and o_pgr.docspec=125075754 and o_pnr.docspec=125075754 and bt.community=15042052 and bd.community=15042052 and bo.community=15042052 and bt.member=111459733 and bd.member=111459733 and bo.member=111459733 and bt.hide=FALSE and bd.hide=FALSE and bo.hide=FALSE and o_sek.attrid=1 and o_pgr.attrid=4 and t_sek.attrid=0 and t_pgr.attrid=2 and t_sta.attrid=9 and t_sol.attrid=4 and d_pnr.attrid=6 and d_sta.attrid=16 and abstime(bd.time)::date > t_sol.val_date and t_sol.val_date <= now()::date and o_sek.val_str=t_sek.val_str and o_pgr.val_str=t_pgr.val_str and o_pnr.val_str=d_pnr.val_str and t_sta.val_str=d_sta.val_str and o_sek.envelope=o_pgr.envelope and o_sek.envelope=o_pnr.envelope and o_sek.docindex=o_pgr.docindex and o_sek.docindex=o_pnr.docindex and t_sek.envelope=t_pgr.envelope and t_sek.envelope=t_sta.envelope and t_sek.envelope=t_sol.envelope and t_sek.docindex=t_pgr.docindex and t_sek.docindex=t_sta.docindex and t_sek.docindex=t_sol.docindex and d_pnr.envelope=d_sta.envelope and d_pnr.docindex=d_sta.docindex and bt.envelope=t_sek.envelope and bd.envelope=d_pnr.envelope and bo.envelope=o_sek.envelope Here is what explain says: Unique (cost=3395.39..3395.40 rows=1 width=212) -> Sort (cost=3395.39..3395.39 rows=1 width=212) -> Nested Loop (cost=0.00..3395.38 rows=1 width=212) -> Nested Loop (cost=0.00..3389.37 rows=1 width=190) -> Nested Loop (cost=0.00..3383.35 rows=1 width=168) -> Nested Loop (cost=0.00..3369.99 rows=1 width=146) -> Nested Loop (cost=0.00..3363.98 rows=1 width=124) -> Nested Loop (cost=0.00..3149.05 rows=36 width=102) -> Nested Loop (cost=0.00..2727.76 rows=1 width=94) -> Nested Loop (cost=0.00..2719.21 rows=1 width=82) -> Nested Loop (cost=0.00..1813.58 rows=107 width=60) -> Nested Loop (cost=0.00..1392.83 rows=1 width=48) -> Nested Loop (cost=0.00..1325.31 rows=11 width=26) -> Index Scan using boxinfo_j_index on boxinfo bo (cost=0.00..419.68 rows=107 width=4) -> Index Scan using docobj_j_index on docobj o_sek (cost=0.00..8.44 rows=1 width=22) -> Index Scan using docobj_j_index on docobj o_pgr (cost=0.00..6.00 rows=1 width=22) -> Index Scan using boxinfo_j_index on boxinfo bt (cost=0.00..419.68 rows=107 width=12) -> Index Scan using docobj_j_index on docobj t_sta (cost=0.00..8.44 rows=1 width=22) -> Index Scan using docobj_j_index on docobj t_sol (cost=0.00..6.01 rows=1 width=12) -> Index Scan using boxinfo_j_index on boxinfo bd (cost=0.00..419.68 rows=107 width=8) -> Index Scan using docobj_j_index on docobj t_pgr (cost=0.00..6.00 rows=1 width=22)-> Index Scan using docobj_j_index on docobj o_pnr (cost=0.00..5.99 rows=1 width=22) -> Index Scan using docobj_env_index on docobj d_pnr (cost=0.00..13.34 rows=2 width=22) -> Index Scan using docobj_j_index on docobj t_sek (cost=0.00..6.00 rows=1 width=22) -> Index Scan using docobj_j_index on docobj d_sta (cost=0.00..6.00 rows=1 width=22) Maybe there are just too many joins :/ Dirk ---(end of broadcast)--- TIP 3: if posting/reading through
Re: [SQL] is it easy to change the create sequence algorithm?
Well, the quickest solution I can think of off hand is to not use
SERIAL. Instead, do it manually, like this:
DROP SEQUENCE my_seq;
CREATE SEQUENCE my_seq;
DROP TABLE my_table;
CREATE TABLE my_table (
my_table_id INTEGER DEFAULT nextval('my_seq') PRIMARY KEY,
...
);
Kevin Brannen wrote:
> I see in the docs that when I create a column that is of type SERIAL,
> the engine automatically creates the sequence for me, named
> TABLE_COLUMN_seq. That's great until the table name + column name
> lengths are > 27 chars, then it starts chopping, and you guessed it, I
> have multiple table/column combinations that don't differ until after
> that length.
>
> Is there a way to influence the "create sequence" generator with a
> directive, hint, set value, whatever, to be something else? (e.g.
> COLUMN_seq if I guarantee all the columns are unique)
>
> Yes I know that I could create the sequence myself, but the engine
> does such a good job. :-)
>
> Thanks,
> Kevin
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
