Re: [SQL] How to find entries missing in 2nd table?
Hi, what about SELECT controller_id FROM control WHERE controller_id NOT IN (SELECT DISTINCT controller_id FROM datapack); ? Regards Peter > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von > Richard Broersma Jr > Gesendet: Dienstag, 11. Juli 2006 19:04 > An: SQL Postgresql List > Betreff: Re: [SQL] How to find entries missing in 2nd table? > > > > --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > > > >> I need to get all entries from the table control that are not > > > >> listed in datapack. > > > > > > > > SELECT C.CONTROLLER_ID > > > > > > > > FROM CONTROL AS C > > > > LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = > D.CONTROLLER_ID) > > > > > > > > WHERE D.CONTROLLER_ID IS NULL; > > > > > > > > > > > > > Or > > > (SELECT controller_id FROM control) > > > EXCEPT > > > > Good point! But don't forget to include the list. :-) > > > > Regards, > > > > Richard Broersma Jr. > > > (SELECT controller_id FROM datapack) ? > > > > > > -- > > > Milen A. Radev > > > > > > > > > > ---(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 > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Unexpected SQL error for UPDATE
On Jul 11, 2006, at 20:47 , aurora wrote: There is no simple SQL to alter the type. So I use a series of SQLs to create a new column, copy the data over and then replace the old column with the new. What version of PostgreSQL are you running? In 8.0 and later you can use ALTER TABLE ALTER TYPE. http://www.postgresql.org/docs/current/interactive/sql-altertable.html Something like this (untested) should work in your case: ALTER TABLE users ALTER email_addresses TYPE text; ALTER TABLE users ADD email_address_text text UNIQUE; UPDATE users set email_address_text=email_address; ALTER TABLE users DROP email_address; ALTER TABLE users RENAME email_address_text TO email_address; ALTER TABLE users ALTER email_address SET not null; This looks like it should work, so perhaps someone else has an idea as to why you're receiving the error. Is the error reproducible? If you can put together a reproducible test case, it may help someone else be able to figure out what's going on. Michael Glaesemann grzm seespotcode net ---(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] Unexpected SQL error for UPDATE
mydb=# UPDATE users set email_address_text=email_address; ERROR: invalid page header in block 6776 of relation "users_email_address_text_key" users_email_address_text_key sounds a lot like an index... is it an index? if so, what happenned if you REINDEX the table or even DROP and CREATE the index again? it certainly sounds to an index corruption. -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Avoiding RI failure with INSERT ... SELECT FROM
Hello! I got an unexpected SQL failure last night, and want to see how
to prevent it in the future. This is with 8.1.3.
I have a couple of insert statements that run in a transaction block,
initializing rows that will be used the next day:
INSERT into item_hit_log
SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0
FROM items where item_state = 'available';
INSERT into item_view_log
SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0
FROM items where item_state = 'available';
The "items" table has a few hundred thousand rows in it, so this takes a
bit a run.
The error we got last night was:
ERROR: insert or update on table "item_hit_log" violates foreign key
constraint "item_id_fk"
DETAIL: Key (item_id)=(451226) is not present in table "items".
Re-running the transaction block a few minutes later worked.
What's an alternate design that avoids this possible error?
Thanks!
Mark
---(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] Unexpected SQL error for UPDATE
Yes that helped! reindex index users_email_address_text_key; UPDATE users set email_address_text=email_address;It kind of works now. Or at least it does not result in error. The UPDATE query is running for more than an hour and still hasn't return. This is inline with what we have seen before that coping 1 million fields seems to be excessively slow. About the index, it is created implicitly because of the UNIQUE constraint. At this point right after the column is added, they are all null. wyOn 7/12/06, Jaime Casanova <[EMAIL PROTECTED]> wrote: users_email_address_text_key sounds a lot like an index... is it anindex? if so, what happenned if you REINDEX the table or even DROP andCREATE the index again? it certainly sounds to an index corruption.-- regards,Jaime Casanova
Re: [SQL] How to find entries missing in 2nd table?
On Wed, 2006-07-12 at 03:06, Exner, Peter wrote: > Hi, > > what about > > SELECT controller_id FROM control > WHERE controller_id NOT IN > (SELECT DISTINCT controller_id FROM datapack); That one works too, but it's generally not as fast as the left join / is null query on large tables. Give the two a try on a test set and you should see the speed difference. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Avoiding RI failure with INSERT ... SELECT FROM
On 7/12/06, Mark Stosberg <[EMAIL PROTECTED]> wrote:
Hello! I got an unexpected SQL failure last night, and want to see howto prevent it in the future. This is with 8.1.3.I have a couple of insert statements that run in a transaction block,initializing rows that will be used the next day:
INSERT into item_hit_logSELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0 FROM items where item_state = 'available';INSERT into item_view_log SELECT item_id, CURRENT_DATE + CAST('1 day' AS interval), 0
FROM items where item_state = 'available';I would recommend you list your values in your insert statements:INSERT into item_hit_log (item_id_fk, )SELECT
That makes it less prone to problems in the future (like if the column orders change) and makes it easier for others to understand and help you with.
The "items" table has a few hundred thousand rows in it, so this takes abit a run.The error we got last night was: ERROR: insert or update on table "item_hit_log" violates foreign key
constraint "item_id_fk" DETAIL: Key (item_id)=(451226) is not present in table "items".Re-running the transaction block a few minutes later worked.What's an alternate design that avoids this possible error?
Does the items table allow deletes? If so, your insert may be attempting to do an insert for an item_id that was deleted after the select and 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==
Re: [SQL] SQL (Venn diagram type of logic)
On 7/11/06, Vinnie Ma <[EMAIL PROTECTED]> wrote: Hello Aaron,Thank you for the help. My apologies for the directemail but i couldn't figure out how to reply to thethread on the postresql site. Just doing a reply to all should send it to the mailing list but the from address must match the email address you registered on the list. Once i get the purchases seperated by categories, Iplan to track the month on month status of each account.For example, in may 2006, based on her previouspurchaes, customer1 was in category A but in june2006, she bought something in category B. This wouldmake her in bucket 2.bucket 1: A bucket 2: A&Bbucket 3: A&Cbucket 4: Bbucket 5: B&Cbucket 6: Cbucket 7: A&B&C(these are the combinations i was thinking about wheni had the venn diagram in my head.) To follow with the example above:In May, she would be counted as part of bucket 1 andher total spending (life to may) will be part ofrunning total for bucket 1In June, she would be counted as part of bucket 2 and her total spending (life to june) will be part ofrunning total for bucket 2In the end, we will have count(customers),sum(all_purchases) for each of the 7 buckets for eachmonth. From a business perpective, we hope this exercise will show the general migration patterns ofcustomers from one bucket to another over time.I figured out a way to do it but it is no where nearefficient.i added three boolean fields to the account table to indicate catA, catB, and catC. i update the table 3times for each of the categories, turning on theboolean fields where applicable.then i select the count, and sum fields from an innerjoin on account and purchase tables for each of the 7 buckets. it works, but i would manually do it foreach month.any thoughts would be most appreciated. thanks andplease have a great day.What I have done for situations like this is to first create a report schema. Then I have a daily process run that updates reporting tables every night. These reporting tables keep full history and we only update the latest information. The main reason for doing it this way is to:Remove the need to worry about performance of the query to build the report - it is done once a day during low activity on the serverSegment permissions so people have rights to run reports but not dig through the main database (the tech savy analysts tend to write bad queries 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!== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ==
Re: [SQL] Can function results be used in WHERE?
On 7/11/06, Bryce Nesbitt <[EMAIL PROTECTED]> wrote: Tom Lane wrote:> But as far as the underlying misconception goes, you seem to think that> "4" in the WHERE clause might somehow be taken as referring to the> fourth SELECT result column (why you don't think that the "1" would > likewise refer to the first result column isn't clear). This is not so.> "4" means the numeric value four. There is a special case in ORDER BY> and GROUP BY that an argument consisting of a simple integer literal > constant will be taken as a reference to an output column. This is an> ugly kluge IMHO...Yeah, it was a longshot. I only tried it because the column label didNOT work, and I had some gut reaction to repeating the same function twice: As I mentioned before: the only difference between HAVING and WHERE is that WHERE occurs before a GROUP BY and HAVING occurs after. stage=# SELECT pod_code, lat, lon,calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_podWHERE dist < 1 ORDER BY dist desc limit 10;ERROR: column "dist" does not exist You cannot use an alias from the select column list in your WHERE clause because the where is the criteria done BEFORE your columns are pulled out. This is especially noticable when doing a GROUP BY since the WHERE is done before the GROUP BY and the returned column values are gathered after the GROUP BY. If you want to use an alias, do a subquery and then put your where in the outer query. I believe the ORDER BY is done last but that may be dependent on the database implementation. It does make sense to think of ORDER BY to be done last though. For that reason it can use the alias. stage=# SELECT pod_code, lat, lon,calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_podWHERE calculate_distance(lat,lon, 37.789629,-122.422082) < 1 ORDER BYdist desc limit 5; pod_code |lat| lon | dist --+---+-+---5 | 37.792022 | -122.404247 | 0.988808031847045 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==
[SQL] Logging in Stored Procedure
OK, maybe a stupid simple question but, how do you add logging to a stored procedure? My procedure is running but the results I get are not complete and I need to see better what is going wrong. The tool I use has a debugger but the debugger is barfing at a particular line that, when run without 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 ==
Re: [SQL] Logging in Stored Procedure
Well, perhaps the NOTICE functionality, e.g. RAISE NOTICE ''report id of % for date %'', rpt_rec.report_id, rpt_rec.report_s_date; This will show on the terminal if running from a console, and also writes to the log, IIRC. See the documentation for your version for details. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Aaron Bono Sent: Wed 7/12/2006 4:28 PM To: Postgres SQL language list Cc: Subject:[SQL] Logging in Stored Procedure OK, maybe a stupid simple question but, how do you add logging to a stored procedure? My procedure is running but the results I get are not complete and I need to see better what is going wrong. The tool I use has a debugger but the debugger is barfing at a particular line that, when run without 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 == !DSPAM:44b58e5178651804284693! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Unexpected SQL error for UPDATE
On 7/12/06, aurora <[EMAIL PROTECTED]> wrote: Yes that helped! reindex index users_email_address_text_key; what version of postgres is this? there have been some bug fixes that involved indexes on text columns. wich locale are you using? -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
