Don't 'kill -9' the postmaster-- ======
Aaron Bono PresidentAranya Software Technologies, Inc. http://www.aranya.com We take care of your technology needs. Phone: (816) 695-6071
==
It is a hack, but when someone wants you to do something in a way
different from the norm, aren't they asking for a hack?
SQL Server does something like
select top (1) from
I am thinking this is NOT a SQL-99 standard.
-Aaron
On 6/1/06, Yasir Malik <[EMAIL PROTECTED]> wrote:
> select max(
Is this SQL-99 compliant or a PostgreSQL specific query? I really like it and have never seen this before.-AaronOn 6/1/06, Tom Lane <
[EMAIL PROTECTED]> wrote:"Collin Peters" <
[EMAIL PROTECTED]> writes:> What I would like to do is simply get the last date_sent and it's> status for every broadcast
Only if you assume that ordering by unique_id and by date_sent are equivalent. That may be the case but I personally hate making assumptions like that. When someone goes into the database and updates records (clean up bad data, etc.) your perfectly running query can suddenly produce bad results.
This reminds me of an interview question: I was asked how to get a
maximum column from a table without using max. How would you do that?
Select my_column
from my_table
order by my_column desc
limit 1
--
==
Aaron Bono
President
g:
select status
from broadcast_history bh
where bh.date_sent =
(select max(bh2.date_sent)
from broadcast_history bh2);
======
Aaron Bono
PresidentAranya Software Technologies, Inc.
http:
I think this approach will only work if each broadcast_id has the same
maximum date_sent value. You really need to do the group by in a
sub-query to bring the broadcast_id together with the max date_sent.
-Aaron Bono
On 6/1/06, Yasir Malik <[EMAIL PROTECTED]> wrote:
> What I would l
Don't forget that support is a very important part of making a decision about whether to or not to use a technology. Having people who are happy to read and respond to any question is part of great support for the product.
And I am glad to see that most people on this list agree with me on the imp
Though there may be a more eligant way to do it, when we did things like this in the past we created a function (or stored procedure) that got the min and max dates and then created a result set that iterated through the dates to create a virtual table of days. Then you can inner join that list of
select id, first || ' ' || lastfrom mytable;On 6/8/06, George Handin <[EMAIL PROTECTED]> wrote:
Is there a way using built-in PostgreSQL functions to combine two datafields into a single field at runtime when querying data?
For example, the query now returns:idfirstlast--- --- --
That will work if foo is never NULL. If foo takes on a NULL value you will get those records where it is NULL.Is there a reason you cannot do an if/else statement on the list size? That is what I do on my queries.
-AaronOn 6/8/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Thu, 2006-06-08 at 16:
Can you provide an example?Thanks,AaronOn 6/11/06,
Phillip Smith <[EMAIL PROTECTED]
> wrote:
Hi All,
Hope someone can help me – our main company system
runs on Raining Data PICK/D3 (if anyone familiar with it) which stores records
in it's "tables" as variable length items. Every
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.What language are you using to do the export if I may ask?
-AaronOn 6/12/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Phi
I think two tables should suffice: ZKCOST and ZPRECMPL.
So you would have
ZKCOST
zkcost_id,
zkcost_value
and
ZPRECMPL
zkcost_id,
zprecmpl_id,
zprecmpl_value
where zkcost_id is the primary key for ZKCOST and zkcost_id,
zprecmpl_id together are the primary key for ZPRECMPL and zk
So how about creating a sys table too:
SYS
sys_id
ZKCOST
sys_id,
zkcost_id,
zkcost_value
and
ZPRECMPL
sys_id,
zprecmpl_id,
zprecmpl_value
This gives you the flexibility to expand to as many "columns" for
ZPRECMPL as you want. The bottom line is, I think it would be much
more ef
re available to make such searching
easy to implement well?
3. What is the best way to sort by relevance?
Thanks,
Aaron Bono
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
I will look that tsearch (at . It appears their 8.1.x version is
still in development and I use PostgreSQL 8.1.3 but it is worth trying
- I'm not in a hurry for that feature anyway.
I also looked at PHPBB a little - it appears their database stores
words but the code is so difficult to dig throu
I guess I still don't understand...
If you take the approach operationsengineer1 and I suggested, you
should only need 3 or 4 tables regardless of the number of SYS file
records.
Good luck with your implementation.
-Aaron
On 6/13/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
Thanks Aaron - The
Try select to_char(now(),'HH24:MI');
-Aaron
On 6/13/06, chester c young <[EMAIL PROTECTED]> wrote:
this does not work: select to_char(current_time,'HH24:MI')
what am I missing? is it possible to format a time column in a select?
thanks,
stumped, aka, chester
---(end
That character is the EN Dash. Are you by chance copying and pasting
from MS Word or some other program that does smart replace while you
type? I don't see this character in your select. Is there something
else that is running that may be causing this problem?
-Aaron
On 6/14/06, Sergey Levche
Each of your queries has the filter xxx >= $dt where the xxx is the first column in each select. You could simplify the query by turning the unioned selects into a sub-query and then putting the $dt filter in the outer query.
I don't know if this will cause performance problems though. If Postgre
I would use a BIGSERIAL for the ID. It simplifies your inserts, you don't have to mess with any locking and the sequence is maintained for you outside your transaction so two transactions can do inserts without stepping on each other.
This is how I handle auto generated numbers.The only downside i
If you are using pooled connections, doesn't PostgreSQL manage the prepared statements for you? I would expect that, once I prepare a statement, if I attempt to do it again, PostgreSQL would say, "righty then, already done it, here you go". Then again, I don't know what PostgreSQL does under the
I know this is a Java issue but I would recommend something more like:
Statement stmt = con.createStatement(); try { stmt.execute("begin"); stmt.execute("lock table rcp_patient_visit_monitor"); psSave.executeUpdate(); //psSave is a prepared statement
stmt.execute("
d to create a view for each small query and name them something meaningful and then another view that does the union. It would make the queries easier to understand at least (self documented).
-AaronOn 6/16/06, Joe <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:> Each of your queries has the filte
When in this situation I:1. Wait until I have enough data to do a complete commit before even bothering to save any data to the database. I want the life of my transactions to last no more than milliseconds if possible.
2. Use a BIGSERIAL for the primary keys so the IDs are assigned automatically
Looks good but you really shoud put your stmt.close() and conn.close() in a finally block so even if there is an error everything gets cleaned up properly. That syntax is for Java but the principle is the same for any programming language - always make sure you clean up your connections no matter
I would suggest:selectmax(time_occurred) AS last_seen,min(time_occurred) AS first_seen,count(*),prog_datafrom tgroup by
prog_dataI would also suggest you use inner joins rather than put all your tables in the from and join in the where clause. It is much easier to read and understand what you are
I am a little confused. Where are you casting dateStart and dateEnd? I don't see either in your query. I assume dayOfWeek is a number between 0 and 6, or maybe not?A little more detail would help.Thanks,
Aaron BonoOn 6/15/06, joseppi c <[EMAIL PROTECTED]> wrote:
Hi,I have a table which contains
I would use a stored procedure or function for this. You order your results first by employee and then event date and finally even time. Then you create a new result set from the first and return that.That would probably be the most straight forward approach.
You could also try doing some thing l
Why not just create a history table and have the trigger copy the data out of the table into the history table with a time stamp of the change. Then you don't need the query.For exampleTable Aa_id,a_value1,
a_value2Table A_hista_id,a_dt,a_value1,a_value2Then A_hist has a PK of a_id, a_dtThis would
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 pos
This is why I was searching for good meta data.Here is a thought. If your trigger has the OLD and NEW, is there a way to get a list of fields from OLD and NEW? If TG_RELNAME is the name of the table, could you just ask PostgreSQL what the columns are in that table, iterate through those columns,
Right, the 23 is just less than 1 day, not 23 days.The good news: your query is working!-AaronOn 6/26/06, Joe <
[EMAIL PROTECTED]> wrote:Tom Lane wrote:> "Jeremiah Elliott" <
[EMAIL PROTECTED]> writes:>> however if i don't average them here is what i get:>> "7 days 22:24:50.62311";"*2420">> "9 days
How about one of these two:select year_list.year, count(one.*), count(two.*)from ( select years from generate_series(2006,2009) as years) year_listleft outer join mytable as one on (
date_part('year', one.date) = year_list.years and one.cause = 1)left outer join mytable as two
year_list.year) two_listWHERE one_list.year = two_list.year;On 6/28/06,
Pedro B. <[EMAIL PROTECTED]> wrote:
On Wed, 2006-06-28 at 13:34 -0500, Aaron Bono wrote:> select> year_list.year,> count(one.*),> count(two.*)> from (> select years> from generate
2 group by date_part('year',
two.date)) two_list on (year_list.years = two_year);On 6/28/06, Aaron Bono <[EMAIL PROTECTED]
> wrote:Sorry, I think I see the mistake - it is getting all the rows for 1 and all the rows for 2 and joining them. Try splitting up th
I would recommend against using a function. If you are selecting a large number of rows, the function will run for each row returned and will have to do a select for each row. So if you get 1000 rows returned from your query, you will end up with 1001 select statements for your one query.
Assumin
yet to warrant spending a lot of time researching this but if someone with more experience knows what is best I would love to hear about it.Thanks,Aaron Bono
On 6/29/06, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> SELECT trans_no,> customer,> date_plac
I suggest you give a first stab at it and show us what you are doing. That would help us see your table relationships better (please use inner/outer joins to make it clearer) and get a better idea of what you are trying to do.
Also, providing data examples like some of the other posts really help
I agree, using ODBC is probably a good first step. Especially for M$Access.For developers I recommend the EMS Manager tools. They are a commercial product but I have been very pleased with them and we use the tools every day. They are not exactly like SQL Manager but they serve the same purpose.
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/deactiva
You can start by creating 3 views for your 3 categories:CREATE OR REPLACE VIEW cat_a ( account_id, sales_cat_a) ASSELECT
account_id,
sum(sale_price) as sales_cat_aFROM sales -- Assuming sales is the table with the dataWHERE product_id in ('prod1', 'prod2')GROUP BY account_idThen do
On 7/5/06, Erik Jones <[EMAIL PROTECTED]> wrote:
Ok, I have a trigger set up on the following (stripped down) table:CREATE TABLE members (member_id bigint,member_status_id smallint,member_is_deletedboolean);Here's a shortened version of the trigger function:
CREATE OR REPLACE FUNCTION updat
On 7/5/06, Erik Jones <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:> On 7/5/06, *Erik Jones* <[EMAIL PROTECTED] [EMAIL PROTECTED]>> wrote:>> Ok, I have a trigger set up on the following (stripped down) table:
>> CREATE TABLE members (> member_i
On 7/5/06, Keith Worthington <[EMAIL PROTECTED]> wrote:
Hi All,I just finished writing a query that groups data based on the week number.SELECT EXTRACT(week FROM col_a) AS week_number,sum(col_b) AS col_b_total FROM foo WHERE foobar GROUP BY EXTRACT(week FROM col_a)
ORDER BY EXTRACT(we
On 7/6/06, David Clarke <[EMAIL PROTECTED]> wrote:
I posted a couple of weeks back a question regarding the use of a 100char column as a primary key and the responses uniformily advised theuse of a serial column. My concern is that the key is effectivelyabstract and I want to use the column as a fo
On 7/6/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
i alsways make my foreign key column data type int4.i'm not sure if i read that somewhere. anyone, please
feel free to chime in if this isn't good practice.read up on currval, nextval and that whole section.you can begin by getting the nextva
K necessary. If you index addresses.address the insert should run quickly, right?-Aaron Bono
error prone but much more difficult.It is good to see different philosophies about foreign keys though!
-Aaron Bono
On 7/7/06, David Clarke <[EMAIL PROTECTED]> wrote:
The question remains regarding the use of a string value as a primarykey for the table and as a foreign key in other tables. If I use theaddress column as a foreign key in a differrent table will postgresphysically duplicate the data or will it sim
On 7/7/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
Same SELECT query as before, different area
of it… I have a function that calculates the recommended purchase order
quantity for a stock item based off various other values and functions:
pqty(stock.code) AS "pqty"
This needs to be rounded up
.code)
= -1 THEN '@'ELSE ''END||CASE WHEN
stock.kit_pack = 'Y' THEN 'K'ELSE ''
ENDAS "flags"
Is this what you are looking for?-Aaron Bono
On 7/7/06, Scott Marlowe <
[EMAIL PROTECTED]> wrote:
On Fri, 2006-07-07 at 13:07, Stephan Szabo wrote:> On Fri, 7 Jul 2006, Michael Glaesemann wrote:>> >> > On Jul 7, 2006, at 7:55 , Weber, Johann (ISS Kassel) wrote:> >> > > My concern: in a multi threaded environment, can a second thread
> > > in
On 7/7/06, Rodrigo De Leon <[EMAIL PROTECTED]> wrote:
On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:> But that takes me to the next problem:>> For the sake of the example I simplified the regular pattern.
> In reality, BASE_NAME might be:>> 28mm> 28-70mm>> So the reg. expr. requires brackets:>>
On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:
I am trying to come up with a semi-automatic solution to tidy up somedata. If it's got to be done manually via the GUI it would mean a lot ofdummy work [for the customer].I would recommend you alter the GUI to NOT allow any more bad data to get in -
On 7/10/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
Beautiful – Works a treat. Thanks
Aaron.
A follow-on problem now… I have the
below column in the select, but I need to validate the value across all 3 rules
– I need to assign it to a variable!!
Example – my pqty function
On 7/10/06, Kevin Bednar <[EMAIL PROTECTED]> wrote:
Looking to keep 2 databases in
sync, at least semi-realtime if possible, although running a batch update
every x mins wouldn't be out of the question. One db is postgres and the
other is ms-sql. It's to keep inventory in sync from 2
Please reply to all when replying on the list...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.
Ta
On 7/10/06, Bryce Nesbitt <[EMAIL PROTECTED]> wrote:
I have a function, the results of which seem to apply to ORDER BY andHAVING, but not to WHERE. Is this expected?-- Return distance in some mystery units (TODO: convert to miles orkilometers)CREATE FUNCTION calculate_distance(double precision, do
On 7/10/06, Bryce Nesbitt <[EMAIL PROTECTED]> wrote:
I think it is ugly also, but no other syntax seems to work:stage=# selectpod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) asdist from eg_pod where dist < 1 order by dist desc limit 10;
ERROR: column "dist" does not existSELECT
On 7/10/06, Bryce Nesbitt <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:>>> On 7/10/06, *Bryce Nesbitt* <[EMAIL PROTECTED]> [EMAIL PROTECTED]>> wrote:
>>> I think it is ugly also, but no other syntax seems to work:>> stage=# select> pod_code
On 7/10/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
Example:
Funcation
pqty(stock.code) calculates a value of 0 for a particular product. This fails the
last CASE that makes sure the pqty()
value is greater than
our Usage Rate *
Review Cycle – in this case is
3. But that is less
ns with non-constant values.I agree whole heartedly with Tom, using the number in the ORDER BY is ugly and not recommended. Using column names is much easier to read and is much more maintainable by team members. I have to admit the 4 < 1 did confuse me at first.
-Aaron Bono
nd before the insert. Don't know if PostgreSQL will prevent that with table locking or not.
== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
ries and run them on production)
Keep history without having to run the report on everything (my refreshes update this month and last month - this provides enough overlap that I don't have to worry about changing months, leap year, etc.)
Hope this helps!===
62 | 37.780166 | -122.409615 | 0.9449072731025414 | 37.798528 | -122.409582 | 0.919592583879426
86 | 37.777529 | -122.417982 | 0.866416010967029 68 | 37.789915 | -122.406926 | 0.82867104307647(5 rows)==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
the debugger, works with no errors so I know there is something wrong with the debugger.
Thanks,Aaron-- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
==
pack); or
SELECT controller_id FROM controlWHERE controller_id NOT IN(SELECT controller_id FROM datapack GROUP BY controller_id); Guess you need to do some explain plans to see which would be best.
Good luck!
====== Aaron Bono Ara
rue on PostgreSQL also?
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
ne Technology>> (514) 490 2040 ext. 3613
--Adrian Klaver[EMAIL PROTECTED]Or to be more specific:IF (TG_OP = 'UPDATE') THEN==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
INNER and OUTER joins for connecting the tables by their foreign keys.
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
hought by using the FOR '#' I could specify exactly what part of the _expression_ I would get but it still grabs the first (...) of the pattern. At least that is what the documentation in seciton
9.7.2 at http://www.postgresql.org/docs/8.1/static/functions-matching.html led me to believe.
record from City table ( table contains
city, pop, state only ).
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
==
AND ErrorValues.value = '1.2.3.4') INTERSECT SELECT
ErrorValues.id,
FROM ErrorValues
WHERE (ErrorValues.name = 'Type' AND ErrorValues.value = 'OSX Name/Value pairs') );== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com==
sometimes it's easier to validate if it looks like this. "a.ErroID = b.ErrorID
". Just my 2 cents...I wholeheartedly agree. If you don't adopt a good naming convention like this, you will come to regret it as your application grows in size and complexity.
=====
ngs up.==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
out getting the count of updates but when you run the update by itself, PostgreSQL reports the number of records updated so I have to believe the information is available somewhere.
Thanks,Aaron== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
==
On 7/18/06, Bricklen Anderson <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:> On 7/18/06, *Michael Fuhr* <[EMAIL PROTECTED] [EMAIL PROTECTED]>> wrote:>
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS>> OK, this qu
have to put them back later to decode it. I usually leave the return characters where they are.I am curious, why did you decide to remove the return characters?
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
nd revoke all on it. Then add only the permissions it needs and assign the role to the user.======
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
_activity_id, user_id ) VALUES ( SELECT currval('user_activity_user_activity_id_seq'), NEW.user_id ););
I am assuming user_activity.user_activity_id is a BIGSERIAL or SERIAL.========
iving non-experts too much access to your live production data.====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com==
off of the live data.
Good luck!====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
==
of the column name id all over the place will make your queries more prone to error, harder to read and harder to write.
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
amount) pieces of candy and the last (extra) get (dividedamount + 1) pieces of candy.==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
hanks==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
ra 3.But it looks like Ross got you the code to fix the problem. Check what he provided.
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
go with your first approach, a simple order field. Unless the user is reordering a small number of items in a very large list and doing it frequently, is there really a need to worry about the number of updates? Are you worrying about a performance problem you will never have?
===
nd write anyway? It is a hell of a lot easier to read and write than any other computer language I have seen.==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
e you call to reorder the elements that decides how to do it for you so you can easily rewrite the implementation without having to change the application.
Just some ideas...== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
==
Then your problem is solved.
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
ouch the tables directly - it ads another layer of abstraction between the tables and the application. But that may be more complicated than you are ready for - I have yet to decide if it will be worth the effort but I won't know until I experiment with it more.
===
x27;', '' || '' '''''' || NEW.my_value || '''''', '' || '' now() '' || '');'' ; RETURN NEW; END IF;END;'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT ON my_table FOR EACH ROW EXECUTE PROCEDURE my_table_history_fn();I tried it out and it works in version 8.1.==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
7.4.5 also and it worked. I'll be able to continue with my development using the syntax of that example.
Aaron Bono <
[EMAIL PROTECTED]> wrote: On 7/30/06, Thusitha Kodikara <
[EMAIL PROTECTED]> wrote: Hello,I am interested in developing some triggers to keep track of records tha
ot;Scenarios";"Authorise";"Action";"B"
11900;"Scenarios";"Create";"Action";"C"11900;"Scenarios";"Update";"Action";"C"I am guessing it should be an inner join? but by reference book does not
ws you to "delete" rows at sometime in the future or make them appear in the future too.
On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote:> CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF
> opaque AS> '> BEGIN> -- if a trigger insert or update operati
s well maintained.
Anyone care to disagree?====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
==
en you or another developer look at this in the future, you understand right away how the tables are being put together.
== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
ix-ups when hand writing your queries.
I am glad the queries worked for you.-Aaron====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com==
1 - 100 of 194 matches
Mail list logo
