Re: [SQL] Updating column to link one table to another
Mark Collette wrote: Now, I've added the infrequentId columns to both, as an optimization, so that I can quickly find the InfrequentTable entry for a given FrequentTable entry. I've done this because I want to speed up SELECTs. Any new INSERTs are working just fine. But, all my legacy entries, are not linked up yet. I need to know how I can UPDATE the FrequentTable rows, where their infrequentId is zero, to point at the last InfrequentTable entry whose timestamp ts is before its own timestamp ts. How do you distinguish between InfrequentTable entries when their timestamp is the same? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] I'm stumped
I can't figure out an efficient way to do this. Basically I had a typical 3-tier relationship: (Employee -> Department -> Division) However, at some point the need to move employees arose, but instead of changing the key in the emp table, we now have an over-ride table, so a history can be tracked. If I want to get the info for a particular employee, its a pretty simple deal, however, getting all the employees for a dept or division has become troublesome. A very simplified schema: divisions ( div_id, div_name ); departments ( dept_id, dept_name, div_id ); employees ( emp_id, emp_name, dept_id ); emp_dept ( emp_id, dept_id, active, changed_by, changed_when ); The original way that worked well: SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name FROM divisions v INNER JOIN departments d ON d.div_id = v.div_id INNER JOIN employees e ON e.dept_id = d.dept_id WHERE v.div_id = 123; What was initially tried: SELECT v.div_name, COALESCE(ed.dept_id, d.dept_id), e.emp_id, e.emp_name FROM divisions v INNER JOIN departments d ON d.div_id = v.div_id INNER JOIN employees e ON e.dept_id = d.dept_id LEFT JOIN emp_dept ed ON ed.emp_id = e.emp_id AND ed.active = true WHERE v.div_id = 123; This query is flawed, as it still always puts the employees in their original div, but reports the new dept. Which we didn't catch as a problem until emps were moved to depts in a different division. I tried creating a function: CREATE OR REPLACE FUNCTION get_empdept(int4) RETURNS int4 AS ' SELECT CASE WHEN ed.dept_id IS NOT NULL THEN ed.dept_id ELSE e.dept_id END FROM employees AS e LEFT JOIN emp_dept AS ed ON ed.emp_id = e.emp_id AND ed.active = true WHERE e.emp_id = $1 ' LANGUAGE SQL STABLE; And then tried: SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name FROM divisions v INNER JOIN departments d ON d.div_id = v.div_id INNER JOIN employees e ON get_empdept(e.emp_id) = d.dept_id WHERE v.div_id = 123; However since the function is not immutable (since it does a query), I can't create an index, and the join always does a seq scan. I also thought to create a view, but I don't believe Postgres supports indexed views. It was always using a seq scan too. The above examples are actually quite simplified, as several other tables get joined along the way, I'm not sure a UNION would work or not, how would it exclude the ones that match the dept_id in the emp table for those emps that match on the over-ride table? Any suggestions? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Date datatype
hi all, Ca anyone send me an example how to update a date datatype? for example update table set birthdate = "200-01-01" doesnt work. TIA Pablo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] diff databases
--- Begin Message --- EMS has a tool called EMS PostgreSQL Database Comparer. http://www.sqlmanager.net/products/postgresql/dbcomparer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, 19 December 2004 1:23 AM To: [EMAIL PROTECTED] Subject: [SQL] diff databases Hi, I'm looking for a tool which can compare structure of two databases and produce sql commands (ALTER, DROP, CREATE, etc. if needed) which could be used then to convert structure of one database to the other. I would like to use such a tool for postgresql databases especially. Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org <>--- End Message --- __ This email, including attachments, is intended only for the addressee and may be confidential, privileged and subject to copyright. If you have received this email in error, please advise the sender and delete it. If you are not the intended recipient of this email, you must not use, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Date datatype
Hi, I think the problem is the double quote you use. Try a single quote e.g.: update yourtable set birthdate='200-01-01' where ... This man must be a very old one... :) I tried it using timestamp not date, but I think it should work as well. bye, -- cs. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Pablo Digonzelli Sent: Tuesday, December 21, 2004 12:41 AM To: [EMAIL PROTECTED] Subject: [SQL] Date datatype hi all, Ca anyone send me an example how to update a date datatype? for example update table set birthdate = "200-01-01" doesnt work. TIA Pablo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.0 - Release Date: 2004.12.17. -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.0 - Release Date: 2004.12.17. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.0 - Release Date: 2004.12.17. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.0 - Release Date: 2004.12.17. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
