[SQL] DateDiff, IsNull?
Hello all, I am a newbie to postgreSQL, is it possible to write a "DateDiff", "IsNull" function work same as the one in SQL Server and are there and sample out there? And is it possible to override the existing function and operator like "+" to become a concate? Regards Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: DateDiff, IsNull?
IsNull work like this SELECT IsNull( user_name, '') FROM user ; then the query will return empty string when the field user_name is null Regards Bill > > Hello all, > > > > I am a newbie to postgreSQL, is it possible to write a "DateDiff", > > "IsNull" function work same as the one in SQL Server and are there and > > sample out there? And is it possible to override the existing function and > > operator like "+" to become a concate? > > I don't know what IsNull does but: > > template1=> select coalesce(NULL, 'Hello'); > case > --- > Hello > (1 row) > > I do know what DateDiff does: > > template1=> select '2001-08-14'::date - '6 weeks'::interval; > ?column? > > 2001-07-03 00:00:00-05 > (1 row) > > Overloading operators? *Please* tell me that this is impossible. > > Alan Gutierrez ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Thanks
Thanks all for the help :) I got to do all this, because there a existing system running on SQL Server/SQL Anyway which using all these syntax though out the coding, and I got to make it support postgreSQL And here is a question relate to the DataDiff function in SQL Server, is it possible to write a function that pass the parameter like the DataDiff function, I mean: DataDiff( Year, date1, date2) but not DataDiff( 'Year', date1, date2) Is it possible to doing this? Define a enum data type? Thanks Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Function define question
Hello all, Is it possible to define a function to pass parameter like this? function( parameter ) but not function( 'parameter' ) Regards Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Database reporting tool
Hi Jerome - We have developed a web based ad hoc report builder (LGX Ad Hoc) that seems to fit your requirements rather well at first glance. http://www.logixml.com/products/AdHoc/adhoc.htm LGX Ad Hoc is a zero footprint .NET and XML based web application. It provides both an end-user and administrator module. End-users are provided an easy-to-use wizard that allows them the ability to build their reports off of business objects (tables, views) that you can easily set up in a meta data layer. With the wizard users can do things like: * Add charts to their report * Choose dynamic sorting for columns * Choose Export options like PDF, Excel, Word * Set up paging and printing options * Build parameters including calendar controls * Add a grouping level to reports for sub or drill down reports LGX Ad Hoc can connect to PostgresSQL via an ODBC driver. We can also work with databases like Oracle, MySQL, DB2, and SQL Server. Thanks Bill Kotraba LogiXML > On Wed, Mar 10, 2004 at 02:17:31PM +0800, [EMAIL PROTECTED] wrote: > > > > i'm using PostgreSQL as my database and now i'm looking for a good > > reporting tools(can do ad-hoc queries & ease of use) > > that can connect to several database instead of PostgreSQL. > > thanks in advance > > see : http://www.openoffice.org > > bye > > Jerome Alet > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] I'm missing outer joins
Hello! Well, I have a need for an OUTER JOIN, but have semi-solved it by using a UNION. However, I can't do an ORDER BY on a UNION, and have since discovered that a VIEW can not be used with a UNION. Is there another way (possibly with one or more FUNCTIONs), for me to get this join on two tables (where one table may have no matching rows) via a single sorted query? Thanks! Bill
Re: [SQL] New book on Postgres
Hello! wers regarding a book proposal on Regarding the book proposal on Postgres: I can tell you that the world needs a Postgres book! Specifically, I would like to see the following topics covered: - table-inheritance: what does it mean, how does it work, examples - regular expressions in queries: examples - PL/pgSQL: thorough coverage and examples - pg_options file: complete explanation of all options, and detailed coverage of performance-tuning and troubleshooting - creating and adding new types/operators/etc. The most important things to remember are: 1) We do not need another SQL book, we need a PostgreSQL book 2) examples, examples, examples Thanks! B-) -- [EMAIL PROTECTED] Bill.Wadley.org/PGP_KEY.html "The dinosaurs became extinct because they didn't have a space program." -Larry Niven
[SQL] LEAST and GREATEST functions?
Hi, I know the LEAST and GREATEST functions are not part of standard SQL, but they sure were handy where I came from (Oracle-land). Has anyone written user-defined functions that do the same thing? Are there any plans to add these functions as part of a future version Postgres? Thanks, -Stefan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LEAST and GREATEST functions?
> Um, what's wrong with MAX and MIN, exactly? MIN and MAX are aggregate functions, LEAST and GREATEST are not. See the examples on the following table: foo A B - - 1 4 2 3 3 2 > SELECT LEAST(a, b), GREATEST(a, b) FROM foo; LEAST(a, b) GREATEST(a, b) --- -- 1 4 2 3 2 3 > SELECT MIN(a), MAX(b) FROM foo; MIN(a) MAX(b) -- -- 1 4 After further research, I found that the only way to have a function with a variable number of arguments is to create N number of overloaded functions, e.g. CREATE FUNCTION least(int)... CREATE FUNCTION least(int, int)... CREATE FUNCTION least(int, int, int)... ...etc... That sucks, especially since the underlying languages support variable arguments that will scale to who-knows-where (see varargs in C, *args in Python, for starters). Not only that, but I'd have to create another N number of functions for different datatypes (int, float, date, etc.). In addition to adding the LEAST and GREATEST functions, the PostgreSQL developers should add the ability to create user-defined functions with a variable number of arguments. Cheers, -Stefan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LEAST and GREATEST functions?
It's good to hear this kind of discussion going on!
I solved my problem (for now) by creating a bunch of
overloaded LEAST and GREATEST functions, one for each
datatype. They only take two parameters, but that's
fine for what we're doing.
However, I ran into another, unrelated problem. I
created the LEAST and GREATEST functions as described
above, but when I tried to perform an update statement
comparing two timestamps, I ran into a problem:
UPDATE foo
SET my_timestamp_field =
LEAST(my_timestamp_field,
TO_TIMESTAMP('2003-07-01 12:34:56',
'-MM-DD HH24:MI:SS'))
WHERE ...
My LEAST function would not work because
my_timestamp_field has a datatype of "timestamp
without time zone", and the TO_TIMESTAMP(...) creates
a "timestamp *with* time zone". I could not find
anything in the documentation about this behavior. I
am running v7.3.2.
All help is appreciated, and please keep up the
discussion about the ability to create functions with
variable number of arguments (LEAST, GREATEST, etc.).
I could see the potential for wanting to write these
in the future.
Cheers,
-Stefan
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] PG equivalent to Sybase varbinary
Anybody know what the Postgresql equivalent to a Sybase varbinary data type is? I have a package that provides ddl to store a 40 byte/char? varbinary column in a table and it is failing against postrgresql. Thanks for any help and if more info is need to answer, please let me know. Bill Pfeiffer ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] PG equivalent to Sybase varbinary
Thanks for the response. I'll look into re-running the ddl using the bytea datatype the next time I perform the setup I'm using. (I used a Sybase SQL Anywhere db in the meantime to get me back on track with the task at hand). Thanks again, Bill "Richard Huxton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Monday 06 October 2003 15:40, Bill Pfeiffer wrote: > > Anybody know what the Postgresql equivalent to a Sybase varbinary data type > > is? I have a package that provides ddl to store a 40 byte/char? varbinary > > column in a table and it is failing against postrgresql. > > Sounds like "bytea" to me - or have you rejected that? > > -- > Richard Huxton > Archonet Ltd > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [PERFORM] Set-Returning Functions WAS: On the performance of
Josh Berkus wrote: Bill, First off: discussion moved to the SQL list, where it really belongs. True, it started out as [PERFORM], but is no longer. Well, I would have agreed with the uselessness, until this project. The "source of endless debugging" frightens me! Well, the last time I tried to use this capability was SQL Server 7. On that model, the problems I found were: 1) There was no good way to differentiate the recordsets returned; you had to keep careful track of what order they were in and put in "fillers" for recordsets that didn't get returned. 2) Most of the MS client technology (ODBC, ADO) was not prepared to handle multiple recordsets. I ended up hiring a C-based COM hacker to write me a custom replacement for ADO so that we could handle procedure results reliably. Well, they're already handling what MSSQL gives them in their prototype, so that's not my problem. This can be done with Set Returning Functions. The issue is that the call to the function requires special syntax, and the program calling the function must know what columns are going to be returned at the time of the call. Hmmm, is that clear or confusing? Clear as mud. In my case, my application simply doesn't care what row of what kind are returned. See, I'm writing the server end, and all said and done, it's really just glue (frighteningly thick glue, but glue nonetheless) To be clearer: You can create a Set Returning Function (SRF) without a clearly defined set of return columns, and just have it return "SETOF RECORD". However, when you *use* that function, the query you use to call it needs to have a clear idea of what columns will be returned, or you get no data. I don't understand at all. If I do "SELECT * FROM set_returning_function()" and all I'm going to do is iterate through the columns and rows, adding them to a two dimensional array that will be marshalled as a SOAP message, what about not knowing the nature of the return set can cause me to get no data? All of this is very hackneyed, as I'm sure you realize. Well, the way this project is being done tends to cause that. It was written in VB, it's being converted to VB.NET ... the original backend was MSSQL, now it's being converted to PostgreSQL with C glue to make PostgreSQL talk SOAP ... and all on the lowest budget possible. Overall, I'd say that the programming team you've been inflicted with don't like relational databases, or at least have no understanding of them. Quite possibly. It's amazing to me how well I've apparently self-taught myself relational databases. I've spoken with a lot of people who have had formal schooling in RDBMS who don't really understand it. And I've seen LOTs of applications that are written so badly that it's scarey. I mean, check out http://www.editavenue.com ... they wanted me to optimize their database to get rid of the deadlocks. I've been totally unable to make them understand that deadlocks are not caused by poor optimization, but by poor database programmers who don't really know how to code for multi-user. As a result, I've probably lost the work, but I'm probably better off without it. One of the things I love about working with open source databases is I don't see a lot of that. The people on these lists are almost always smarter than me, and I find that comforting ;) -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [PERFORM] Set-Returning Functions WAS: On the performance of
Josh Berkus wrote: Bill, I don't understand at all. If I do "SELECT * FROM set_returning_function()" and all I'm going to do is iterate through the columns and rows, adding them to a two dimensional array that will be marshalled as a SOAP message, what about not knowing the nature of the return set can cause me to get no data? Because that's not the syntax for a function that returns SETOF RECORD. The syntax is: SELECT * FROM set_returning_function(var1, var2) AS alias (col1 TYPE, col2 TYPE); That is, if the function definition does not contain a clear row structure, the query has to contain one. This does not apply to functions that are based on a table or composite type: CREATE FUNCTION RETURNS SETOF table1 ... CREATE FUNCTION RETURNS SETOF comp_type Can be called with: SELECT * FROM some_function(var1, var2) as alias; What this means is that you have to know the structure of the result set, either at function creation time or at function execution time. Yep. You're right, I hadn't looked at that, but I'm probably better off creating types and returning setof those types as much as possible. One of the things I love about working with open source databases is I don't see a lot of that. The people on these lists are almost always smarter than me, and I find that comforting ;) Flattery will get you everywhere. Really? I'll have to use it more often. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Getting the output of a function used in a where clause
HI, I’m a newbie so please bear with me. I have a function defined (got it from one of your threads… thanks Joe Conway) which calculates the distance between 2 zip code centeroids (in lat,long). This thing works great. However, I want to sort my results by distance without incurring the additional burden of executing the function twice. A simplified version of my current SQL (written in a perl cgi) that returns a set of zip codes within a given radius is: $sql = “SELECT zipcode from zipcodes where zipdist($lat1d,$lon1d,lat,long) <= $dist;”; What I want to write is something like: $sql = “SELECT zipcode, distance from zipcodes where distance <= $dist order by distance;”; But I don’t the magic SQL phrase to populate the distance variable using my nifty function. Do I need to create an output type for distance? Thanks in advance! Bill
Re: [SQL] Getting the output of a function used in a where clause
Boy I sure thought that would work... I received the following from postgres: ERROR: Attribute "distance" not found. Started looking into gist Looks complex. Any other ideas? -Original Message- From: PFC [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 1:51 AM To: Bill Lawrence; pgsql-sql@postgresql.org Subject: Re: [SQL] Getting the output of a function used in a where clause try: SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes where distance <= $dist;"; OR you could use a gist index with a geometric datatype to get it a lot faster. On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <[EMAIL PROTECTED]> wrote: > HI, > > I'm a newbie so please bear with me. I have a function defined (got it > from > one of your threads... thanks Joe Conway) which calculates the distance > between 2 zip code centeroids (in lat,long). This thing works great. > However, I want to sort my results by distance without incurring the > additional burden of executing the function twice. A simplified version > of > my current SQL (written in a perl cgi) that returns a set of zip codes > within a given radius is: > > > What I want to write is something like: > > $sql = "SELECT zipcode, distance from zipcodes where distance <= $dist > order > by distance;"; > > But I don't the magic SQL phrase to populate the distance variable using > my > nifty function. Do I need to create an output type for distance? > > Thanks in advance! > > Bill > > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Getting the output of a function used in a where clause
Thanks a bunch! Looks pretty step-by-step at the site for the link you sent. I'll give it a shot and see how it turns out. Thanks again for all your help! Bill -Original Message- From: PFC [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 12, 2005 1:03 AM To: Bill Lawrence Subject: Re: [SQL] Getting the output of a function used in a where clause > Boy I sure thought that would work... I received the following from > postgres: > > ERROR: Attribute "distance" not found. > > Started looking into gist Looks complex. > > Any other ideas? Complex ? CREATE TABLE stuff ( ... coords BOX NOT NULL, ... ) WITHOUT OIDS; CREATE INDEX cities_coords_idx ON geo.cities USING GIST ( coords gist_box_ops ); For some reason you must use BOX instead ot POINT to use the index. CREATE OR REPLACE FUNCTION boxpoint(FLOAT,FLOAT) RETURNS BOXRETURNS NULL ON NULL INPUTLANGUAGE plpgsqlAS $$ DECLARE p POINT; BEGIN p := point($1,$2); IF $1=0 AND $2=0 THEN RETURN NULL; END IF; RETURN box(p,p); END; $$; now use boxpoint(x,y) to select a box : INSERT INTO stuff (...,coords,...) VALUES (...,boxpoint(x,y),...) Now to get all the records whose coords are inside a box using the index : SELECT ... WHERE cords && '((xa,ya),(xb,yb))'::box for all the details look there : http://www.postgis.org/docs/ch04.html#id3530280 it's simple once you're into it. You'll need to install postgis. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Getting the output of a function used in a where clause
Thanks, Unfortunately, I think that solution requires the distance calculation to be executed twice for each record in the table. There are ~70K records in the table. Is the postgres query optimizer smart enough to only perform the calculation once? Bill -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 12, 2005 6:55 AM To: Bill Lawrence Cc: PFC; pgsql-sql@postgresql.org Subject: Re: [SQL] Getting the output of a function used in a where clause Why not just do: SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes where zipdist($lat1d,$lon1d,lat,long) <= $dist;"; On Mon, 2005-04-11 at 20:25, Bill Lawrence wrote: > Boy I sure thought that would work... I received the following from postgres: > > ERROR: Attribute "distance" not found. > > Started looking into gist Looks complex. > > Any other ideas? > > > -Original Message- > From: PFC [mailto:[EMAIL PROTECTED] > Sent: Monday, April 11, 2005 1:51 AM > To: Bill Lawrence; pgsql-sql@postgresql.org > Subject: Re: [SQL] Getting the output of a function used in a where clause > > > try: > > SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes > where distance <= $dist;"; > > OR you could use a gist index with a geometric datatype to get it a lot > faster. > > > On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <[EMAIL PROTECTED]> > wrote: > > > HI, > > > > I'm a newbie so please bear with me. I have a function defined (got it > > from > > one of your threads... thanks Joe Conway) which calculates the distance > > between 2 zip code centeroids (in lat,long). This thing works great. > > However, I want to sort my results by distance without incurring the > > additional burden of executing the function twice. A simplified version > > of > > my current SQL (written in a perl cgi) that returns a set of zip codes > > within a given radius is: > > > > > > What I want to write is something like: > > > > $sql = "SELECT zipcode, distance from zipcodes where distance <= $dist > > order > > by distance;"; > > > > But I don't the magic SQL phrase to populate the distance variable using > > my > > nifty function. Do I need to create an output type for distance? > > > > Thanks in advance! > > > > Bill > > > > > > > > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Getting the output of a function used in a where clause
Thanks Tom and Rod. There are indeed several additional conditions on the "real" query which prune the search space (I formulate a quick search box and filter on Lat/Lon's within the box). Since my user interface limits the search to a 30 mile radius, there are at most 81 results (in New York city, far fewer, for other regions of the US), so I've elected to post process the results in my script (calculating the distance for each returned record) and display only the closest 20 results (I intended to use a LIMIT clause combined with an ORDER BY in my SQL... LOL). Again, Thanks for all the great advice! -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, April 18, 2005 8:00 PM To: Rod Taylor Cc: Bill Lawrence; Scott Marlowe; PFC; pgsql-sql@postgresql.org Subject: Re: [SQL] Getting the output of a function used in a where clause Rod Taylor <[EMAIL PROTECTED]> writes: > You can force it with a subselect though: > SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) > as distance > from zipcodes) AS tab where distance <= $dist; The above will *not* stop zipdist from being run twice, because the planner will happily flatten the subquery into the outer query, resulting in the same situation of zipdist() being present twice in the text of the query. You could force the issue with SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist; since LIMIT/OFFSET clauses presently disable the flattening optimization. Keep in mind though that the OFFSET is an absolute optimization fence: it will result in the subquery being evaluated completely, even if there were outer conditions that might have avoided the need to look at some rows. For example, if the query is SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist AND some-other-conditions then not letting the some-other-conditions migrate down below the evaluation of zipdist could result in making the query be far slower, not faster, than you would get if you weren't trying to outsmart the planner. In general the system operates on the assumption that function calls are cheap relative to disk I/O. If that's not true for you, you're going to have some issues ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] reduce many loosely related rows down to one
Here is a boiled down example of a scenario which I am having a bit of difficulty solving. This is a catchall table where all the rows are related to the "id" but are entered by different unrelated processes that do not necessarily have access to the other data bits. CREATE TABLE test ( id INTEGER, rspid INTEGER, nspid INTEGER, cid INTEGER, iac BOOLEAN, newp SMALLINT, oldp SMALLINT, ppv NUMERIC(7,2), tppv NUMERIC(7,2) ); INSERT INTO test (id, rspid, nspid, cid, iac) VALUES (1,2,3,4,TRUE); INSERT INTO test (id, rspid, nspid, newp) VALUES (1,2,3,100); INSERT INTO test (id, rspid, nspid, oldp) VALUES (1,2,3,200); INSERT INTO test (id, rspid, nspid, tppv) VALUES (1,2,3,4100); INSERT INTO test (id, rspid, nspid, tppv) VALUES (1,2,3,3100); INSERT INTO test (id, rspid, nspid, ppv) VALUES (1,2,3,-100); INSERT INTO test (id, rspid, nspid, ppv) VALUES (1,2,3,250); INSERT INTO test (id, rspid, nspid, cid) VALUES (2,7,8,4); -- raw data now looks like this: select * from test; id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv +---+---+-+-+--+--+-+- 1 | 2 | 3 | 4 | t | | | | 1 | 2 | 3 | | | 100 | | | 1 | 2 | 3 | | | | 200 | | 1 | 2 | 3 | | | | | | 4100.00 1 | 2 | 3 | | | | | | 3100.00 1 | 2 | 3 | | | | | -100.00 | 1 | 2 | 3 | | | | | 250.00 | 2 | 7 | 8 | 4 | | | | | (8 rows) -- I want this result (where ppv and tppv are summed and the other distinct values are boiled down into one row) -- I want to avoid writing explicit UNIONs that will break if, say the "cid" was entered as a discreet row from the row containing "iac" -- in this example "rspid" and "nspid" are always the same for a given ID, however they could possibly be absent for a given row as well id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv +---+---+-+-+--+--+-+- 1 |2 | 3 | 4 | t | 100 | 200 | 150.00 | 7200.00 2 |7 | 8 | 4 | | | |0.00 |0.00 I have experimented with doing the aggregates as a CTE and then joining that to various incarnations of DISTINCT and DISTINCT ON, but those do not do what I want. Trying to find the right combination of terms to get an answer from Google has been unfruitful. Any ideas? Thank you for your consideration. Bill MacArthur -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] reduce many loosely related rows down to one
On 5/25/2013 7:57 AM, Marc Mamin wrote: Von: pgsql-sql-ow...@postgresql.org [pgsql-sql-ow...@postgresql.org]" im Auftrag von "Bill MacArthur [webmas...@dhs-club.com] Gesendet: Samstag, 25. Mai 2013 09:19 An: pgsql-sql@postgresql.org Betreff: [SQL] reduce many loosely related rows down to one Here is a boiled down example of a scenario which I am having a bit of difficulty solving. This is a catchall table where all the rows are related to the "id" but are entered by different unrelated processes that do not necessarily have access to the other data bits. -- raw data now looks like this: select * from test; id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv +---+---+-+-+--+--+-+- 1 | 2 | 3 | 4 | t | | | | 1 | 2 | 3 | | | 100 | | | 1 | 2 | 3 | | | | 200 | | 1 | 2 | 3 | | | | | | 4100.00 1 | 2 | 3 | | | | | | 3100.00 1 | 2 | 3 | | | | | -100.00 | 1 | 2 | 3 | | | | | 250.00 | 2 | 7 | 8 | 4 | | | | | (8 rows) -- I want this result (where ppv and tppv are summed and the other distinct values are boiled down into one row) -- I want to avoid writing explicit UNIONs that will break if, say the "cid" was entered as a discreet row from the row containing "iac" -- in this example "rspid" and "nspid" are always the same for a given ID, however they could possibly be absent for a given row as well id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv +---+---+-+-+--+--+-+- 1 |2 | 3 | 4 | t | 100 | 200 | 150.00 | 7200.00 2 |7 | 8 | 4 | | | |0.00 |0.00 I have experimented with doing the aggregates as a CTE and then joining that to various incarnations of DISTINCT and DISTINCT ON, but those do not do what I want. Trying to find the right combination of terms to get an answer from Google has been unfruitful. Hello, If I understand you well, you want to perform a group by whereas null values are coalesced to existing not null values. this seems to be logically not feasible. What should look the result like if your "raw" data are as following: id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv +---+---+-+-+--+--+-+- 1 | 2 | 3 | 4 | t | | | | 1 | 2 | 3 | 5 | t | | | | 1 | 2 | 3 | | | 100 | | | (to which cid should newp be summed to?) regards, Marc Mmain Ya, there is more to the picture than I described. Didn't want to bore with excessive detail. I was hoping that perhaps somebody would see the example and say "oh ya that can be solved with this obscure SQL implementation" :) I have resigned myself to using a few more CTEs with DISTINCTs and joining it all up to get the results I want. Thanks for the look anyway Marc. Your description of what I wanted was more accurate and concise than I had words for at the time of the night I originally posted this. Have a good one. Bill MacArthur -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] reduce many loosely related rows down to one
On 5/28/2013 11:04 AM, Torsten Grust wrote: On 25 May 2013, at 9:19, Bill MacArthur wrote (with possible deletions): [...] select * from test; id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv +---+---+-+-+--+--+-+- 1 | 2 | 3 | 4 | t | | | | 1 | 2 | 3 | | | 100 | | | 1 | 2 | 3 | | | | 200 | | 1 | 2 | 3 | | | | | | 4100.00 1 | 2 | 3 | | | | | | 3100.00 1 | 2 | 3 | | | | | -100.00 | 1 | 2 | 3 | | | | | 250.00 | 2 | 7 | 8 | 4 | | | | | (8 rows) -- I want this result (where ppv and tppv are summed and the other distinct values are boiled down into one row) -- I want to avoid writing explicit UNIONs that will break if, say the "cid" was entered as a discreet row from the row containing "iac" -- in this example "rspid" and "nspid" are always the same for a given ID, however they could possibly be absent for a given row as well id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv +---+---+-+-+--+--+-+- 1 |2 | 3 | 4 | t | 100 | 200 | 150.00 | 7200.00 2 |7 | 8 | 4 | | | |0.00 |0.00 One possible option could be SELECT id, (array_agg(rspid))[1] AS rspid,-- (1) (array_agg(nspid))[1] AS nspid, (array_agg(cid))[1] AS cid, bool_or(iac) AS iac, -- (2) max(newp) AS newp, -- (3) min(oldp) AS oldp, -- (4) coalesce(sum(ppv), 0) AS ppv, coalesce(sum(tppv),0) AS tppv FROM test GROUP BY id; This query computes the desired output for your example input. There's a caveat here: your description of the problem has been somewhat vague and it remains unclear how the query should respond if the functional dependency id -> rspid does not hold. In this case, the array_agg(rspid)[1] in the line marked (1) will pick one among many different(!) rspid values. I don't know your scenario well enough to judge whether this would be an acceptable behavior. Other possible behaviors have been implemented in the lines (2), (3), (4) where different aggregation functions are used to reduce sets to a single value (e.g., pick the largest/smallest of many values ...). Cheers, --Torsten Slick! Interesting usage scenarios for those aggregate functions array_agg and bool_or, one new to me and the other rarely used, and even for min and max which I never thought of using in this sense. I tried not be be overbearing with descriptive details hoping that somebody would look at the simplistic case and offer what might be considered an obscure way of implementing some of Postgres's handy features for an unusual problem. With a little tweaking for the exact nature of the environment, I am good to go. Thank you, Torsten! Bill MacArthur -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postmaster: init.d/start
Markus Wagner wrote: > > Hi Daniel, > > thank you for your reply. > > There are some unanswered questions. > Why does one have to consider multiple run levels? Doesn't run level 3 > (network + X) implicate run level 2 (network)? > So if I don't use XDM (or equivalent) and the system starts with a login > shell only, it would start run level 2 services. And if I use XDM and > the system starts using X it would also load run level 2 services? Not neccesarily. each run-level contains it's own list of services. They may, or may not, overlap. BTW, Runlevel3 is generally networking + NFS, NOT X. X is usually runlevel 5 (all depending on the variant). if you start a system not configured to go to X-RUnlevel, it will usually go to runlevel 3 (Don't recall ever seeing a default system go to runlevel two; Unix/Linux).
