[SQL] seleting all dates between two dates
I am interested in a query where I can select all dates between two dates. I figure I can build a table of all valid dates with a resonable range and then select from that table, but I would like to use the power of sql to get the work done without building a date table. Any ideas? For example: I want all dates between 05-29-2001 and 06-02-2001 The result set would be: 05-30-2001 05-31-2001 06-01-2001 Thanks for the help. -Jeff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] create type of table
I have a problem where I need to restrict queries by more than a million ids that are the result of another program. Typically I would in the application layer write those ids into an IN( ) clause, but that will not work for more than 10,000 ids. So I need to load these ids into the db into some structure that I can call/join in the other queries. I have a I amd considering a temporary solution where a table is loaded with the nessesary ids for restricting the queries. I would really like something more efficent. In oracle I know I can create a variable of type table and fill it with these ids and then write my queries to use this table. How can I do this in postgresql, I did not see any support for a type table. Anyone know of any good tutorials or how-tos for doing this type of stuff in postgresql? Thanks - Jeff ---(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] Restriction by grouping problem.
The query I have now: SELECT min(datettime), sid FROM logs GROUP by sid; This returns the first instance of an sid in the logs table, there can be many rows in the table for each sid. The problem I have is that I do not need the SID I just need to group the min(datetime) by it. I need to return the column getfile. So the question I have is how would I create the retstriction of the min(datetime), sid and only return the value of getfile? The english of what I am looking for is. Select the getfile for the earliest of each sid. Any help would be greatly appreciated. --Jeff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Restriction by grouping problem.
Yeah I have been having a bit of a problem expressing myself in terms of this query, sorry about that. What I need is: SELECT getfile FROM logs (and a restriction that results in finding the rows with the lowest datetime for each unique sid) To define this table a bit more: Logs table has a primary key of logid (int4) and serveral columns, of which I am deling with sid (text), getfile (text), and datetime (int4). Now a select getfile, datetime, logid from logs where sid = onevalue; would return a set of rows for that sid, the row I want is for the one with the smallest aka min(datetime) and I want this for every row in the table. Thanks for the quick response Josh, hopefully above will help out some. --Jeff ""Josh Berkus"" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Jeff, > > > The query I have now: > > > > > > SELECT min(datettime), sid FROM logs GROUP by sid; > > > > > > This returns the first instance of an sid in the logs table, there > > > can be > > > many rows in the table for each sid. The problem I have is that I do > > > not > > > need the SID I just need to group the min(datetime) by it. > > The answer to this question is simple: > > SELECT min(datetime) as mintime FROM logs GROUP BY sid; > > However, I get the impression that your intended problem was more > complicated. Can you re-explain it? > > -Josh > > > __AGLIO DATABASE SOLUTIONS___ > >Josh Berkus > > 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 > > ---(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 ---(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] \set variant for use in regular sql commands
I need to use the \set command in regular sql (I am running postgres queries through php and it does not seem to support the \ commands in general). How can I set a variable with regular sql? I want to be able to do the following: \set testvar '1,2,3,4,5' \set testvar '6,7,8,':testvar Which is then used in: select * from table where id in (:testvar); Thanks, --Jeff ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] row level locking?
I have an update statement (no transaction controls surround these statements): update sessions set sessdate = 0 where sessid in ( long list of ids); How long will the rows being updated be locked for this statement? Will all be locked until all updates are completed or will the row locking only occur for each row being updated? If I have a statement like: update sessions set sessdate = 0 where datetime < 1; (this would be the same criteria that created the list used above) How long will each row be locked for? These queries can be updating a good number of rows ( > 10,000) every 10 minutes and I need to figure out how signifigant of an impact the locking occuring in those updates can be. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] calling a shell script from pl/pgsql
How can I call a shell script from within a pl/pgsql function that is called as from a trigger. I do not want to interact with the script I just want it to run. I do want the trigger to wait for the script it called to complete before returning a value. Any ideas would be greatly appreciated. Thanks, Jeff Barrett ---(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] calling a shell script from pl/pgsql
Thanks for the suggestions. I am running 7.1.2 and going to 7.1.3 soon. If I use pl/tclu or pl/perlu I can call this executable from within the code? I have a signifigant limitation, I cannot duplicate the action of the programs I want to call in a program I write within postgres, I need to call the executable (In this one case it is a shell script but I have others where it is a binary). I cannot find the pl/sh module. The google links that came up brought me to pages that no longer exist on postgresql.org. I will have to look around some more. Thanks for the advice... great help! Jeff Barrett "Jeff Barrett" <[EMAIL PROTECTED]> wrote in message 9nip2p$1s5o$[EMAIL PROTECTED]">news:9nip2p$1s5o$[EMAIL PROTECTED]... > How can I call a shell script from within a pl/pgsql function that is called > as from a trigger. I do not want to interact with the script I just want it > to run. I do want the trigger to wait for the script it called to complete > before returning a value. > > Any ideas would be greatly appreciated. > > Thanks, > > Jeff Barrett > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] select is fast, update based on same where clause is slow
I have a select statement that returns a set of 74,000+ results back in under a minute as follows: select s.sessid, s.membid, s.datetime from sessions2 s, (select min(datetime) as datetime, membid from sessions2 where membid is not null group by membid) as minsess where s.membid = minsess.membid and s.datetime = minsess.datetime; The final cost from explain for the above select is 22199.15 ... 24318.40 with rows = 5 and width = 28. Then I issue an update as follows (to update those 74,000+ rows): update sessions2 set sinceinception = 0 from sessions2 s, (select min(datetime) as datetime, membid from sessions2 group by membid) as mindate where s.membid = mindate.membid and s.datetime = mindate.datetime; The final cost from explain for the above update is 31112.11...98869.91 with rows = 2013209 and width=87. This update statement has been left running over night and does not complete. The ram usage on the machine is at about 3/4 capacity (800mb) during this time and CPU usage is near 100%. The machine has the -F option set and memory segments of 200mb and is running 7.1.2. What could be causing this update statement to not complete? Why are the costs so different since it seems to me that besides the cost of the update they are the same query? Any help would be great! Jeff Barrett ---(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] select is fast, update based on same where clause is slow
That did the trick. Thank you for the quick detailed answer. It runs in about a minute now. Jeff Barrett "Stephan Szabo" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > On Fri, 21 Sep 2001, Jeff Barrett wrote: > > > I have a select statement that returns a set of 74,000+ results back in > > under a minute as follows: > > > > select s.sessid, s.membid, s.datetime > > from sessions2 s, (select min(datetime) as datetime, membid > > from sessions2 > > where membid is not null > > group by membid) as minsess > > where s.membid = minsess.membid > > and s.datetime = minsess.datetime; > > > > The final cost from explain for the above select is 22199.15 ... 24318.40 > > with rows = 5 and width = 28. > > > > Then I issue an update as follows (to update those 74,000+ rows): > > update sessions2 set sinceinception = 0 > > from sessions2 s, (select min(datetime) as datetime, membid from sessions2 > > group by membid) as mindate > > where s.membid = mindate.membid > > and s.datetime = mindate.datetime; > > > > The final cost from explain for the above update is 31112.11...98869.91 with > > rows = 2013209 and width=87. > > > > This update statement has been left running over night and does not > > complete. The ram usage on the machine is at about 3/4 capacity (800mb) > > during this time and CPU usage is near 100%. The machine has the -F option > > set and memory segments of 200mb and is running 7.1.2. > > > > What could be causing this update statement to not complete? > > Why are the costs so different since it seems to me that besides the cost of > > the update they are the same query? > > I thought that the updated table is always in your from list (implicitly), > so you'd want: > update sessions2 set sinceinception = 0 > from (select min(datetime) as datetime, membid from sessions2 group by > membid) as mindate > where sessions2.membid=mindate.membid and > sessions2.datetime=mindate.datetime; > > I think your query would be joining the s/mindate results against > sessions2. > > > > ---(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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
