Re: [GENERAL] Update with ORDER BY and LIMIT
On Mon, Aug 08, 2011 at 10:20:18PM -0400, David Johnston wrote: 8.X in this context means 8 point something, but I can't recall which something. Could be 8.2, 8.3 or 8.4. Thus, in effect, asking those replying to restrict themselves to 8 series features, as opposed to 9 series features. There are a lot of features added between 8.0 and 8.4; WITH and WINDOW being two major ones, that just saying 8 is not helpful. In the future please take the time to issue a SELECT pg_version() before asking others to take time to help. It is for your own benefit and makes it easier for those wanting to help to give useful advice. It'd be great if select pg_version() worked, but PG doesn't recognize the function, when issued from the PG prompt. I had to go all the way back to aptitude to find out it's verson 8.3.1-1 running under Debian unstable. Paul -- Paul M. Foster http://noferblatz.com http://quillandmouse.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Update with ORDER BY and LIMIT
Two tables: 1) cust (one record each customer) contains: a) lpmtdt (date = last payment date) b) lpmtamt (numeric = last payment amount) c) custno (varchar(6) = customer string) 2) cashh (one record each income/cash transaction) contains a) custno (varchar(6) = customer string) b) rcptamt (numeric = amount of receipt) c) rcptdt (date = date of receipt) For whatever reason, the cust.lpmtamt and cust.lpmtdt are sometimes lacking values and shouldn't be. I want to update the customer table to update these values from the cashh table. I don't want to use an internal function. The PG version is 8.X. I can get the proper updating record with: SELECT rcptamt, rcptdt FROM cashh WHERE custno = 'COL1' ORDER BY rcptdt DESC LIMIT 1; (This gives me the latest cash receipt for this customer.) But I can't seem to merge this with an UPDATE cust ... query so the update happens in one step. Any help? Paul -- Paul M. Foster http://noferblatz.com http://quillandmouse.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update with ORDER BY and LIMIT
On Mon, Aug 08, 2011 at 05:34:14PM -0400, David Johnston wrote: For whatever reason, the cust.lpmtamt and cust.lpmtdt are sometimes lacking values and shouldn't be. I want to update the customer table to update these values from the cashh table. I don't want to use an internal function. The PG version is 8.X. -- No such version. All PostgreSQL released versions use the numbers 0-9 and periods only; no letters. 8.X in this context means 8 point something, but I can't recall which something. Could be 8.2, 8.3 or 8.4. Thus, in effect, asking those replying to restrict themselves to 8 series features, as opposed to 9 series features. The general form for an UPDATE is: UPDATE table SET field = table2.field FROM table2 WHERE table.field = table2.field; SO: UPDATE customer SET lpmtdt = rcpt.rcptdt, lpmtamt = rcpt.rcptamt FROM (SELECT custno, rcptdt, rcptamt FROM cashh WHERE ... ORDER BY ... LIMIT 1) rcpt WHERE customer.custno = rcpt.custno AND customer.lptmdt IS NULL OR customer.lpmtamt IS NULL NOT TESTED Works well enough as a starting point. Thanks. Paul -- Paul M. Foster http://noferblatz.com http://quillandmouse.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updates: all or partial records
Scenario: You have to update a record. One or more fields are unchanged from the original record being altered. So you have two options: 1) Include those fields in your UPDATE statement, even though they are unchanged; 2) Omit unchanged fields from the UPDATE statement. My first inclination is to omit unchanged fields. However, I have the idea that PG simply marks the existing record to be dropped, and generates a whole new row by copying unspecified fields from the original record. My question is, which is more efficient? Performance-wise, does it matter whether unchanged fields are included or omitted on UPDATE statements? Paul -- Paul M. Foster -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Re[GENERAL] sources for learning PostgreSQL
On Tue, Sep 22, 2009 at 10:43:09AM -0700, dan06 wrote: Hi, I've used mysql for sometime now, and I'd like to broaden my db knowledge/experience to include postgresql. Can anyone recommend any books or other resources that could help me along? Thanks. The PostgreSQL website has excellent documentation (second only to php.net). If you already know MySQL, then you're 2/3 of the way there. You just need to get used to the quirks of PostgreSQL. For books, I have two: Beginning Databases with PostgreSQL 2/e by Matthew Stones PostgreSQL 2/e by Douglas Douglas These are better than the original PostgreSQL: Introduction and Concepts by Momjian (sorry, Bruce). That book struck me as mostly a transcription of the existing documentation at the time. It was very helpful when there were no other PostgreSQL books out there. But you may not even need the books, if you peruse the website. Paul -- Paul M. Foster -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What kind of JOIN, if any?
Folks: I can't find a way to do this purely with SQL. Any help would be appreciated. Table 1: urls id | url -- 1 | alfa 2 | bravo 3 | charlie 4 | delta Table 2: access userid | url_id --- paulf | 1 paulf | 2 nancyf | 2 nancyf | 3 The access table is related to the url table via url_id = id. Here's what I want as a result of a query: I want all the records of the url table, one row for each record, plus the userid field that goes with it, for a specified user (paulf), with NULLs as needed, like this: userid | url - paulf | alfa paulf | bravo | charlie | delta I can do *part* of this with various JOINs, but the moment I specify userid = 'paulf', I don't get the rows with NULLs. Again, any help would be appreciated. Paul -- Paul M. Foster -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What kind of JOIN, if any?
On Thu, Sep 17, 2009 at 04:20:57PM +0100, Sam Mason wrote: On Thu, Sep 17, 2009 at 10:29:11AM -0400, Paul M Foster wrote: I want all the records of the url table, one row for each record, plus the userid field that goes with it, for a specified user (paulf), with NULLs as needed Maybe something like this? SELECT a.userid, u.url FROM urls u LEFT JOIN access a ON u.id = a.url_id AND a.userid = 'paulf'; I can do *part* of this with various JOINs, but the moment I specify userid = 'paulf', I don't get the rows with NULLs. I guess you were putting userid = 'paulf' into the WHERE clause, that's the wrong place. It needs to be up in the ON clause. You da man. That is the answer; it worked. Thanks very much. (BTW, on your website, the link from Simple Report Generator to http://samason.me.uk/~sam/reportgen/ is broken.) Paul -- Paul M. Foster -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What kind of JOIN, if any?
On Thu, Sep 17, 2009 at 11:23:12AM -0400, Mark Styles wrote: On Thu, Sep 17, 2009 at 10:29:11AM -0400, Paul M Foster wrote: I can't find a way to do this purely with SQL. Any help would be appreciated. Table 1: urls id | url -- 1 | alfa 2 | bravo 3 | charlie 4 | delta Table 2: access userid | url_id --- paulf | 1 paulf | 2 nancyf | 2 nancyf | 3 The access table is related to the url table via url_id = id. Here's what I want as a result of a query: I want all the records of the url table, one row for each record, plus the userid field that goes with it, for a specified user (paulf), with NULLs as needed, like this: userid | url - paulf | alfa paulf | bravo | charlie | delta I can do *part* of this with various JOINs, but the moment I specify userid = 'paulf', I don't get the rows with NULLs. SELECT userid, url FROM urls LEFT OUTER JOIN (select * from access where userid = 'paulf') AS access ON access.url_id = urls.id; Another good suggestion. Thanks. Paul -- Paul M. Foster -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Ajax/PostgreSQL
I'm doing some massive (internal company) applications using PHP, which query extensive PostgreSQL tables. This is fine, but obviously it often requires multiple web pages to get something done. Supposedly, AJAX promises to make web pages more interactive. But from what I understand, I'd have to have bindings from Javascript into PostgreSQL to make this work. Here's an example: The user wants to enter a bill (accounts payable) into the system. He first has to pick a vendor. Normally, this would entail a PHP page that generates a PostgreSQL query. The user would then get a second page with various vendor information (like number of due days for that vendor), and various other payable info. But wouldn't it be nice to have vendor information filled in on the original page, directly after the user picks a vendor? Theoretically, AJAX might allow something like this. But from what I can see, it would require PostgreSQL bindings in Javascript, and some way to pass the data back so that PHP could use it. Is this even possible? Is it being worked on? Is there a different solution I don't know about? I can see where Javascript can alter the look of a page, but I can't work out how it would allow interactive use of a PostgreSQL table. -- Paul M. Foster ---(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: [GENERAL] Any *real* reason to choose a natural, composite PK
[EMAIL PROTECTED] wrote: In the book Practical Issues in Database Management, Fabian Pascal notes three reasons for choosing one PK over another - familiarity, stability, and simplicity. He notes further that those influenced by OO db design tend to use simple, surrogate keys for all PKs in all databases they design; that this is not *precluded* by relational theory, but that there's somehow something illicit about it. Today at least, and why I ask, I think it's a good rule of thumb to create surrogate keys for almost all tables. Familiarity seems like a spurious concern, and a poor tradeoff against both stability (guaranteeing you are uniquely identifying rows) and simplicity (with queries, and others intuiting your design). What am I missing? Why use a composite key *ever* aside from familiarity? Could someone give a real-world example where familiarity is a compelling reason to choose a composite PK, and trumps stability and simplicity? Stability seems to be the single-most important factor to consider. If the database can't uniquely identify a row, what's the point? Choosing a surrogate key guarantees stability. Surrogate keys have the advantage of performance. A composite key composed of four fields of 50 characters each could create performance problems. But a unique serial avoids this problem. Of course, one of the drawbacks of surrogate keys is that, if you design your table so that those four fields together are unique, once you index the table on the serial key, there's nothing to guarantee your four fields will *stay* unique. Another reason for surrogate keys is that there may not be any *meaningful* combination of fields to make up a unique key. That is, although you may indeed have four unique fields for your table, together they really have no meaning. For example, I have a log table in one of my applications which adds several records per job. Yes, I could make the key jobno + sequence_number. But why not just let the primary key be a serial? Certainly simpler. Here's another reason for using surrogate keys (real world). You have a PHP application where you're passing GET parameters to the next PHP script in the chain. If you're going to query a table with one of these long composite keys, you've got to go through the operation of concatenating all these values together in order to pass it via the GET parameter. A real pain. It's far easier to retrieve the single serial key field and pass that. Overall, I'd say this is the problem with guys who write books full of theory, expecting students to buy their pronouncements. Yes, some of these guys have real world experience. But really, you should hang out with some people who actually do this for a living after you read the theory guys. The theory's keen and all that. But it's no substitute for getting your hands dirty in the real world. Things are often very different out there. Give me a mechanical engineer any day over an architect who's never been near a building. (I don't know much about Frank Lloyd Wright's education, but he built some beautiful buildings which didn't stand up well to the elements.) -- Paul M. Foster ---(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: [GENERAL] Re: PostgreSQL vs Oracle vs DB2 vs MySQL - Which should I use?
On Fri, Feb 16, 2001 at 09:36:23AM -0500, Bruce Momjian wrote: snip Funny you should mention PHP. I talked to Rasmus about the email volume when I first met him in the fall. He said the volume of email is so great that just reading the subject lines takes a long time. We aren't there yet, but we are heading in that direction. One thing some of us have done are to take full-time jobs with PostgreSQL so we can handle the increased load. Second, I have started to skip emails with subjects that contain obvious questions, relying on other users to answer these. When several people post on the easy question, I start to suspect there is some issue there and start reading. This could eventually be handled the way they do with Usenet lists. When the volume finally gets too high, split the lists into more specialized sub-lists. (Though obviously you want to continue to improve the docs as well.) The busiest list I was ever on was the debian-help list. This one might be even busier. Paul
Re: [GENERAL] PostgreSQL vs Oracle vs DB2 vs MySQL - Which should I use?
On Tue, Feb 13, 2001 at 11:55:18AM +1300, Christopher Sawtell wrote: snip It is supported by a very competent team of developers who are not only determined to stay at the front of open source database race, but also quite patently give the impression that they actually _care_ about their users' problems. Let me echo this. I have _never_ been on a list where so many of the developers of a product were subscribed as well. And these folks provide _very_ considerate and clear assistance. Hats off to them. Paul
Re: [GENERAL] Importing a Database
On Sat, Feb 10, 2001 at 11:16:45AM -0500, No Name wrote: If i have a dbf database, how can I import it into PostgreSQL? Thanks I have a program that does this. I've set up a project at SourceForge for it, but I'm still fighting with them over CVS access etc. If you're interested, I can email you a copy of the tarball. Paul
Re: [GENERAL] Data entry screen building utilities
On Mon, Jan 22, 2001 at 03:14:36PM -0800, Tim Barnard wrote: Rather than writing PostgreSQL data entry screens from scratch using ncurses and cdk, is anyone aware of any data entry screen building utilities useful for character-based screens, as opposed to GUI utilities? I had never heard of cdk, but I found and downloaded it, since I'm doing similar console-type work. I haven't looked at it extensively, but is there a reason why you wouldn't want to use it? Paul
[GENERAL] Script Location
When I dump a database (creating a script from the pg_dump command), I can't just put it in any old directory and point psql at it. I have to put it in the /var/lib/pgsql subdirs before postgresql will accept it. Is this right? Am I doing something wrong? Is this a bug^H^H^Hfeature? Paul M. Foster
