Re: [SQL] Fetching BLOBs
On mið, 2006-12-20 at 08:55 +0530, Ashish Ahlawat wrote: > I have a very intersting question to all of you. Pls help me to build > this query, I want to fetch more that 70,000 BLOB from different > customer servers. the issue is there are some BOLB files with common > names on all servers. So I want merge these files into a single > BLOB during fetching data. I am able to fetch the BLOB data from all > cust servers but unfortunatelly it overwrite previous file. > > So pls provide any simple query format for the same, assuming two > table tab1 & tab 2 a bit of friendly advice on how to ask on these lists: I you do not get any useful replies to a question, you should follow up with more details, more specific questions, show what you have tried so far, and tell us in what way that did not work. It is unlikely that repeating the same question verbatim 3 times in different threads, CC'd to some random list users will give better results than the first posting did. Remember that many of us are not native english speakers, so describe your problem as clearly and precisely as possible. For example, it is totaly unclear to me what your actual problem is. gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] join/group/count query.
Hi folks. I have the following query which works for me at the moment.
However, o_model refers to a table stock_models which has one record for each
model type. At the moment if I another record to the stock_models I have to
amend the select. Is it possible to make this automatic by joining the
stock_models data somehow?
select o_p_id as key, o_p_id as p_id, p_name, count(o_p_id) as total,
count (case when o_model = 5 then 1 else NULL end) as KA,
count (case when o_model = 10 then 1 else NULL end) as Focus,
count (case when o_model = 13 then 1 else NULL end) as C_Max,
count (case when o_model = 16 then 1 else NULL end) as S_Max,
count (case when o_model = 20 then 1 else NULL end) as Fiesta,
count (case when o_model = 25 then 1 else NULL end) as Fusion,
count (case when o_model = 30 then 1 else NULL end) as Mondeo,
count (case when o_model = 35 then 1 else NULL end) as Galaxy,
count (case when o_model = 40 then 1 else NULL end) as Ranger,
count (case when o_model = 50 then 1 else NULL end) as Connect,
count (case when o_model = 60 then 1 else NULL end) as Transit,
count (case when o_model = 70 then 1 else NULL end) as Fiesta_Van
from order_details
where date_trunc('month',CURRENT_DATE) = date_trunc('month',o_ord_date)
group by o_p_id, p_name;
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] join/group/count query.
On mið, 2006-12-20 at 10:12 +, Gary Stainburn wrote:
> Hi folks. I have the following query which works for me at the moment.
> However, o_model refers to a table stock_models which has one record for each
> model type. At the moment if I another record to the stock_models I have to
> amend the select. Is it possible to make this automatic by joining the
> stock_models data somehow?
>
> select o_p_id as key, o_p_id as p_id, p_name, count(o_p_id) as total,
>count (case when o_model = 5 then 1 else NULL end) as KA,
>count (case when o_model = 10 then 1 else NULL end) as Focus,
>count (case when o_model = 13 then 1 else NULL end) as C_Max,
>count (case when o_model = 16 then 1 else NULL end) as S_Max,
>count (case when o_model = 20 then 1 else NULL end) as Fiesta,
>count (case when o_model = 25 then 1 else NULL end) as Fusion,
>count (case when o_model = 30 then 1 else NULL end) as Mondeo,
>count (case when o_model = 35 then 1 else NULL end) as Galaxy,
>count (case when o_model = 40 then 1 else NULL end) as Ranger,
>count (case when o_model = 50 then 1 else NULL end) as Connect,
>count (case when o_model = 60 then 1 else NULL end) as Transit,
>count (case when o_model = 70 then 1 else NULL end) as Fiesta_Van
>from order_details
>where date_trunc('month',CURRENT_DATE) = date_trunc('month',o_ord_date)
>group by o_p_id, p_name;
if I understand correctly, you want one column in your output,
for each row in the table table stock_models
you can do this with the crosstabN function in the contrib
module 'tablefunc', or by making your own procedural language
function.
gnari
---(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] Help with quotes in plpgsql
It makes sense when ya'll explain it It never sounds that good when I'm talkin to myself That solves my problem but not my ignorance I'm still curious about how would I properly quote create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - interval '$1 day'; return next a; return; end $$ language 'plpgsql'; I got the usage example for interval from http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html Thanks Richard On Tue, 19 Dec 2006, Tom Lane wrote: "Hector Villarreal" <[EMAIL PROTECTED]> writes: select into a now() - ($1::text||'days')::interval; People keep suggesting variants of that as ways to convert numeric values to intervals, but it's really extremely bad practice. Much better is to use number-times-interval multiplication: select into a now() - $1 * '1 day'::interval; This is less typing, at least as easy to understand, more flexible (you can use any scale factor you want), and considerably more efficient. The first way involves coercing the integer to text, then text-concatenating that with a constant, then applying interval_in which does a fairly nontrivial parsing process. The second way is basically just a multiplication, because '1 day'::interval is already a constant value of type interval. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(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] Help with quotes in plpgsql
Richard Ray wrote: It makes sense when ya'll explain it It never sounds that good when I'm talkin to myself That solves my problem but not my ignorance I'm still curious about how would I properly quote create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - interval '$1 day'; The basic mistake is that you're assuming strings interpolate variables. This isn't true in plpgsql, never has been and probably never will. So you can't do: my_msg := 'Hello $1, how are you?'; You need: my_msg := 'Hello ' || $1 || ', how are you?'; Don't forget most variables don't have a leading dollar sign. Imagine you'd defined a variable called "day" in test() - you wouldn't expect that to be interpolated, would you? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] join/group/count query.
Ragnar wrote:
On mið, 2006-12-20 at 10:12 +, Gary Stainburn wrote:
Hi folks. I have the following query which works for me at the moment.
However, o_model refers to a table stock_models which has one record for each
model type. At the moment if I another record to the stock_models I have to
amend the select. Is it possible to make this automatic by joining the
stock_models data somehow?
select o_p_id as key, o_p_id as p_id, p_name, count(o_p_id) as total,
count (case when o_model = 5 then 1 else NULL end) as KA,
count (case when o_model = 10 then 1 else NULL end) as Focus,
count (case when o_model = 13 then 1 else NULL end) as C_Max,
count (case when o_model = 16 then 1 else NULL end) as S_Max,
count (case when o_model = 20 then 1 else NULL end) as Fiesta,
count (case when o_model = 25 then 1 else NULL end) as Fusion,
count (case when o_model = 30 then 1 else NULL end) as Mondeo,
count (case when o_model = 35 then 1 else NULL end) as Galaxy,
count (case when o_model = 40 then 1 else NULL end) as Ranger,
count (case when o_model = 50 then 1 else NULL end) as Connect,
count (case when o_model = 60 then 1 else NULL end) as Transit,
count (case when o_model = 70 then 1 else NULL end) as Fiesta_Van
from order_details
where date_trunc('month',CURRENT_DATE) = date_trunc('month',o_ord_date)
group by o_p_id, p_name;
if I understand correctly, you want one column in your output,
for each row in the table table stock_models
you can do this with the crosstabN function in the contrib
module 'tablefunc', or by making your own procedural language
function.
gnari
And, I may be missing something, but I'm having a hard time
understanding why you have all of those select columns of the form:
count (case when o_model = 5 then 1 else NULL end) as KA,
Considering that that can only return 1 or 0, the case statement would do. Is
it to avoid putting all of the column names in the group by clause? That's
hackish and is as much or more typing.
With regards to what you are actually trying to do, giving us your table
definitions and what you are trying to achieve would help a lot more than just
telling us the problem you are having. The column names in your query are in
no way descriptive and tell us nothing about your actual table structure.
--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] join/group/count query.
> > And, I may be missing something, but I'm having a hard time > understanding why you have all of those select columns of the form: > > count (case when o_model = 5 then 1 else NULL end) as KA, > > Considering that that can only return 1 or 0, the case statement would do. > Is it to avoid putting all of the column names in the group by clause? > That's hackish and is as much or more typing. > > With regards to what you are actually trying to do, giving us your table > definitions and what you are trying to achieve would help a lot more than > just telling us the problem you are having. The column names in your query > are in no way descriptive and tell us nothing about your actual table > structure. The order_details view is a join of the orders table to the other tables. The order table contains the orders. Each order has a business partner which is in a separate table. p_id is the key, p_name is the name of the partner. Each order is for a single vehicle, which must be of a specific mode. The models are also stored in a separate table. o_model is the attribute in the orders table that contains the key to the models table. The output I'm getting is below, which is what I want. For each partner I get a total followed by a breakdown by model the orders for the current month. key|p_id|p_name | total | ka | focus | c_max | s_max | fiesta | fusion | mondeo | galaxy | ranger | connect | transit | fiesta_van ---+++---++---+---+---++++++-+-+ 40| 40|rrr | 2 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 |0 30| 30|r Group Plc | 2 |0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |0 78| 78|r r | 2 |0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |0 46| 46| ) | 3 |0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 |0 3| 3|e | 8 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 4 |0 9| 9|eee | 3 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 2 |0 12| 12||13 |0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 5 | 6 |0 10| 10|rr Motor| 7 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 5 |0 34| 34|ff fff | 3 |0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 2 |0 102| 102| xxx| 1 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |0 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] join/group/count query.
Gary Stainburn wrote: And, I may be missing something, but I'm having a hard time understanding why you have all of those select columns of the form: count (case when o_model = 5 then 1 else NULL end) as KA, Considering that that can only return 1 or 0, the case statement would do. Is it to avoid putting all of the column names in the group by clause? That's hackish and is as much or more typing. With regards to what you are actually trying to do, giving us your table definitions and what you are trying to achieve would help a lot more than just telling us the problem you are having. The column names in your query are in no way descriptive and tell us nothing about your actual table structure. The order_details view is a join of the orders table to the other tables. The order table contains the orders. Each order has a business partner which is in a separate table. p_id is the key, p_name is the name of the partner. Each order is for a single vehicle, which must be of a specific mode. The models are also stored in a separate table. o_model is the attribute in the orders table that contains the key to the models table. The output I'm getting is below, which is what I want. For each partner I get a total followed by a breakdown by model the orders for the current month. key|p_id|p_name | total | ka | focus | c_max | s_max | fiesta | fusion | mondeo | galaxy | ranger | connect | transit | fiesta_van ---+++---++---+---+---++++++-+-+ 40| 40|rrr | 2 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 |0 30| 30|r Group Plc | 2 |0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |0 78| 78|r r | 2 |0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |0 46| 46| ) | 3 |0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 |0 3| 3|e | 8 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 4 |0 9| 9|eee | 3 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 2 |0 12| 12||13 |0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 5 | 6 |0 10| 10|rr Motor| 7 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 5 |0 34| 34|ff fff | 3 |0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 2 |0 102| 102| xxx| 1 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |0 Ok, then, yeah, I read those count statements wrong and the crosstab contrib is what you're looking for. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] join/group/count query.
HI in reading this is it possible what was really intended : Sum(case when o_model = 5 then 1 else NULL end) as KA That would provide a count of all records meeting that condition. Otherwise the count( approach will not do that. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gary Stainburn Sent: Wednesday, December 20, 2006 8:20 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] join/group/count query. > > And, I may be missing something, but I'm having a hard time > understanding why you have all of those select columns of the form: > > count (case when o_model = 5 then 1 else NULL end) as KA, > > Considering that that can only return 1 or 0, the case statement would do. > Is it to avoid putting all of the column names in the group by clause? > That's hackish and is as much or more typing. > > With regards to what you are actually trying to do, giving us your table > definitions and what you are trying to achieve would help a lot more than > just telling us the problem you are having. The column names in your query > are in no way descriptive and tell us nothing about your actual table > structure. The order_details view is a join of the orders table to the other tables. The order table contains the orders. Each order has a business partner which is in a separate table. p_id is the key, p_name is the name of the partner. Each order is for a single vehicle, which must be of a specific mode. The models are also stored in a separate table. o_model is the attribute in the orders table that contains the key to the models table. The output I'm getting is below, which is what I want. For each partner I get a total followed by a breakdown by model the orders for the current month. key|p_id|p_name | total | ka | focus | c_max | s_max | fiesta | fusion | mondeo | galaxy | ranger | connect | transit | fiesta_van ---+++---++---+---+---++ ++++-+-+ 40| 40|rrr | 2 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 |0 30| 30|r Group Plc | 2 |0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |0 78| 78|r r | 2 |0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |0 46| 46| ) | 3 |0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 |0 3| 3|e | 8 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 4 |0 9| 9|eee | 3 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 2 |0 12| 12||13 |0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 5 | 6 |0 10| 10|rr Motor| 7 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 5 |0 34| 34|ff fff | 3 |0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 2 |0 102| 102| xxx| 1 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |0 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
