[SQL] query to return hourly snapshot
I orginally sent this email to the [novice] list but did not get any response.
I am look for help in developing a query that will return the nearest process
record that was
logged at or after each hour in a day (i.e. hourly snapshot).
Below are typical sample data. Also, I've included a query used to return the
average of the
process value for each hour to give an example for the format that I am trying
to achieve.
Thanks for the help.
Regards,
Richard
select * from process limit 10;
tstamp| process
-+-
2005-10-25 21:10:41 | 3.56513
2005-10-25 21:10:42 | 3.56503
2005-10-25 21:10:43 | 3.56494
2005-10-25 21:10:44 | 3.56484
2005-10-25 21:10:45 | 3.56475
2005-10-25 21:10:46 | 3.56465
2005-10-25 21:10:47 | 3.56455
2005-10-25 21:10:48 | 3.56446
2005-10-25 21:10:49 | 3.56436
2005-10-25 21:10:50 | 3.56427
(10 rows)
select avg(process),
date_trunc('hour',tstamp) as date
from process
wheredate_trunc('day', tstamp) = '2005-10-26'
group by date_trunc('hour', tstamp)
order by date_trunc('hour', tstamp);
avg|date
--+-
9.79195118032606 | 2005-10-26 05:00:00
10.0249767947376 | 2005-10-26 06:00:00
8.88596018049452 | 2005-10-26 07:00:00
7.95090951088542 | 2005-10-26 08:00:00
8.10741349776586 | 2005-10-26 09:00:00
7.30079822791947 | 2005-10-26 10:00:00
7.10586501293712 | 2005-10-26 11:00:00
8.15196838166979 | 2005-10-26 12:00:00
8.26183129151662 | 2005-10-26 13:00:00
8.95141531440947 | 2005-10-26 14:00:00
10.562882253329 | 2005-10-26 15:00:00
10.863490825 | 2005-10-26 16:00:00
11.4077104069976 | 2005-10-26 17:00:00
12.4702264580744 | 2005-10-26 18:00:00
11.9155618293134 | 2005-10-26 19:00:00
11.5622152555012 | 2005-10-26 20:00:00
11.6527367563489 | 2005-10-26 21:00:00
10.3170960432442 | 2005-10-26 22:00:00
9.56747980806563 | 2005-10-26 23:00:00
(19 rows)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] query to return hourly snapshot
Yes! Thanks you very much!
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 04, 2006 at 05:58:26PM -0700, Richard Broersma Jr wrote:
> > I am look for help in developing a query that will return the nearest
> > process record that was logged at or after each hour in a day (i.e.
> > hourly snapshot).
>
> Are you looking for something like this?
>
> SELECT p.process, date_trunc('hour', p.tstamp) AS hour
> FROM process AS p
> JOIN (
> SELECT date_trunc('hour', tstamp), min(tstamp)
> FROM process
> WHERE date_trunc('day', tstamp) = '2005-10-26'
> GROUP BY date_trunc('hour', tstamp)
> ) AS s ON s.min = p.tstamp
> ORDER BY hour;
>
> Or, using PostgreSQL's non-standard DISTINCT ON clause:
>
> SELECT DISTINCT ON (date_trunc('hour', tstamp))
>process, date_trunc('hour', tstamp) AS hour
> FROM process
> WHERE date_trunc('day', tstamp) = '2005-10-26'
> ORDER BY date_trunc('hour', tstamp), tstamp;
>
> --
> Michael Fuhr
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] how to solve this problem
Just a thought, Could you achieve that same result using the binary representation of an integer? Regards, Richard --- ivan marchesini <[EMAIL PROTECTED]> wrote: > Dear users, > I have this problem > > I have a table where there are 20 columns named > vinc1, vinc2, vinc3, vinc4, etc > > the values contained into each column are simply 1 or 0 (each column is > dichotomic) > 1 means presence > 0 means absence > > I would obtain a column (new_column) containg the name of the columns, > comma separated, where the value is = 1 > > for example: > > vinc1 vinc2 vinc3 vinc4 new_column > 1 0 1 0 vinc1,vinc3 > 0 0 0 1 vinc4 > 0 1 1 1 vinc2,vinc3,vinc4 > > can someone help me to find the best way to obtain this result??? > thank you very much > > Ivan > > > > > > > > -- > Ivan Marchesini > Department of Civil and Environmental Engineering > University of Perugia > Via G. Duranti 93/a > 06125 > Perugia (Italy) > e-mail: [EMAIL PROTECTED] > [EMAIL PROTECTED] > tel: +39(0)755853760 > fax: +39(0)755853756 > jabber: [EMAIL PROTECTED] > > > > > ---(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 > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How To Exclude True Values
> > how can i exclude true values for this query? > > > > http://www.rafb.net/paste/results/obtkGz26.html > > > > if i uncomment out > > > > --AND t_inspect_result.inspect_result_pass = 'f' > > > > it looks for prior falses within an inspect_id and > > returns it. i want the original result set minus > > the > > trues, if possible. > > > > tia... > > this SQL appears to do the trick... > > http://www.rafb.net/paste/results/zZKIjH80.html I have one idea that hopefully wont complicate you query but it could simplfy your query by getting rid of the query nexting. Also, I haven't tested it. Basically, Replace the DISTINCT ON (t_inspect.inspect_id) construct with GROUP BY t_inspect.inspect_id HAVING t_inspect_result.inspect_result_pass = 'f' Regards, Richard Broersma Jr. ---(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] Advanced Query
> Personally: I think your posts are getting annoying. This isn't SQLCentral. > Learn to write your own damn queries or even better - buy a book on SQL... Personally: (being a newbie with an interest in developing a strong rdms skillset) I've enjoyed following threads like these. Even when the questions (to some) seems overly simplistic, the courteous respondents often share insightful solutions or nuances that are not found in an "off the self" SQL book. However, if questions like these are *really* off-topic for the pgsql-sql I would be interested in knowing what kind of threads are acceptable and on-topic for this list. Also, if there are other mailing lists (pg or other) that are better suited for threads like this, I would appreciate learning of them. Regards, Richard Broersma ---(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] How To Exclude True Values
> inspect_id, inspect_result_id, inspect_result_pass, > inspect_result_timestamp > 3, 5, f, 2006-06-05 05:00:00 > 3, 6, t, 2006-06-05 06:00:00 > 4, 7, f, 2006-06-05 07:00:00 > *4, 8, f, 2006-06-05 08:00:00* > the query linked in this post will return 3 lines... > > 1, 2, f > 3, 5, f > *4, 8, f* -- the only one i really want >From your sample it seems to me that you are really only looking for the most >recient occuring record that have produced a false test regardless of which Inspect_id or inspect_result_id it came from. Is this correct? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How To Exclude True Values
> an inspection node (each row in t_inspect is an > inspection node) that has passed can't have a new > defect added - since it has already passed. > > therefore, in the defect entry form, i only want to > display those inspection nodes that don't have a true > value. by definition, a true value will always be the > last (by time) inspect_result. > > therefore, i need all the inspect nodes that don't yet > have a true value (iow, a true value in the last (by > time) inspect_result_pass row). > > an inspection node can have multiple inspection > results, hence, the t_inspection_results table. > > this might seem counter-intuitive at first, but it > makes sense since it may take 5 tries to eventually > pass a particular inspection node (f, f, f, f, t) for > fucntional test, for example. one node, five tests to > pass it. here is a test I did. bye the way, I did this is access as it is the only source available to me at the moment. table = test id_iir_id teststamp 1 1 No 6/5/2006 1:00:00 AM 1 2 No 6/5/2006 2:00:00 AM 1 3 Yes 6/5/2006 4:00:00 AM 2 4 Yes 6/5/2006 4:00:00 AM 3 5 No 6/5/2006 5:00:00 AM 3 6 Yes 6/5/2006 6:00:00 AM 4 7 No 6/5/2006 7:00:00 AM 4 8 No 6/5/2006 8:00:00 AM query -- SELECT a.id_i, a.ir_id, a.test, max(a.stamp) as mstamp -- PostgreSQL might not require an aggregate with the group by. FROM test a INNER JOIN ( SELECT Max(stamp) AS tmax, id_i FROM test group by id_i ) b ON a.stamp = b.tmax group by a.id_i, a.test, a.ir_id, a.stamp having a.test = false ORDER BY a.id_i,a.ir_id, a.test ; results -- id_iir_id testmstamp 4 8 No 6/5/2006 8:00:00 AM Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How To Exclude True Values
> Richard, that is the result i would need given that > data set. i have to digest this version, though. > > should this query be more efficient than the subquery > version as the table starts to get large? My experience is that Distinct On queries do not preform as well as their group by counter parts. I believe that others have also commented to the same effect. To speed the query you could apply indexes on the group by fields and the join columns. Also, I produced a second query using PostgreSQL: select a.id_i, a.ir_id, a.test, a.stamp from test a join ( select max(stamp) as mstamp, id_i from test group by id_i ) b on a.stamp = b.mstamp where a.test = false ; -- result id_i | ir_id | test |stamp --+---+--+- 4 | 8 | f| 2006-06-05 08:00:00 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How To Exclude True Values
> yields the following error: > > ERROR: schema "a" does not exist > > i tried to interpret you query and apply it to my > case, but, apparently, i didn't too good of a job. > > do you see the error? Hmmm... That is strange. but according to the PostgreSQL documentation on select you can add the option [AS] keyword between the table_name and alias. http://www.postgresql.org/docs/8.1/interactive/sql-select.html [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] select a.inspect_id, a.inspect_result_id, a.inspect_result_pass, a.inspect_result_timestamp from t_inspect_result AS a join ( select max(t_inspect_result.inspect_result_timestamp) as mstamp, t_inspect_result.inspect_id from t_inspect_result group by t_inspect_result.inspect_id ) AS b on a.inspect_result_timestamp = b.mstamp ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How To Exclude True Values
> Richard, > > i got the expected rewsults! now i just have to study > the query to figure out what it does. -lol- > > i tried using AS, but i only did it in one place - > doh! the error message just moved to the next place i > didn't do it. i'll know better next time. > > thanks for the help. I am surprised that the query did not work the first time without the optional AS keyword. I would be interested in knowing why your server requires the AS and mine doesn't. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How To Exclude True Values
> i think i've figured out the gist of the thought > process behind the SQL query... > > 1. select everything from t_inspect_result as table a > 2. select max timestamp value entries in > t_inspect_result as table b > 3. choose only choose those rows where the max > timestamps of table a and b are equal. > > is that about it? The one problem with query would be if there is a possibility that two differnt tests will have the exact same time stamp. If that is a possibility, then you could use the exact same query structure but replace Max(timestamp) with max(inspect_result_id) and then join on inspect_result_id instead. Regards, Richard Broersma ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Query to return modified results at runtime?
> IDColor > --- --- > 1 Blue > 2 Red > 3 Green > 4 Orange > > How would I rewrite the query to return results where the colors are > replaced by letters to give the following results? > > IDColor > --- --- > 1 A > 2 D > 3 B > 4 C http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html this is probably the easiest to implement but hard to mangage over time. Another solution would be to create color_code table that is referenced by your test table. Then when you can create a query as: select a.ID, b.code from test as a join color_code as b on a.color = b.color; There are additional solutions to this also. But these two are probably the easiest. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Finding multiple events of the same kind
> On Sunday 11. June 2006 15:27, Frank Bax wrote: > >SELECT participants.person_fk, count(participants.person_fk) FROM > > events, participants > > WHERE events.event_id = participants.event_fk > > AND events.tag_fk in (2,62,1035) > > GROUP BY participants.person_fk HAVING > > count(participants.person_fk) > 1 > > That worked like a charm! Thank you very much! > -- Also, you could create a unique column constraint that would prevent multiply instances of the same person in the participants table. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] COPY to table with array columns (Longish)
> On Mon, 2006-06-12 at 21:58 -0500, Aaron Bono wrote: > > I agree with Tom. Personally I cannot think of a time I would use an > > array column over a child table. Maybe someone can enlighten me on > > when an array column would be a good choice. > > Arrays are a good choice when the data comes naturally segmented. > Also, for a case and point, some of the postgresql system tables use arrays. I suppose that these would be examples were the core develops felt arrays were a good fit. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Repetitive code
> AFAIK PostgreSQL does not support materialized views but it's > interesting that you mention that because in essence the query is used > to materialize a view, i.e., it's part of an INSERT / SELECT into a > table which is then joined back to the other tables to construct a web > page as well as an RSS feed. This thread: http://archives.postgresql.org/pgsql-performance/2006-06/msg00324.php mentions the use of materialized views in postgresql. See: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html However, I am not sure if this link shows how postgresql supports materialized views or if it just shows how to simulate a materialized view with procedural code. Either way, I thought it might be of interest to you. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] any additional date_time functions?
I am working with the date_trunc() function with great success especially in the group by clause for aggregates. However, it is limited to returning "WHOLE" time units. i.e. years, months, days, hours, minutes, seconds. Are there any functions similar to date_trunc that can return variable increments i.e.: 5, 10, or 15 minutes increments, 3, 4, 6 hour increments, 1, 2 weekly increments, I imagine that the returned values would have to either be the "floor" or "ceiling" of the actual time stamps. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] any additional date_time functions?
> > Are there any functions similar to date_trunc that can return variable > > increments i.e.: > > > > 5, 10, or 15 minutes increments, > > 3, 4, 6 hour increments, > > 1, 2 weekly increments, > You might be able to extract the time since the epoch and divide it by the > appropiate number of seconds (the length of your interval) and truncate > the result. This might have unexpected results for you when you span > daylight savings time changes. > > Another option is to not use timestamp, but rather just store an integer that > represents some number of your intervals offset from an epoch. Bruno, Thanks for the suggestions. I am going to "toy" around with them to see what I can get to work. Thanks for the help. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to get a result in one row
> >I'd like to get the result in only one row: > >id | nick > >--+-- > >22192 | A,T > This question is in the archives (probably more than once). The answer > is...> > Read the online docs about aggregate functions. There is an example that > does (almost) exactly what you are asking. Where you referring to the tread regarding the LTREE contrib module for postgresql? http://archives.postgresql.org/pgsql-general/2006-06/msg00745.php I know I've seen this done using cursors in PL-PGSQL, but I would be interested if there was a solution with pre-existing aggregates. Regards, Richard Broersma Jr. ---(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: Fwd: [SQL] Start up question about triggers
> I did some research and can't even find a way to get meta data in a trigger. > > In a trigger, is there a way to inspect OLD and NEW to see what columns are > there and see what has changed? If so, you may not be able to grab the > actual query but you could create a generic trigger that reconstructs a > possible update/insert/delete for any table in your database. > > Does anyone know of a good place to go get information about using meta data > in a stored procedure or trigger? yes. See the section "User Comments" at the very bottom of Chapter 33 after "Writing Trigger Functions in C". It is odd that a PL_PGSQL example is given at the end a chapter for triggers written in C. http://www.postgresql.org/docs/8.1/interactive/trigger-example.html Also see the entire chapter 36.10 "Trigger Procedures" http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html Hope this is what you are looking for. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Fwd: [SQL] Start up question about triggers
> Im really interested in the part where you say "generic trigger" can you > give me some tips? As to how I will go about that? I had already read the > links that Richard gave, I new I could get the values like that. So right > now I will have to create a trigger for each of my tables to create the > necessary queries, or I could do it "generically" :-) Sorry, I guess I haven't kept up to speed with this thread. However, from chapter 36.10 http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html Notice the variables that you have to work with in a trigger function: TG_WHEN Data type text; a string of either BEFORE or AFTER depending on the trigger's definition. TG_RELNAME = Data type name; the name of the table that caused the trigger invocation. TG_OP = Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired. NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers. Also, notice chapter 9.19 http://www.postgresql.org/docs/8.1/interactive/functions-info.html current_user = user name of current execution context So with this information couldn't one (from a trigger function) insert a record in to a history table with the following columns?: Then for each column of the affect table if old.tbl_col1 != new.tbl_col1 then add a record to the history as follows. TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col1 TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col2 TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col3 TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_coln is this something like what you had in mind? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Fwd: [SQL] Start up question about triggers
> The problem is not tracking WHAT changed, this can be done, as we have > discussed in this thread, the problem is how to replicate the necessary > commands that will alter a mirror database to reflect what has been changed, > sequencially and in the order that it has occurred. If your finial goal is just to achieve db server replication, wouldn't slony achieve what you want? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Fwd: [SQL] Start up question about triggers
> > Actually man I do need to be able to write to both databases, and keep them > > synchronized, and all this because of the recurring xenofobia for technology > > Then sorry, but this can't be done out of the box by anything. You > have all manner of race conditions here. Doesn't PGcluster allow for multiple master databases that are kept synchronized? http://pgfoundry.org/projects/pgcluster/ I thought that Mammoth replicator might support synchronous masters but it appears to be an Asynchronous system like Slony. http://www.commandprompt.com/products/mammothreplicator Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] i need solution to this problem
> > I have tables like 1) emp_table (personal_no integer (foreign key), > > cdacno varchar (primary key),name varchar); > > > 2) Rank_table (rank_id varchar (primary key), rank_name varchar); > > > 3) Rank_date_table (rank_id (foreign key), rank_date date); > > > 4) Unit_table (unit_id varchar (primarykey), unit_name varchar); > > > 5) Personal_table (per_no varchar (primary key), pername varchar); > > > My query is â¦.if I give cdacno I have to get per_no from > > personal_table.. With this I have to display rank_name from > > rank_table ,name from emp_table, unit_name from unit_master.. > > it is not clear what the relationships are between the tables. for > example what is the foreign key to unit_table? > > how does the rank connect to emp_table or personal_table? yes. in addition to this, it seems that emp_table references personal_table on personal_no = per_no. But it is not clear how this is the case when personal_no is an integer and per_no is a varchar. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SELECT Aggregate
> SELECT trans_no,
> customer,
> date_placed,
> date_complete,
> date_printed,
> ord_type,
> ord_status,
select (
SUM(sell_price)
from soh_product
where sales_orders.trans_no = soh_product.soh_num
) as transact_sum,
> customer_reference,
> salesman,
> parent_order,
> child_order,
> order_number
> FROMsales_orders
> WHERE (trans_no Like '8%' AND order_number Like '8%')
> OR (trans_no Like '9%' AND order_number Like '8%')
> OR (trans_no Like '8%' AND order_number Like '9%')
> OR (trans_no Like '9%' AND order_number Like '9%')
> AND(warehouse='M')
> AND(date_placed > (current_date + ('12 months ago'::interval)))
> ORDER BY trans_no DESC
I am pretty new to SQL. But while reading a book written by an author
recommended on this list,I
can suggest a possible solution that I've seen. It might work for your
problem. Of course, I
haven't tested anything like this and don't know if PostgreSQL supports it.
Just be sure that trans_no is unique in the returned query.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] SELECT Aggregate
> > SELECT trans_no,
> > customer,
> > date_placed,
> > date_complete,
> > date_printed,
> > ord_type,
> > ord_status,
(select SUM(sell_price) -- this syntax working for me. see
below
fromsoh_product
where sales_orders.trans_no = soh_product.soh_num
) as transact_sum,
> > customer_reference,
> > salesman,
> > parent_order,
> > child_order,
> > order_number
> > FROMsales_orders
> > WHERE (trans_no Like '8%' AND order_number Like '8%')
> > OR (trans_no Like '9%' AND order_number Like '8%')
> > OR (trans_no Like '8%' AND order_number Like '9%')
> > OR (trans_no Like '9%' AND order_number Like '9%')
> > AND(warehouse='M')
> > AND(date_placed > (current_date + ('12 months ago'::interval)))
> > ORDER BY trans_no DESC
>
>
> I am pretty new to SQL. But while reading a book written by an author
> recommended on this
> list,I
> can suggest a possible solution that I've seen. It might work for your
> problem. Of course, I
> haven't tested anything like this and don't know if PostgreSQL supports it.
>
> Just be sure that trans_no is unique in the returned query.
select
f1.fiscalyear,
(select f2.startdate
from fiscalyeartable2 as f2
where f1.fiscalyear = f2.fiscalyear
) as start2date,
f1.enddate
from
fiscalyeartable1 as f1;
fiscalyear | start2date | enddate
++
1995 | 1994-10-01 | 1995-09-30
1996 | 1995-10-01 | 1996-08-30
1997 | 1996-10-01 | 1997-09-30
1998 | 1997-10-01 | 1998-09-30
QUERY PLAN
--
Seq Scan on fiscalyeartable1 f1 (cost=0.00..6.83 rows=1 width=6)
(actual time=0.044..0.067 rows=4 loops=1)
SubPlan
-> Index Scan using fiscalyeartable2_pkey on fiscalyeartable2 f2
(cost=0.00..5.82 rows=1 width=4)
(actual time=0.008..0.009 rows=1 loops=4)
Index Cond: ($0 = fiscalyear)
Total runtime: 0.138 ms
(5 rows)
it works, and check out the nifty query plan.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Using In Clause For a Large Text Matching Query
> We can assume a certain portion of the text is included in the DB table, > so I want to be able to do a substring match on "brown" and "green" and > in this case return both "brown kitty", and "green doggy". However the > problem is, if I run the query on each of my 300 rows to scan 200,000 > rows in my DB is entirely too slow. So I was hoping to use the IN clause > to create an IN group of about 300 items to scan the DB once. You can probably do it. However, you will have to pick a substring from your text field to compare against. In this case you seem to be choosing the first word, i.e. "brown" and "green". so maybe: select t1.col1 from table1 as t1, ( select distinct split_part( tblFileDump.Col1, ' ', 1 ) as samplecol from tblFileDump ) as fd1 where t1.col1 like '%' || fd1.samplecol || '%' ; This is just an idea. I've never used split_part or developed a sudo join this way. But it may work provided you and jump your text files into a temp table. Notice: http://www.postgresql.org/docs/8.1/interactive/functions-string.html for the syntax for split_part(). Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Using In Clause For a Large Text Matching Query
> Well, there is also: href="http://www.postgresql.org/docs/8.1/interactive/functions-comparisons.html#AEN13377";> > > /expression/ /operator/ ANY (/array expression/). So, if you have a way > to preprocess you > input text fields that you want matched > you could build a regex for each and feed them in an array to an '~ ANY' > expression like so (or, > use ~* for case > insensitive matching): > > SELECT col1 > FROM table > WHERE col1 ~ ANY (ARRAY['regex1', 'regex2', ...]); Good point, But don't forget to include the list in your response. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT Aggregate
> I've tried Aaron's suggestion of the GROUP BY and I don't know much about > it, but it ran for around 17 hours and still going (it had a dedicated Dual > Xeon 3.0GHz box under RHEL4 running it!) Maybe, this query that you are trying to run is a good candidate for a "Materialize View". http://archives.postgresql.org/pgsql-performance/2004-02/msg00279.php Also before you run your query you might want to see the explain plan is. Perhap it is using a sequencial scan in a place where an index can improve query preformance. ---(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] SELECT Aggregate
> Well whatdyaknow?? Being a Postgres newbie I hadn't even played with indexes > yet. They're awesome!! > Using Richard's suggestion of the Sub-Select in the COLUMN list, combined > with adding some indexes, I can now return this in under 5 seconds! Also, another way to improve preformance will be to analyze the affected tables. Analyze will ensure that the query planner has accurate statics by which it will use in picking fastest possible query. If you ever plan on updating or deleting records. You will also need to vacuum the table. And an additional measure of maintance would be to re-index the database. All of this is listing in the postgresql manual. If you really want to ensure the best possible speeds, it will be an important step to take. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Alternative to Select in table check constraint
I am practicing with SQL examples comming from the book:
JOE CELKO'S
SQL
PUZZLES
& ANSWERS
The following codes doesn't work on PostgreSQL 8.1.4 but according to the book
does conform to
SQL-92. Is there any other solutions that would result in the same effect? Or
is this an example
of a contraint that should be avoided at all costs?
CREATE TABLE BADGES
(
BADGENO SERIAL NOT NULL PRIMARY KEY,
EMPNO INTEGER NOT NULL REFERENCES SECEMPLOYEES (EMPNO),
ISSUEDATE DATE NOT NULL,
STATUS CHAR(1) NOT NULL CHECK ( STATUS IN ('A', 'I')),
CHECK ( 1 = ALL ( SELECT COUNT(STATUS)
FROM BADGES
WHERE STATUS = 'A'
GROUP BY EMPNO))
);
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] How To Exclude True Values
> Also, I produced a second query using PostgreSQL: > select a.id_i, a.ir_id, a.test, a.stamp > from test a > join > ( > select max(stamp) as mstamp, id_i > from test > group by id_i > ) b > on a.stamp = b.mstamp > where a.test = false > ; > -- result > id_i | ir_id | test |stamp > --+---+--+- > 4 | 8 | f| 2006-06-05 08:00:00 I found this query produced the same result. It is a list slower than the first with my small dataset. but maybe it will improve for larger datasets? select t1.id_i, t1.ir_id, t1.test, t1.stamp, t1.inttest from test as t1 where t1.stamp = ( select max(T2.stamp) from test as t2 where t2.id_i = t1.id_i) and t1.test = 'f'; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to Select in table check constraint
> > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > FROM BADGES > > WHERE STATUS = 'A' > > GROUP BY EMPNO)) > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > interactive/sql-createtable.html) > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > ON badges (empno) > WHERE status = 'A'; > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html Michael, Partial indexs seem to be "what the doctor ordered!" And your suggest is right on, the idea of the constraint is to allow only one active badge status at a time. But now that I think about it, using the authors suggestion (if it actually worked), how would would it be possible to change the active status from one badge to another? Oh well, partial index are obvious the superior solution since the entire table doesn't not have to be scanned to determine if the new badge can be set to active. Once again thanks for the insight. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Alternative to Select in table check constraint
> > The following codes doesn't work on PostgreSQL 8.1.4 but according to > > the book does conform to SQL-92. > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > FROM BADGES > > WHERE STATUS = 'A' > > GROUP BY EMPNO)) > > Yeah, the spec does allow sub-SELECTs in CHECK constraints but PG > doesn't implement that. The problem with it is that there's no clear > way to make it perform reasonably, because the CHECK doesn't simply > implicate the row you're currently inserting/updating --- every other > row is potentially referenced by the sub-SELECT, and so changing row > X might make the CHECK condition fail at row Y. A brute-force > implementation would be that every update of any sort to BADGES causes > us to re-evaluate the CHECK constraint (hence, re-scan BADGES entirely > to compute the sub-SELECT) for every row of BADGES (hence, O(N^2) work > if there are N rows in the table). That is certainly unworkable :-(. > A bright person can think of ways to optimize particular cases but > it's not easy to see how the machine might figure it out for arbitrary > SELECTs. > > The unique-index hack that Michael suggested amounts to hand-optimizing > the sub-SELECT constraint into something that's efficiently checkable. > > regards, tom lane Ah. Thanks for the clarification. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Left join?
> In the following table, codsol, codate and codfec are foreign keys > referencing table func and I need some help to codify a SELECT command that > produces the following result set but instead of codsol, codate and codfec I > need the respectives names (column nome from table func). > > postgres=# select * from reqtran; > codreq | codsol | codate | codfec > +++ > 1 ||| > 2 | 1 || > 3 | 1 | 1 | > 4 | 1 | 1 | 1 > postgres=# \d func > Table "public.func" > Column | Type | Modifiers > +-+--- > codfun | integer | not null > nome | text| > Indexes: > "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun) > "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun) > "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun) Would this do what you need? select R1.codreq, CS.nome, CD.nome, CF.nome from rectran as R1 left join func as CS on (R1.codsol=CS.codefun) left join func as CD on (R1.codate=CD.codefun) left join func as CF on (R1.codfec=CF.codefun) ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Alternative to Select in table check constraint
> On Fri, 2006-06-30 at 18:41 -0700, Richard Broersma Jr wrote: > > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > > FROM BADGES > > > > WHERE STATUS = 'A' > > > > GROUP BY EMPNO)) > > > > > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > > > interactive/sql-createtable.html) > > > > > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > > > ON badges (empno) > > > WHERE status = 'A'; > > > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > > > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html > > > > Michael, > > > > Partial indexs seem to be "what the doctor ordered!" And your suggest is > > right on, the idea > of > > the constraint is to allow only one active badge status at a time. > > > > But now that I think about it, using the authors suggestion (if it actually > > worked), how would > > would it be possible to change the active status from one badge to another? > > Unset the status first then set on the new one. Same transaction of > course. > > You may find this type of constraint is more workable with a TRIGGER > deferred until commit time than a unique constraint which cannot (at > this time in PostgreSQL) be deferred. Thanks for the Input Rod. I will try implementing a trigger as a way to constrain the input data to see how it works. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to Select in table check constraint
> This is more of an implementation option, but when I worry about what is > active/inactive I put start/end dates on the tables. Then you don't need > active indicators. You just look for the record where now() is >= start > date and now() <= end date or end date is null. You can even > activate/deactivate a badge on a future date. Of course, this complicates > the data integrity - you will need some kind of specialized trigger that > checks the data and makes sure there are no date overlaps to ensure you > don't have two badges active at the same time. But is also gives you a > history of badges and their activities. Good point. I take it that this type of solution stems from temporal schema design. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Can't drop table
> I have created a table called experimenters with the following query: > CREATE TABLE experimenters ( > experimenter_id INTEGER CONSTRAINT firstkey PRIMARY KEY, > first_name CHAR(20), > last_nameCHAR(25), > address CHAR(30), > phone_numCHAR(15) > ); > The query was successful. But when I try to drop or alter the table, it just > hangs without > echoing any error message. I have to cancel the query to get out. > I ran the following query to drop the table > DROP TABLE experimenters; > It also hangs when I try to alter the table. > Just don't understand the problem here. > Any help will be highly appreciated. You might want to post this on the PG_General Mailing list of you do not get many responses. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] hi i need to connect database from my system to another system
> I am using postgresql database server... in my system.. if I want to > connect to this database server from other system..Or how can I use this > database server in other machine .. > > How can I do this..if any one have idea about this please give me brief > description You need to start the postgresql server with the "-i" option to allow TCP/IP connections. Then you need to configure your pg_hba.conf file to set the permissions about which users can connect to which databases. Notice the pg_ctl section for starting postgresql. http://www.postgresql.org/docs/8.1/interactive/app-pg-ctl.html Notice the -o "options" section that can be set. (in this case you want -i) http://www.postgresql.org/docs/8.1/interactive/app-postmaster.html Finally notice, the section on the pg_hba.conf file. http://www.postgresql.org/docs/8.1/interactive/client-authentication.html#AUTH-PG-HBA-CONF Also, secure shell also works well when connecting from another system. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] hi i need to connect database from my system to another system
> If u don't mind can u please tell me clearly.. where I have start -I or > how to start postgresql with -I > > I am using postgresql database server... in my system.. if I want to > > connect to this database server from other system..Or how can I use > this > > database server in other machine .. > > > > How can I do this..if any one have idea about this please give me > brief > > description > > You need to start the postgresql server with the "-i" option to allow > TCP/IP connections. Then > you need to configure your pg_hba.conf file to set the permissions about > which users can connect > to which databases. > > Notice the pg_ctl section for starting postgresql. > http://www.postgresql.org/docs/8.1/interactive/app-pg-ctl.html > > Notice the -o "options" section that can be set. (in this case you want > -i) > http://www.postgresql.org/docs/8.1/interactive/app-postmaster.html > > Finally notice, the section on the pg_hba.conf file. > http://www.postgresql.org/docs/8.1/interactive/client-authentication.htm > l#AUTH-PG-HBA-CONF > > Also, secure shell also works well when connecting from another system. Don't forget to include the pgsql-sql list in your reply's. :-) The individuals on this list have varying degree of experience and knowledge of postgresql. So if you only respond to me, you would be limiting yourself to my meager experience and knowledge. ;-) To start off with, it would be helpful to know which OS version of PostgreSQL you are using. The windows versions is pre-configured to start with the "-i" option for allowing TCP/IP connections. Secondly, how are you trying to connect to your postgresql server from another system? What error messages are you getting. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] hi i need to connect database from my system to another
> I am using windows xp operating system... > I want to know how to connect remote postgresql from my system.. I need > brief description from beginning.. > Now I am using pg_HBF conf file to connect remote postgresql in that I > am giving host name , ip-address,mask and trust... but it is showing > error like > > Could not connect to server: connection refused(0x274d\10061) > Is the server running on host 127.0.0.1 and accepting TCP\IP connection > on port 5432 ? Actually, your PostgreSQL server is running on the IP address that is assigned to that computer. The 127.0.0.1 in the pg_hba.conf file is the initial accepted client IP address that your postgresql server will accepted connections from. 127.0.0.1 is basically means localhost. So, your pg_hba.conf file is initial configured to only allow client connections from itself. So in this case, if you want to connect from an IP address from a different computer, you will need to specify that ip address in your pg_hba.conf file as well as the permissions. But just to get started, you could try editing a line in your pg_hba.conf to this: #hostall all 127.0.0.1/32md5 hostall all/24 trust hostall all 127.0.0.1/32trust To understand all of this, you will need to read the introduction in your pg_hba.conf file. Also, keep in mind that the above change is basically an "open door" to everything in your DB server. So after you've had a change to get familiar with everything, you will want to tighten up on how,what, and how access is granted to you system. "Last but not least", you will need to restart your postgresql service so these changes will take effect. This should get you connected. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] i have a problem of privilages
> I am not an expert in postgres, may this work for you. > REVOKE INSERT,UPDATE,DELETE ON ALL from PUBLIC; > it will take the INSERT,UPDATE,DELETE permmission from all users for all > objects in the database. > If you want to give permission for a specific user on all objects > GRANT ALL on ALL to ; > Please make a backup before doing this , good for always > I am not sure is this is want you want. > > On 7/4/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote: > > > > I have 290 tables .. to these tables one schema is there .. that name is > > public .so I don't know how to create permission to these tables.. either I > > have to give permissions to individual table.. or I have to give permissions > > to schema or schema name if I give permission to schema it has to > > applicable to all tables.. > > I created one user with password then finally I don't know how to > > allocate permission to him.. please tell me very briefly step by step.. then > > I can understand > > Please tell me if any one knows about this .. One way to limit which users can access specific database is with the pg_hba.conf file. You really should spend some time to read it. http://www.postgresql.org/docs/8.1/interactive/client-authentication.html#AUTH-PG-HBA-CONF In PostgreSQL 8.1 the concept of "Roles" were added. A "Role" can be given specific privileges to the database. Once this step is complete, database uses can be assigned to this role. This will essentially inherit all role privileges to these users. There is also an important document that should be read in order to understand it. http://www.postgresql.org/docs/8.1/interactive/user-manag.html The SQL syntax for assigning privileges is found here: http://www.postgresql.org/docs/8.1/interactive/ddl-priv.html Also finding these topic is very easy also. Simple scan over the table of contents of the manual: http://www.postgresql.org/docs/8.1/interactive/index.html And of-course depending on what version of PostgreSQL you have: http://www.postgresql.org/docs/manuals/ Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] create aggregate function 'count_bool( column_name, boolean )'
> so now I can create the statement > select distict product_id, count_bool(purchased, true), > count_bool(was_selected, true) from some_table group by product_id; > > instead of breaking the query into 3 seperate queries > > select distict product_id from some_table; > select count(purchased) from product_id where purchased = true; > select count(was_selected) from some_table where was_selected = true; > Am I missing a detail with SQL based aggregate function development? > Any help would be appreciated. how about: select product_id, ( select count(purchased) from some_table as A2 where purchased=true and A1.product_id=A2.product_id ) as TP, ( select count(selected) from some_table as A3 where purchased=true and A1.product_id=A3.product_id ) as TS from some_table as A1 group by product_id; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SELECT substring with regex
> valid entries would be: > "28mm F2.8" (prime lens) > "30-70mm F4" (zoom lens) > "30-70mm F2.8" (zoom lens) > "30-100mm F4.5-5.6" (zoom lens with variable speed) > > > In the WHERE clause I have specified all those NAMEs, which follow that > pattern but have some gubbins appended: > > WHERE NAME ~ > '^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$' > > > which gives me a listing of those candidates that need to be amended - > manually or otherwise. > > Next, I wanted to produce a result which splits NAME into what it should > be (BASE) and attributes (SUFFIX). Maybe I can generate some SQL from > that to tidy up the data. would this give you the results you want? It admit that it doesn't look to elegant. name: substr(your_string, 0, strpos(your_string, ' ')+1) suffix: substr(your_string, length(your_string)-strpos(your_string, ' '), length(your_string)) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Celko's Puzzle Number 5
No matter how I try it, I can't getting the book's answer for this puzzle to work. Does anyone know of a solution that will work for this problem. The Problem is: "How do you ensure that a column will have a single alphabetic character string in it? (That means no spaces, no numbers, and no special characters.)" The book's solution is as follows: CREATE TABLE Foobar (alpha_only VARCHAR(6) CHECK ((UPPER(TRIM(alpha_only)) || 'A') BETWEEN 'AA' AND 'ZZ') ); However, this check constraint only prevents numerics beginning with 'A'. So the constraint works by preventing following strings that begin with these kinds of characters: !, 4, A!..., A4... But the constraint fails to prevent non-alphabetic characters when the string starts with a character > A. Thus B thru Z can be follow on not alphabetic characters. I compared the result from PostgreSQL with sqlite and access. They returned the same result. According to the text, this solution "could" have been generalized to work with more complicated strings as well. For example string "masks" could be used to enforce a kind of tagging convention like 'AA4', 'BB5'. Has anyone seen or done anything like this before? I am interested to hear what kind of solutions there are. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] table joining duplicate records
> --query results-- > survey :category :question :answer > survey1:category1:question1:answer1 > survey1:category1:question1:answer2 > > how can i elimate duplicates on my query results? > an also am i using the right 'table joining' or table design for my > survey app? What duplicates? You have two unique answers for question1. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Celko's Puzzle Number 5
> http://www.postgresql.org/docs/8.1/interactive/functions-matching.html > > Any of these CHECK expressions should work: > > CHECK (alpha_only SIMILAR TO '[A-Za-z]+') > CHECK (alpha_only ~ '^[A-Za-z]+$') > CHECK (alpha_only ~* '^[a-z]+$') > > Unfortunately, even though SIMILAR TO has been standard SQL for > several years, not all databases implement it. Many databases > do support regular expressions but generally via a non-standard > syntax (as PostgreSQL does with its ~, ~*, !*, and !~* operators). Thanks for the link! Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] table joining duplicate records
> can i have a result set. just like the outer join returns. > but on my case. return null on duplicates. > like this one. > > survey :category :question :answer > --- > survey1:category1:question1:answer1 >: : :answer2 hmmm.. I am no sure that you are going to get what you want from a simple select query. A reporting software could do this easily however. also be sure to include the list in your replies :-). Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] MS-SQL<->Postgres sync
> On 7/10/06, Kevin Bednar <[EMAIL PROTECTED]> wrote: > > > > Thanks Aron. What I'm actually trying to do is this: > > > > Postgress in physical store, being used by POS system as the back end. > > MS-SQL being used on web server by ecommerce system. > > > > Table structures are different of course, but some common fields. What I > > want to do is when an item is sold in the store, update the quantity field > > for that sku number on the web site and vice versa. Only 2 fields basically > > need to be updated on each side, the SKU number and quantity. This is to > > keep the product table in sync and try to avoid selling product that isnt in > > stock and setting a flag on the web system stating such. Thanks for your > > help. > > > > > For something this simple you are probably better off doing some custom > coding. > > If you have the ability to modify the databases, I would recommend putting a > trigger on each database so when there is a product sold, that sale is > recorded in a temp table (which serves as a queue of data that needs to be > synched). Then have a process read from these temp tables and feed the data > back to the other database. Of course, I am assuming you have full control > to change the databases - some vendors do not allow that. > > You may be able to connect the databases - MS SQL Server will definitely > allow you to connect via ODBC to another database and feed data back and > forth. I think there are add on modules for PostgreSQL but I have not tried > to have PostgreSQL talk to other databases before. I am not sure if this applys directly to the problem here, but this link my be useful also. http://archives.postgresql.org/pgsql-general/2006-07/msg00298.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to find entries missing in 2nd table?
> Hi, > I realize I probably lost my marbles but I've been having a god > awful time with a single query: > > control: > > > controller_id pk; > > > datapack: > > controller_id fk; > > > > > I need to get all entries from the table control that are not listed in > datapack. SELECT C.CONTROLLER_ID FROM CONTROL AS C LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID) WHERE D.CONTROLLER_ID IS NULL; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to find entries missing in 2nd table?
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > >> I need to get all entries from the table control that are not listed in > > >> datapack. > > > > > > SELECT C.CONTROLLER_ID > > > > > > FROM CONTROL AS C > > > LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID) > > > > > > WHERE D.CONTROLLER_ID IS NULL; > > > > > > > > > Or > > (SELECT controller_id FROM control) > > EXCEPT > > Good point! But don't forget to include the list. :-) > > Regards, > > Richard Broersma Jr. > > (SELECT controller_id FROM datapack) > > ? > > > > -- > > Milen A. Radev > > > > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to find entries missing in 2nd table?
> > SELECT controller_id FROM control > > WHERE controller_id NOT IN > > (SELECT DISTINCT controller_id FROM datapack); > The DISTINCT is not necessary. I have heard with Oracle that DISTINCT is a > huge performance problem. Is that true on PostgreSQL also? >From my experience, it does not preform as well as the standard group by >clause. I noticed a ~20% increase in query run times. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to find entries missing in 2nd table?
> > > > SELECT controller_id FROM control > > > > WHERE controller_id NOT IN > > > > (SELECT DISTINCT controller_id FROM datapack); > > > The DISTINCT is not necessary. I have heard with Oracle that DISTINCT > > is a > > > huge performance problem. Is that true on PostgreSQL also? > > > > From my experience, it does not preform as well as the standard group by > > clause. I noticed a ~20% > > increase in query run times. > > > > So in that case this would be better: > > SELECT controller_id FROM control > WHERE controller_id NOT IN > (SELECT controller_id FROM datapack); > > or > > SELECT controller_id FROM control > WHERE controller_id NOT IN > (SELECT controller_id FROM datapack GROUP BY controller_id); Well in this case, the group by or distinct is simple not needed for the query to preform correctly. The additional group by clause in the second query could cause it to preform additional processing which "may" cause it to preform slower. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Table Join (Maybe?)
> I have two tables: > 1. Sales figures by date and customer. > 2. Customer details - including their Geographic State > I need to extract a report from the first table (I can do that!), and in > that report order by their State (I can do that too!), but I also need a > summary of all the customers in each state, below the end of each state, and > have a grand total at the bottom. > Eg: > Customer 1 State 1 $100.00 > Customer 2 State 1 $100.00 > State 1 $200.00 > Customer 3 State 2 $100.00 > Customer 4 State 2 $100.00 > State 2 $200.00 > Grand Total $400.00 > Does anyone have any magic pointers for me? I've been playing with SELECT > INTO as 2 queries (the individual customers, then the summary figures added > to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS IN SECOND > QUERY instead of them all sorted together nicely :-( I do not believe that a single "query" will give you what you want here. YOU could have 3 seperate queries: 1 for customer detail by state. 1 for state total 1 for grand total However, if you use a reporting tool like crystal or others, they have the ability to generate summaries exactly as you are referring to here. you would only need to pass it the query on total per customer. The reporting utility has a groupby feature where it would group the customers by state for your. In the group by summary it would automaticly display state total. Then if the report footer you could add a grand total summary for all of the records you passed to the report. The following link show some of the reporting programs that you can use. http://www.postgresql.org/community/survey.43 Regards, Richard Broersma Jr. ---(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] User Permission
> > Dear group, > >i created a user named 'dataviewer' and grant only select permission to > > that user, > >but now the user could able to create tables. how to restrict this, > >i want to give permission to create views and do selects on tables and > > views. > >how to do it? > >plz help. > Have your checked > http://www.postgresql.org/docs/8.1/interactive/sql-grant.html > http://www.postgresql.org/docs/8.1/interactive/sql-revoke.html > I would start by creating a role: > http://www.postgresql.org/docs/8.1/interactive/user-manag.html > And revoke all on it. Then add only the permissions it needs and assign the > role to the user. Also, one additional point would be to revoke all from public as mentioned in the following thread: http://archives.postgresql.org/pgsql-general/2006-07/msg00148.php Apparently, whatever privileges 'pubic' has are extended to the privileges of the individual users. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error when trying to use a FOR loop
> Sorry if this is in the archives, but I've done a search and couldn't find > anything relevant. > I'm running HP's precompiled version of 8.1.3.1 as part of their Internet > Express offering, and > I can't seem to run a for loop. Here's what I'm seeing: > xp512-0715-0716=# FOR LV in 1..10 LOOP > xp512-0715-0716-# select * from ldevrg; > ERROR: syntax error at or near "FOR" at character 1 > LINE 1: FOR LV in 1..10 LOOP The following is give examples of control structures. However, you need to be sure that your version of postgresql has a procedural language installed. If not, you will need to install it. http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS Use can use createlang to add different languages to postgresql. http://www.postgresql.org/docs/8.1/interactive/sql-createlanguage.html The following link gives a shot list of languages that you can use. There are others however: http://www.postgresql.org/docs/8.1/interactive/server-programming.html see Server-side Procedural Languages from: http://www.postgresql.org/download/ Hope this help. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help with privilages please
REVOKE ALL on TABLE suppliers FROM hilary; now login as hilary SELECT * from suppliers; and I get all the records!!! If I create a **new** table though and then do the above, the permissionswork I get a polite message telling me "no go". Thissounds to me like a problem with earlier compatibility. Is there away I can overcome this. A simple dump/restore does not solve theproblem. You might also have to revoke all from public: Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] simple problem???
> A|B|C > 1|2|20 > 1|3|10 > 1|4|21 > 2|3|12 > 2|4|22 > 3|4|23 > 1|3|10 > 2|3|12 > 3|4|23 > -select all records where A=1, > -find, into this selection, the record where there's the minimum value > of the field C > -print all the fields for this record. maybe this will work; select a,b,c from table as T1 join (select a, min(c) as minc from table group by a) as T2 on (t1.a = t2.a) and (t1.c = t2.c) ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to use table from one database to another
> I have 2 databases namee PAO and CAS. > PAO contains 3 schemas named Public,pao,sts CAS contains 4 schemas > named Public,cao,sts,reports > Now i am in PAO database..now i want access table 'activity' in > schema 'cas' in CAS database. > How it is posible. > 2nd thing is... > i have 2 servers access i.e local and mainserver. > How access table from one server to another server? > please tel me...because we need this one > Please give me full details with examples... because I am new to > Postgres but I know oracle very well For this functionality to work you will need the db-link add on for postgresql. http://pgfoundry.org/projects/snapshot/ Also for other useful addons check out the project tree. http://pgfoundry.org/softwaremap/trove_list.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Query response time
> I am using PostgresSQL 7.4 and having some serious performance issues. > Trawling through the archives and previous posts the only visible advice > I could see was either by running vacuum or setting the fsynch flag to > false. > > I am using tables that only contain approx 2GB of data. However > performing a number of simple conditional select statements takes a > great deal of time. Putting limits on the data obviously reduces the > time, but there is still a delay. (Note: on one particular query I set > the limit to 538 and the query returns in under 2mins if the limit > becomes 539 the query loops indefinitely!) > From previous experience I know these delays are longer than both > Informix and MySql. In some instances it takes so long I end up having > to kill the query. > > The install was performed by yum onto a RAID server using Centos. I am > sure there is something fundamentally wrong for I can't believe that > postgres would have the reputation it has based on the statistics I'm > getting. Does anyone have any advice? > > The data I am using was imported from an Informix system as part of a > migration strategy. > I know this is long shot but I hope someone can shed some light. Are the Update/Insert queries slow or is it the select queries that are taking awhile? For select queries, an explain analyze of the offending query would be helpful. Also, in addition to vacuuming you may want to reindex you db in order to clean all of the dead tuples from your indexs. For heavy insert/update queries check your postgres logs to see if any messages suggest increasing your check-point-segments. If this is the case, try increasing you check_point_segments and try moving your pg_xlog to a different spindle. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Best way to do this query..
> I have the following: > > name effective tstamp rate > John 01-01-2006 2005-12-07 13:39:07.614945115.00 > John 01-16-2006 2006-01-07 13:39:07.614945125.00 > John 01-16-2006 2006-01-09 15:13:04.4169351885.00 > > I want the output to be: > name effective end_daterate > John 01-01-2006 01-15-2006115.00 > John 01-16-2006 1885.00 > > What is the best way to do this? This is on a huge table and what I > have right now is quite slow. Any ideas? I would assume that your data does not change after it is logged. If this is the case, maybe this part of your data model would actually be a good canidate for an OLAP data model. If this is not the direction your are enterested in moving, you could also reduce your query processing time by limiting the data ranges for your existing query to something for reasonable. Regards, Richard Broersma Jr. ---(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] Best way to do this query..
> Yes the data does not change once it is logged. > I am quite new to this whole thing, do you mind elaborating more > about the OLAP data model you mentioned about? Well just to give a generalization using an OLAP as your data model, the data is stored in a denormalized yet defined model. Data integrety is maintained because by definition all records inserted will remain static. Here are some additional links that can be used for getting started: http://en.wikipedia.org/wiki/OLAP http://www.amazon.com/gp/product/0123695120/sr=8-4/qid=1156546075/ref=pd_bbs_4/002-4041472-4877644?ie=UTF8 Another alternative is to create a materialized view that will update itself only when records are inserted or updated. http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Create Assertion -- Question from a newbie
Sorry if my question is a little off topic. I am reading my new "SQL for Smarties" book side by side with the PostgreSQL 8.1 manual. I noticed that this particular feature is not included in PostgreSQL. Some of the achieve threads mostly discuss that this feature is currently not supported. My understanding is that Assertions place constraints upon data spanning multiple related tables. Is the Assertion feature slated to be added in the future? (Perhaps rolled up in a more generalized "TO-DO" item?) Would this feature add functionality that can not be achieved by other means? (i.e. alternative schema definitions or triggers?) Or does it merely provide a redundant means to constrain data, and thereby not warrant addition into the features of PostgreSQL? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Create Assertion -- Question from a newbie
> You can accomplish what assertions do using triggers. > I think the issue is generating triggers for general assertions that don't > totally suck performancewise. Ah, I see. So the points is that checking the integrity between two complete data sets can become a preformace killer. Thanks for the feed back. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Fastest way to get max tstamp
> name | program | effective | tstamp | rate > --+-+++-- > jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | AAA | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20 > jdoe | BBB | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20 > jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > > I want to get: > name | program | effective | tstamp | rate > --+-+++-- > jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > > Basically, for effective='08-16-2006', it only gets the latest inserted > record (using tstamp) for that effective date, which is 2006-08-25 11:57: > 17.394854. > > So what is the quickest way to do this? > I can always do: > Select * from Table t where tstamp=(select max(tstamp) from Table t2 where > t2.name=t.name and t2.effective=t.effective) > but it takes so long since this is a huge table. > > Any suggestions? SELECT name, program, effective, tstamp, rate FROM TABLE AS T1 JOIN ( SELECT max(tstamp) as maxtstamp FROM Table WHERE tstamp between current_timestamp - interval '7 days' and current_timestamp GROUP BY name, program, effective ) AS T2 ON (T1.tstamp = T2.maxtstamp) ; A smaller date range on a large table will really speed up your query also. If you really need to see the results of the same table over and over again, a materialized view(i.e. push the query results into a table and then add incremental updates over time) would probably work better for you. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] where clause subqueries vs multiple rows results
> I've a query which I'd have liked to word akin to: > > SELECT guid FROM child WHERE the_fkey = > ( SELECT id FROM parent WHERE name ~ 'some_regex' ) > ORDER BY the_fkey, my_pkey; > > I got around it by doing the SELECT id first, and then doing a SELECT > guid for each row returned, appending the results together. > > Can that be done in a single query, insead of 1+n queries? select guid from child C join parent P on (C.the_fkey = P.di) Where P.name ~ 'some_regex' order by C.the_fkey, P.my_pkey; Perhaps this might work. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to autoincrement a primary key...
> I am sure this is simple, but I don't get it. I am new to PGSQL, coming from
> MySQL - in mysql, you can autoincrement the primary key; in postgre, I am
> not sure how to do this. I have read the documentation, and tried "nextval"
> as the default - I have searched for the datatype SERIAL, but I am using
> navicat and this datatype is not supported. Can someone tell me how to do
> this - I just want the integer value for a primary key to autoincrement by
> one.
CREATE TABLE bar (idSERIAL PRIMARY KEY);
Is just shorthand notation for:
CREATE SEQUENCE foo START 1;
CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('bar'));
Also see:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] conversion of numeric field from MSSQL to postgres
> It seems that you've already asked for this question last Sunday, and > because your question is somewhat deterministic, the answers are more likely > to be the same. Check your previous e-mails. actually I am get duplicate emails like this one across the various PG lists. Perhaps someone else is resending these email? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] conversion of numeric field from MSSQL to postgres
> This could be fixed if someone wanted to improve the moderation software > to auto-approve pending messages from someone who's just subscribed, but > perhaps that's a lot of work. I haven't looked at that code, so I'm not > volunteering ... Thats not a problem. I was surprised to see your name in a man pages for other command line utilities un-related to postgresql. So I would expect that you have enough to do in the mean time besides altering the moderation software. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [HACKERS] Bug?
> Then how do we clear the values of a serial column(is it done only by > dropping the column?)? If you look at the following link, you will see that serial is not really a true data-type. It is merely a short-cut to get the desired results: http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL Also, if you are interested in resetting your columns "serial" value back to 1 or 0 or -1, you can do it using the setval() function for the following link: http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] delete on cascade
> Hi all, > I guess this is an already asked question, but I didn't found an answer, so > apologize me. Imagine I've got two tables: > skill(id,description) // primary key => id > family(id,description)// primary key => id > and I want to associate skills to families: > ass_sf(id_skill,id_family)// appropriate foreign keys > > Tables are already created and the database is running. Now I'd like to > implement a delete cascade, thus when I delete a skill also its association > with the family must be deleted. I gues I should have declared the skill > table as follows: > CREATE TABLE skill > ( > id varchar(20) on delete cascade, > description varchar(50), > primary key(id) > ); > > right? The problem is: how can I alter the table to add the column constraint > now, without redeclaring the table? > Thanks very much for helping me. This link has one line that is very similar to what you want to do. You will probably have to start a transaction, drop the foriegn key contraint and then add a new foriegn key contraint with the on drop cascade . http://archives.postgresql.org/pgsql-general/2006-10/msg00467.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] delete on cascade
i guess my first attempt to send a reply failed. --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > Hi all, > > I guess this is an already asked question, but I didn't found an answer, so > > apologize me. Imagine I've got two tables: > > skill(id,description) // primary key => id > > family(id,description) // primary key => id > > and I want to associate skills to families: > > ass_sf(id_skill,id_family) // appropriate foreign keys > > > > Tables are already created and the database is running. Now I'd like to > > implement a delete cascade, thus when I delete a skill also its association > > with the family must be deleted. I gues I should have declared the skill > > table as follows: > > CREATE TABLE skill > > ( > > id varchar(20) on delete cascade, > > description varchar(50), > > primary key(id) > > ); > > > > right? The problem is: how can I alter the table to add the column > > constraint > > now, without redeclaring the table? > > Thanks very much for helping me. > > This link has one line that is very similar to what you want to do. You will > probably have to > start a transaction, drop the foriegn key contraint and then add a new > foriegn key contraint > with > the on drop cascade . > > http://archives.postgresql.org/pgsql-general/2006-10/msg00467.php > > Regards, > > Richard Broersma Jr. > > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Add calculated fields from one table to other table
> I have two tables. Tick table has fields like ticker, time, price & volume
> and Timeseries
> table has fields like ticker, time, avg_price, avg_volume.
>
> The time field in Timeseries table is different from time in tick table,
> its the timeseries
> for every minute. Now I want to calculate the average price & volume from
> tick table for each
> ticker and for every minute and add those fields to timeseries table. Can
> anyone please help me
> out with the sql query.
>
> Note: The ticker in the tick table also has duplicate values, so i am not
> able to create
> relation between two tables.
Here is my guess how it can be done:
insert into Timeseries ( tiker, time, avg_price, avg_volume ) select ...
where select would be
selecttick,
date_trunc('minute', time) as minute,
avg(price) as avg_price,
avg(volume) as avg_volume
from ticker
where time between 'yourstartdate' and 'yourenddate'
group by tick, minute;
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to query information schema from shell script
> In my original email i forgot to mentioned i need to know if the > database exists or not from the shell script. If it doesn't exist i > would then create it. Currently i was just creating the db everytime > our db script is run (since it doesn't hurt the db) but this generates > the 'db already exists' log and I now have the requirement that the > users not see that log. ie, don't run createdb if it already exists. > > In looking through the postgres docs i can see how to check if a table > exists but not how a db exists. > > Again, thanks for the help. psql -l Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Case Preservation disregarding case sensitivity?
> Case was preserved. Now lets add the foreign key just as we did before (note
> that the case in the table definition and the ALTER TABLE query is the same):
>
> ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
> (userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
> ^^
> ERROR: column "userprofiletypeid" referenced in foreign key constraint does
> not exist
When ever you defince a column with quotes, all references to it must also
contain quotes. Try:
ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
("userProfileTypeId") REFERENCES user_profile_type ("userProfileTypeId");
> OK, another query (perfectly valid SQL):
>
> insert into user_profile_type
> (userProfileTypeId,userProfileType) VALUES(1,'ABNORMAL');
> ^ ^^^
> ERROR: column "userprofiletypeid" of relation "user_profile_type" does not
> exist
Try:
insert into user_profile_type
("userProfileTypeId","userProfileType") VALUES(1,'ABNORMAL');
>
> I am hoping that there is an easy way to obtain case-preservation with
> case-insensitivity, or at the very least, case-preservation and complete
> case-sensitivity, or case-preservation and a consistant case-conversion
> strategy.
>
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give
> me
> case-preservation with EITHER case-sensitivity OR case-insensitivity, but not
> both as I am seeing.
Perhaps in your queries or views you use the AS keywork to respecify the column
name with
upper/lower cases.
i.e.
mydb=# select id as "Id" from foo;
Id
-
goodbye
(1 row)
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Case Preservation disregarding case sensitivity?
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give > me > case-preservation with EITHER case-sensitivity OR case-insensitivity, but not > both as I am seeing. > > Thanks in advance. I am hoping to find a solution to this so I can actually > convert one of our databases to use Postgres. And I can say that little > issues like this are precisely why Postgres was never used in this > organization before, even though several of the other database developers > like the features, stability and performance of Postgres. Here is an explination from the postgresql manual: http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS ... Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.) ... Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Add calculated fields from one table to other table
> Thanks a lot for your help. The query does work, but now I have a problem. > The query goes like > this: > > select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price), > sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) > from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and > tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by > tm.timeseries_time, > tk.ric order by tk.ric, tm.timeseries_time > > The problem is, if there is no row for certain minute, then I want the > count to be displayed > as zero and other coulmns like avg to be null. In this query, it just omits > those minutes which > doesnt have any row for a particular minute. You have to use an outer join. You will need a table or sequence that has every minute in a range that you are interested in and outer join that to your actual table. This will give you a count of zero. i.e. select S.minute, count(W.minute) as minutecnt from Series_of_Minutes S left join Working_table W on S.minute = W.minute ; hope this helps. REgards, Richard Broersma jr. ps. sorry that my query suggestion didn't work :0) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Add calculated fields from one table to other table
> select foo.ric, tm.times_time, count(tk.*), avg(tk.price), > sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct > ric from ticks) as > foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and > tk.tick_time < > (tm.times_time + '1 minute' :: interval)::time and tk.ric = foo.ric group by > tm.times_time, > foo.ric order by tm.times_time; > > I get a error message like this: > > ERROR: invalid reference to FROM-clause entry for table "foo" > HINT: There is an entry for table "foo", but it cannot be referenced from > this part of the > query. > > Can you help me with this? I will try, but to start with, to help us, when you have a difficult query to solve, you should simplify your query as much a possible. This way we can more quickly see what you are intending verses the problem you are having. 1 tip: (select distinct ric from ticks) I think that you will find that: (select ric from ticks group by ric) is much faster than using the distinct. The error in the query that I see is that you are using foo as a criteria in the ON syntax. This will not work. To illistrate: A,B join C ON (B.id = C.id) --ON syntax only works with joins AND (B.id2 < C.id) --The And is still part of the ON syntax --you can not reference A since it is not joined Where A.id = B.id --you can only specify a non-joined tables contrainst AND A.id2 < C.id2 ; --in the where clause I hope this helps. Regards, Richard Broersma JR. ---(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] Add calculated fields from one table to other table
> Thanks for your help. That does make sense, but I am not able to get the
> result what I wanted
> exactly. Let me explain you.
>
> I have ticks table in which I have columns like ric, tick_time, price &
> volume. The times
> table has just one column with times_time which has time data for each minute
> ie.)
>
> Ticks
> ric | tick_time | price | volume
> A | 12:00:01 | 23.00 | 12
> A | 12:00:02 | 26.00 | 7
> B | 12: 00:02 | 8.00 | 2
> B | 12:01:01 | 45.00 | 6
>
> Times
> times_time
> 12:00
> 12:01
> 12:02
>
> Now I want the timeseries for each minute for all ric in the tick table. So
> my query goes like
> this for a particular ric say for example ric 'A'
>
> select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum
> (tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select ric from
> ticks where ric = 'A'
> group by ric) as foo, times tm left join ticks tk on tk.tick_time >=
> tm.times_time and
> tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric =
> 'A' group by
> tm.times_time, foo.ric order by tm.times_time;
>
> I get the result as I expect, but i am not able to derive a query for all
> rics in the tick
> table.
>
How about:
SELECT
foo.ric,
date_trunc('minute', tm.times_time) as minute,
count(tk.*),
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Add calculated fields from one table to other table
> Hi Richard,
>
> Thanks a lot. I still am not able to get the result for all the rics in the
> ticks table but I
> am able to get the result for a particular ric.
>
> Can you help me with getting the result for all the rics in the ticks table
>
> Thanks
> Roopa
Could you send create table statements for the tables you are working on, and a
few insert
statements for each table to have sample data. then show what you want the
query results to look
like.
But from what you stated in your previous emails here is what I gather: maybe
it might work?
selecttk.ric as ric,
tm.times_time as minute, --timestamps by minutes
count(tk.*) as ,
...
from times tm
left join ticks tk
on (tm.times_time = date_trunc('minutes', tk.time))
group by ric, minute
order by minute;
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Add calculated fields from one table to other table
> I am sending you the create statement of tables & few insert statements as
> well. Hope this
> helps to solve the problem.
where are the insert statements? ;)
>
> CREATE TABLE ticks
> (
> tick_id int8 NOT NULL DEFAULT nextval(('ticks_s'::text)::regclass),
> ric varchar(30) NOT NULL,
> tick_date date NOT NULL,
> tick_time time NOT NULL,
> price float8,
> volume int4,
> CONSTRAINT ticks_pkey PRIMARY KEY (tick_id),
> )
> WITHOUT OIDS;
>
> CREATE TABLE times
> (
> times_time time NOT NULL,
> count int4,
> CONSTRAINT times_pkey PRIMARY KEY (times_time)
> )
>
> selct statement of ticks table
>ric | tick_date | tick_time | price
> -++--+---
> A | 2006-04-04 | 00:00:55.023 | 4.05
> AA | 2006-04-04 | 00:00:55.023 | 9.05
> A | 2006-04-04 | 00:00:59.023 | 6.05
> A | 2006-04-04 | 00:01:00.023 | 5.05
> ABC | 2006-04-04 | 00:01:00.509 |12.00
> ABI | 2006-04-04 | 00:01:03.511 |13.00
> AA | 2006-04-04 | 00:01:08.023 | 6.05
> ABT | 2006-04-04 | 00:01:08.518 | 3.06
> ABT | 2006-04-04 | 00:01:09.518 | 7.06
>
> select statement of times table
> times_time
> ---
> 00:00:00
> 00:01:00
> 00:02:00
>
>
> I want the query result to look
> ric | times_time | count | avg_price
> ++---+---
> A | 00:00:00 | 2 | 5.05
> AA | 00:00:00 | 1 | 9.05
> ABC | 00:00:00 | 0 |
> ABI | 00:00:00 | 0 |
> ABT | 00:00:00 | 0 |
> A | 00:01:00 | 1 | 5.05
> AA | 00:01:00 | 1 | 6.05
> ABC | 00:01:00 | 1 |12.00
> ABI | 00:01:00 | 1 |13.00
> ABT | 00:01:00 | 2 | 5.06
Here is what I got:
ric | minute | count |avg_price
-+--+---+--
ABC | 00:00:00 | 0 |0
ABT | 00:00:00 | 0 |0
AA | 00:00:00 | 2 | 9.05
ABI | 00:00:00 | 0 |0
A | 00:00:00 | 6 | 5.05
A | 00:01:00 | 3 | 5.05
ABI | 00:01:00 | 1 | 13
AA | 00:01:00 | 2 | 6.05
ABT | 00:01:00 | 9 | 5.726667
ABC | 00:01:00 | 1 | 12
A | 00:02:00 | 0 |0
AA | 00:02:00 | 0 |0
ABI | 00:02:00 | 0 |0
ABC | 00:02:00 | 0 |0
ABT | 00:02:00 | 0 |0
(15 rows)
And here is how I got it:
SELECT
A.ric,
A.minute,
count(B.*) as count,
COALESCE(avg(B.price),0) as avg_price
FROM
(
SELECT T.ric,
M.times_time as minute
FROM
ticks T
CROSS JOIN
times M
WHERE
M.times_time
BETWEEN
'00:00:00'
AND
'00:03:00'
) A
LEFT JOIN
ticks B
ON
A.ric = B.ric
AND
A.minute = date_trunc('minute', B.tick_time)
GROUP BY
A.ric,
A.minute
ORDER BY
A.minute
;
Hope this is what you were looking for. This is the first time I've ever had
to employ a cross
join get what I wanted. Just realize that this query will explode with a very
large number to
records returned as the times table grows. You should expect a quantity of
results like (total
ticks * total times)
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Duplicate Fields + Views Questions
> I've got 2 views - each with identical column names. I want to create an > aggregate view of both tables as a new view. But when I try to do this I > understandably get errors on duplicate fields. > > As an example: > > CREATE VIEW gnis_view AS SELECT * FROM gnis_international_view, > gnis_usa_view; > ERROR: column "the_geom" duplicated > ERROR: column "the_geom" duplicated > > Any ideas on how I can achieve what I am seeking Yes but you will have to specify each column name from each table and create aliases for the duplicated columns. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] a celko-puzzle from long time ago
> While reading celko's SQL puzzles (second edition) i followed a reference to > http://www.dbmsmag.com/9801d06.html. > There is a puzzle that counts items in boxes. > When i try to run the proposed solution on PG 8.1.5, PG says: ERROR: column > reference "qty" is > ambiguous > > > apparently the variable declaration used in the solution is not proper for > PostgreSQL. Is there > a way to solve this puzzle in PostgreSQL? I believe that qty is a parameter variable that must first be set in psql. I've seen some thread in various lists that describe how to set parameter variables before executing a query that uses them. Regards, Richard Broersma Jr. ---(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] Groups and Roles and Users
> Could someone suggest me how to get a documentation about Groups and Roles > and Users ? > > It would be nice to see a simple and easy documentation (a tutorial could be > better, with pgadmin much better). This is the documentment that I know of: http://www.postgresql.org/docs/8.1/interactive/user-manag.html http://www.postgresql.org/docs/8.1/interactive/client-authentication.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] fetching unique pins in a high-transaction environment...
> Best practice, to me, is to do a couple things. One, create a sequence
> and set it to the first available pin number. Let's say you have pins
> available from the number 1 to . Create a default sequence, it'll
> start on 1. Then, select nextval('yourseqhere') and use that to fetch
> the pin like so:
>
> begin;
> select nextval('yourseqhere'); -- store in a var
> update pin set date_used=now() where id=$var and date_used IS NULL
>
> If date_used is not null, then someone grabbed it from you. Given that
> we're grabbing them using a sequence, this is unlikely, but you never
> know when things might go south.
>
> Otherwise you just reserved it. Then grab it:
>
> select pin from table where id=$var;
> commit;
>
> if a transaction fails, you might not use a pin, no big loss. Better
> than accidentally giving it out twice.
>
> I'd wrap what I just wrote in a simple pl/pgsql script using security
> definer and set the perms so ONLY the user defined function can get you
> a new pin.
It is my understanding that nexval and even currentval are safe across
transactions or even user
sessions. I was curious of the datatype for pin, in the previous example I
think that it was
defined as a varchar. Perhaps casting the sequence to a varchar would be the
finial step before
updating/inserting the records.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Requirement for PostgreSQL Database Developer
Thanks for I think the list that you are looking for is: [EMAIL PROTECTED] --- Mark <[EMAIL PROTECTED]> wrote: > > Hi , > > > Location: San Diego, CA [You can also TeleCommute...] > > Duration: 6+ months. > > > > This is Mark with ProV International, This email is in regards to the > requirement we have with > one of our direct client in San Diego, CA. > > > > PostgreSQL Database Developer > > This position involves creating tables, views, functions and stored > procedures to support front > end OLTP and reporting applications. The ideal developer will have thorough > knowledge of SQL > (PL/pgSQL), experience with at least one other PostgreSQL language (e.g. > PL/Perl), and extensive > experience with complex stored procedures, code optimization, and index > tuning in PostgreSQL. > > > > Ideal candidate will have the following qualifications: > > 5+ years database development with PostgreSQL > > Knowledge of at least one other language in addition to PL/pgSQL, such as > PL/Perl or PL/Java. > > Experience implementing PostgreSQL replication . > > Some experience with either SQL Server 2000 or Oracle 9i/10g. > > Significant background in creating complex stored procedures and SQL scripts > > Understanding of database normalization concepts > > Some experience in logical and physical database design and implementation > > Prior experience working in a project oriented environment and meeting > deadlines under tight > time constraints > > Strong analytical skills > > Capable of working independently with minimal supervision. > > > > > > If you find yourself comfortable with this job profile & find it interesting > please send me your > resume in MS Word Format. > > > > thanks , > > > > Mark, > ProV International > Tampa, FL 33607 > Tel 408 - 241 - 7795 Xtn - 27 > [EMAIL PROTECTED] > www.provintl.com > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] converting Informix outer to Postgres
--- [EMAIL PROTECTED] wrote: > Hi all, > I have been working on this Informix SQL query which has an outer join. > I have attached Informix query and my "supposedly" solution to this query > but I cannot get the same count. I appreciate for any help. > Thanks. > > --Informix query > select count(u.id) > from user u, invention i, inv_contracts ic, inv_milestones im1, milestonedef > mdef1, > OUTER inv_milestones im2, > milestonedef mdef2 > where u.id = i.user_id and > ic.inv_id = i.id and > ic.contract_id = mdef1.contract_id and > im1.inv_id = i.id and > mdef1.id = im1.milestone_id and > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > ic.contract_id = mdef2.contract_id and > im2.inv_id = i.id and > mdef2.id = im2.milestone_id and > im1.datereceived IS NULL > > --Postges query > select count(u.id) > from dbuser u, inv_contracts ic, inv_milestones im1, milestonedef mdef1, > --OUTER inv_milestones im2, > milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = > im2.milestone_id > LEFT OUTER JOIN invention i ON im2.inv_id = i.id > where u.id = i.user_id and > ic.inv_id = i.id and > ic.contract_id = mdef1.contract_id and > im1.inv_id = i.id and > mdef1.id = im1.milestone_id and > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > ic.contract_id = mdef2.contract_id and > --im2.inv_id = i.id and > --mdef2.id = im2.milestone_id and > im1.datereceived IS NULL Is there a reason that these two lines are commented out in the postgresql query? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] PostgesSQL equivalent of the Oracle 'contains' operator
> I'm beginning a port of an Oracle application to PostgrSQL. One > immediate issue is the use of the Oracle 'contains' operator to do > whole word searches on table rows that contain blank separated lists of > names. I was told that the original application was written using the > 'like' operator but the contains operator was found to be much faster. > > Is there a PostgreSQL equivalent for this type of use of the Oracle > contains? The other methods are to use regular expressions, but I do not expect them to be any faster than a like. Have you looked at the contrib module tsearch2? http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ Regards, Richard Broersma Jr. ---(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: RES: [SQL] Inserting data in composite types!
> Thanks, It works! I have tried: > > insert into employee (c, nome, sal) values(1, 'emp name', '(1, (01/01/2000, > 01/01/2000))' ); > > And it doesn't work! What is the error message? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Another question about composite types
> CREATE TYPE t_salary AS (Value numeric(10,2), Validity date); > CREATE TABLE employee (id int, name varchar(30), salary t_salary[]); > > That is, I need an array of composite type and searching on google I found > that is impossible to do it! Is it really impossible? Anyone have do it ? Why not use a conventional table to hold this information that references your employee table? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] unexpected EOF within message length word
> I'm having trouble getting the Windows ODBC drivers to work. They used > to work, but it's been 6 months or so. If you don't find the answer you are looking for on this list, maybe try: [EMAIL PROTECTED] Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] sql problem with join
> Hi all, > I've got a problem tryng to define a view with a few joins, I'll appreciate > if > someone could drive me in the definition of such query. > I've got a table roleSkill that contains a row for each skill belonging to a > defined role and with the desired competence level for such skill in such > role: > roleSkill = (id_role, id_skill, desired_level) PRIMARY KEY(id_role,id_skill) > > Then I've got a table peopleSkill with a row for each evaluated skill for a > person (a skill in this case could not belong to a defined role): > peopleSkill = (id_person,id_skill, evaluated_level) PRIMARY > KEY(id_person,id_skill) > > Finally I've got an association between a person and a role: > peopleRole = (id_person,id_role) PRIMARY KEY(id_person,id_role) > > Now I'd like to build a view that shows a row for each skill a person has > (i.e. it has been evaluated) and should have (i.e. it is listed in the role). > Initially I tried with something like: > select p.*,r.* > from roleSkill r > JOIN peopleRole p on p.id_role=r.id_role /* this gives me all the roles a > person has and all her skills */ > LEFT JOIN peopleSkill s on s.id_skill = r.id_skill/* this should > keep all > the role skills and show the one evaluated */ > > So the first join should give me all the role played from a person, with its > skills, the second join should take the evaluated skills and should keep the > not evaluated (i.e., present only in roleSkill) rows. But this is not > working, I see a lot of rows with the same role for the same person and I > cannot find the error. > Any clues? May be you could show the results you are getting and then make up some results that you would really like to get. I am not entirely clear what you are trying to achieve. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select into
> Hi guys, > >Am new to sql scripting so this might be a stupid question. Am getting an > error while trying > to do the following > > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id; > > where svc_data_rec is defined as record and svc_tbl_name is a varchar that > holds the name of a > table and sub_id is another varchar. the error message is > >ERROR: syntax error at or near "$1" at character 17 >QUERY: SELECT * from $1 where 'uid' = $2 >CONTEXT: PL/pgSQL function "foo" line 132 at select into variables >LINE 1: SELECT * from $1 where 'uid' = $2 > >where am i going wrong? it seems that the variables are not being > substituted with their > values correctly. Notice: http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html and particularly this example: http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html#AEN54014 Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] select into
--- Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > Richard, the plain SQL "INSERT INTO " is an other thing as the > original problem... > > He is in a PL/pgsql - function and he should rewrite this to use EXECUTE > ... INTO > I see, I am not even a novice yet when it comes PL/pgsql. Thanks for the clarification. Regards, Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SQL command join question
> SELECT t1.* > FROM (t1 outer join t2 on (t1.a=t2.a and t1.b=t2.b)) t outer join t3 on ^^ > (t1.c=t3.c); ^^ -- which one t or t1? > I'm getting the following error message: > ERROR: invalid reference to FROM-clause entry for table "t1" > HINT: There is an entry for table "t1", but it cannot be referenced from > this part of the query. I am not use if I am about to give to the correct advice, but here is what stands out at me: when you specify an outer join, I believe that you have to specify whether it is a (left|right|full). Also you are using a "t" to alias the (t1 .. t2 on (...)). First of all, I am not sure this is good syntax, but if I am wrong I will have learned something new. That aside, if it is legal syntax, I don't believe that you can refer to any of it's enternal tables any more. So (t1.c=...) should really be (t.c=...). this is what I expect would work: select * from t1 left outer join t2 on ((t1.a,t1.b) = (t2.a,t2.b)) left outer join t3 on (t1.c = t3.c) ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Setting boolean column based on cumulative integer value
> CREATE TABLE passenger_queue ( > id serial NOT NULL, > name character varying(40) NOT NULL, > weight integer NOT NULL, > gets_seat boolean default false > ) > > insert into passenger_queue values (1,"Peter",75,false) > insert into passenger_queue values (2,"Mary",50,false) > insert into passenger_queue values (3,"John",70,false) > insert into passenger_queue values (4,"Steve",80,false) > > According to the specifications given above Peter, Mary and John would > have 'gets_seat' > set to true because their cumulative weight is 195kg while Steve misses out. > > The big question is: How can I do this in a nice SQL query??? Well there are two ways that I can think of: The first option is probably the best. But the second is a good mental exercise. 1) a trigger that checks to insure that a new record doesn't exceed your max. 2) instead of inserting passenger weight you could insert begin/end weight range for each passenger. i.e.: CREATE TABLE passenger_queue ( id serial NOT NULL, name character varying(40) NOT NULL, plane_start_weight integer NOT NULL, plane_end_weight integer not null, constraint plane_max_wieght check( plane_end_weight <= 200 ), constraint sanity_check check( plane_end_weight > plane_start_weight) ) insert into passenger_queue values (1,"Peter", (select max(end_weight) from passenger_queue), (select max(end_weight) from passenger_queue) + 75); insert into passenger_queue values (2,"Mary", (select max(end_weight) from passenger_queue), (select max(end_weight) from passenger_queue) + 50); insert into passenger_queue values (3,"John", (select max(end_weight) from passenger_queue), (select max(end_weight) from passenger_queue) + 70); insert into passenger_queue values (4,"Steve", (select max(end_weight) from passenger_queue), (select max(end_weight) from passenger_queue) + 80); once you try to insert a record that exceeds your max weight the insert will fail. ofcourse if you have to delete a passenger record because he/she wishes to get off early you will need to have an additional update statement to shift down higher valued records insure that the range does not have any gaps. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Using Control Flow Functions in a SELECT Statement
> *ORA: 00907: Missing right parenthesis* > Query :- > SELECT Name AS Title, StatID AS Status, RatingID AS Rating, > IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify What kind of error is ORA? Is this an oracle error? In postgresql I do not believe that the "IF" predicate exists. However the "CASE" predicate does and will do what you want. http://www.postgresql.org/files/documentation/books/aw_pgsql/node44.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Using Control Flow Functions in a SELECT Statement
> Thanks FOR your prompt responseBut USING CASE issue still NOT resolved > Oracle prompts same error. I see, was answers to you get from the oracle mailing lists regarding this problem? ;o) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Can someone explain the problem with this select
> dcc=# EXPLAIN ANALYZE select * from documents left outer join comments on > (documents.doc_num = comments.doc_num) where documents.doc_num in (select > doc_num from documents limit 10); This query is preforming the join on all records of your two tables. After all of the that exhaustive work is done, it this filter out the records you want. you should preform a filtered select first and then use those results in you left join. I guess the lesson you can learn from this example is that you should try to filter your data set to get it as small as possible before you do anything else with it. select * from ( select doc_num from documents limit 10 ) as D1 left outer join comments on (D1.doc_num = comments.doc_num) ; Regards, Richard Broersma Jr. ---(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] I don't want receive more emails
> hi, could you tell me, what have I do for not reveice more emails Send an email to: [EMAIL PROTECTED] Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Need to subtract values between previous and current row
> > ID ATIME (MM/dd/) > == > 110/12/2006 > 210/14/2006 > 310/18/2006 > 410/22/2006 > 510/30/2006 > > Now I need a query that will subtract atime of row 1 from row 2, row2 from > row3 and so on... just an idea. select (A.atime - max(B.atime)) duration from table A join table B on (A.atime > B.atime) group by A.atime; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
