Re: [SQL] MAX() of 0 records.
> UPDATE entry_stats > SET entry_maxprice=MAX(item_price) > FROM item > WHERE item.item_entry_id=NEW.item_entry_id > AND item.item_live = 't'; Try COALESCE(MAX(item_price),0) Christopher J.D. Currie Computer Technician Dalhousie: DalTech - CTE _ Lord, deliver me from the man who never makes a mistake, and also from the man who makes the same mistake twice. -William James Mayo
[SQL] Altnerate Column Return Names
If I do a select * from table1, table2 where table1.id = table2.id I normally get two columns returned with a column identifier of id. Such as id | name | data | id | name | data +--+--++--+-- | | || | Is there anyway to get the result to return table1.id | table1.name | table1.data | table2.id | ,,, ---+-+-+---+- ... It seems awkward, however the only way I've been able to work around this is to use column names prefixed with the table name. One of my projects is using PHP and I"m sure that the hash table they create to select rows is determined from the column names returned from a query. Thanks, Thomas ... I feel like I ask some weird questions ... - - Thomas Swan - - Graduate Student - Computer Science - The University of Mississippi - - "People can be sorted into two fundamental groups, - those that divide people into two groups and - those that don't."
Re: [SQL] MAX() of 0 records.
Paul McGarry <[EMAIL PROTECTED]> writes: > However there will be situations where there are no records for > a given item_entry_id with item_live='t'. Currently when I try > do update/insert a record so that this occurs I get the following > error 'ERROR: ExecutePlan: (junk) 'ctid' is NULL!' and the > insert/update attempt is rolled back. This seems like a backend bug to me, but being an overworked hacker I'm too lazy to try to reconstruct the scenario from your sketch. Could I trouble you to submit a formal bug report with a specific, hopefully compact script that triggers the problem? > Is there a good way of going about this or should I just be wrapping > the whole thing up in an Until I've isolated the bug I don't want to speculate about whether it'll be reasonable to try to back-patch a fix into 7.0.*. Usually we don't risk back-patching complex fixes into stable releases, but the fix might be simple once we know the cause. regards, tom lane PS: I trust you're using 7.0.* ?
Re: [SQL] MAX() of 0 records.
> Hello, > > I wish to perform the following query in a plsql function upon an > update/insert/delete trigger: > > UPDATE entry_stats > SET entry_maxprice=MAX(item_price) > FROM item > WHERE item.item_entry_id=NEW.item_entry_id > AND item.item_live = 't'; > > However there will be situations where there are no records for > a given item_entry_id with item_live='t'. Currently when I try > do update/insert a record so that this occurs I get the following > error 'ERROR: ExecutePlan: (junk) 'ctid' is NULL!' and the > insert/update attempt is rolled back. > > In this scenario I want entry_stats.entry_maxprice to be set to zero > (which is also the default value for that column if it's any help). > > Is there a good way of going about this or should I just be wrapping > the whole thing up in an > > IF (COUNT(*) FROM item >WHERE item.item_entry_id=NEW.item_entry_id > AND item.item_live = 't')>0 > THEN > UPDATE ... =MAX() ... > ELSE > UPDATE SET ... =0 ... > END IF > > ? > > Thanks > > -- > Paul McGarrymailto:[EMAIL PROTECTED] > Systems Integrator http://www.opentec.com.au > Opentec Pty Ltd http://www.iebusiness.com.au > 6 Lyon Park RoadPhone: (02) 9878 1744 > North Ryde NSW 2113 Fax: (02) 9878 1755 I once had the same problem with an int4 column and solved it by using the function below : CREATE FUNCTION "nulliszero" (int4 ) RETURNS int4 AS 'SELECT 0 WHERE $1 IS NULL UNION SELECT $1 WHERE $1 IS NOT NULL' LANGUAGE 'SQL'; Your request then would look like : (...) SET entry_maxprice=nulliszero(MAX(item_price)); (...) -Benoit
[SQL] Search for underscore w/ LIKE
How do I use LIKE to search for strings with an underscore? The
documentation (well, Bruce's book) says to use 2 underscores (__) but it
doesn't work. For example:
create table liketest (
somestr varchar(50)
);
insert into liketest values ('foo_bar');
insert into liketest values ('foobar');
insert into liketest values ('snackbar');
insert into liketest values ('crow_bar');
-- I want to select strings with "_bar"
select * from liketest where somestr like '%_bar';
somestr
--
foo_bar
foobar
snackbar
crow_bar
(4 rows)
-- Using double underscore doesn't work either
select * from liketest where somestr like '%__bar';
somestr
--
foo_bar
foobar
snackbar
crow_bar
(4 rows)
-- Escaping w/ backslash doesn't work
select * from liketest where somestr like '%\_bar';
somestr
--
foo_bar
foobar
snackbar
crow_bar
(4 rows)
Brian
--
Brian Baquiran <[EMAIL PROTECTED]>
http://www.baquiran.com/ AIM: bbaquiran
Work: (632)718 Home: (632)9227123
Re: [SQL] confused by select.
John wrote: > Hello. I'm trying to do a select here that i have looked at from many > angles and cannot find a solution too. My main problem, (i believe) is > that it is trying to create a many to many relationship. I would be > grateful if anyone knew a way around this. > > Here's my predicamint. > I have a database for sales orders. > An inventory table. > And > A history table. > > Inventory: > Create t1 (sku char(4), type char(1)); > History: > Create t2 (id char(6), items text); > > [There are more fields, but this is all that matters for this query] > > I would like to get the id's where the customer has purchased an item of a > specific type. > > Problem A: most people order more than one item at a time. > So the 'items' field is a colon delimitted text field containing the >skus of the purchased items. > > Problem B: there are many skus of each type. >as are there many purchases. > > What would the proper select be? > > create view v1 (select sku from t1 where type ='K'); >will get me all the skus of one type but i don't know where to go > from there. And it feels as if i've exhausted all options. > > i've been working around: > select id from t2 where items like sku; > and no matter what i use in the where clause (regex, like, or wildcards). > i get back an error or a zero. > and there are no other related fields in the mentioned tables. > > is there a way to step through the sku field item by item without leaving > postgres (i.e. resorting to scripting)? > > I have also tried different fieldtypes for the 'items' field. > But they all give me problems too. > The array works much the same way as the : delimitted field i have does. > Except you have less operators that work with it. > And to break it up into separate items fields. (item1, item2, item3, > etc.) is a waste, seeing as the average order is 2.? but there are many > orders with hundreds of items. > > Sorry for the long winded explanation. > But I figured, that the more imformation i gave, the more someone may be > able to help. > > Thanks in advance. > .jtp usually many-to-many relationships are handled by a third table, like this: create table items (item_id,...) create table customers (customer_id, ...) create table orders (customer_id, item_id, quantity_orderered) Hoping it may help Patrick JACQUOT
Re: [SQL] MAX() of 0 records.
Hi Benoit, > I once had the same problem with an int4 column and solved it by > using the function below : > > CREATE FUNCTION "nulliszero" (int4 ) > RETURNS int4 AS > 'SELECT 0 WHERE $1 IS NULL > UNION SELECT $1 WHERE $1 IS NOT NULL' LANGUAGE 'SQL'; > > Your request then would look like : > (...) > SET entry_maxprice=nulliszero(MAX(item_price)); > (...) Thanks, I'm afraid that doesn't help in this context (see attached sql). I'm sure it will come in handy some time though. The problem isn't that MAX(item_price) returns null, it's that it causes an error. From what I have distilled from the bugs/hackers list where Tom has kindly written a lengthy response/discussion of the problem it seems that in this situation (when no rows match the where condition) the MAX (or min or count or any aggregate function?) would cause an implicit grouping to occur around a null field which the backend doesn't like. I'd read Tom's post if you are interested though, it probably makes more sense! -- Paul McGarrymailto:[EMAIL PROTECTED] Systems Integrator http://www.opentec.com.au Opentec Pty Ltd http://www.iebusiness.com.au 6 Lyon Park RoadPhone: (02) 9878 1744 North Ryde NSW 2113 Fax: (02) 9878 1755 benoit.sql
[SQL] Ancient postgres+EXCEPT
Lo. I have in my local machine quite new POstgresql
[PostgreSQL 6.5.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66]
but the machine, I am writing scripts for has some ancient postgres(btw,
how to see the version on psql, if its not shown at the start? ).
I made a query
SELECT ajaperiood.isik_id,struktuuriyksus,ametikood,tase,eesnimi_perenimi
FROM ajaperiood,ametikoht,isik,tootaja
WHERE perioodi_tyyp='tooleping'
AND
(
algus<=timestamp(date_trunc('month', timestamp 'today')-interval '1 day')
OR
algus is NULL
)
AND
(
lopp>=timestamp(date_trunc('month', timestamp 'today')-interval '1 day')
OR
lopp is NULL
)
AND
tootaja.isik_id=ajaperiood.isik_id
AND
ametikoht.id=tootaja.ametikoht_id
AND
ametikoht.ametikood='10'
EXCEPT
SELECT ajaperiood.isik_id,struktuuriyksus,ametikood,tase,eesnimi_perenimi;
and now I have to put it in such state, that doesn't have except, but does
the same thing. I guess something twice as big but with ANDs,ORs, or
smthng like this :(. But I don't have a clue.
As a comments, I can offer you this: I have four tables-
job,workers,persons and timeintervals. Job is connected through workers
with persons(that is the table that binds persons and jobs), and every
person can have multiple time intervals such as 'vacation','contract',
'stopped contract','unpaid vacation' ...
What I need is to get the list of workers, that have contract, but aren't
on vacation etc. It would be nice to get them with one query, but if its
impossible, than I have to code it the hard way *yawn*.
Antti
P.S. Sorry for not translating the field and table names, but I have a
mighty Sandman around me.
[SQL] order by accents?
hi, if I do a query like this one : SELECT name from medias ORDER BY name name ÉCCC 6 rows Why the record : ÉCCC is at the end? HOW can I fix this? Thank you Patrick
Re: [SQL] Search for underscore w/ LIKE
[EMAIL PROTECTED] writes: > How do I use LIKE to search for strings with an underscore? The > documentation (well, Bruce's book) says to use 2 underscores (__) but it > doesn't work. If Bruce's book says that, I hope it's not too late for him to change it ;-) The correct way is to escape the underscore with a backslash. You actually have to write two backslashes in your query: select * from foo where bar like '%\\_baz' The first backslash quotes the second one for the query parser, so that what ends up inside the system is %\_baz, and then the LIKE function knows what to do with that. Similarly, '\\%' would be the way to match a literal %. You can actually backslash-quote any single character this way in LIKE, but % and _ are the only ones where it makes a difference. (In the regexp-matching operators there are many more special characters and so many more times when you need the backslash trick.) regards, tom lane
[SQL] Get: Month & Month-1 & Month+1
Hi, E.g. : The user/customer writes December, and I want to give back the plants flowering in November + December + January. (A circular situation, a 'season' covering more the one calender year in two cases ) The flowering field should be marked montly - in some convenient way: March-May , April-August etc - but which way. How could this job be done. -- Regards, Ebbe Poulsen, epa @post.cybercity.dk
Re: [SQL] Search for underscore w/ LIKE
I wrote: > Similarly, '\\%' would be the way to match a literal %. You can > actually backslash-quote any single character this way in LIKE, > but % and _ are the only ones where it makes a difference. Er, check that. Backslash itself also needs quoting in LIKE. Exercise for the student: if you need to match a literal backslash in a LIKE pattern, how many backslashes do you have to write in your query? regards, tom lane
Re: [SQL] Search for underscore w/ LIKE
> I wrote: > > Similarly, '\\%' would be the way to match a literal %. You can > > actually backslash-quote any single character this way in LIKE, > > but % and _ are the only ones where it makes a difference. > > Er, check that. Backslash itself also needs quoting in LIKE. > > Exercise for the student: if you need to match a literal backslash > in a LIKE pattern, how many backslashes do you have to write in your > query? I will fix the book. Seems I didn't do the test properly. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] order by accents?
Patrick Coulombe wrote: > > hi, > if I do a query like this one : > > SELECT name from medias ORDER BY name > > name > > > > > > > ÉCCC > > 6 rows > > Why the record : ÉCCC is at the end? > HOW can I fix this? > > Thank you > Patrick Hello, I have the same problem (the character is not recognized - we also use french) and it seems that the only way around this "bug" is to install Postgres with a local package... but many told me that is was slower and not very useful... I did not try it. Instead, we plan to create a second column with same french data but without accents... The query will be based on that field for the order by needs... not great, but I will do exactly what we expect and no add-ons. Hope this helps! Bonne journée! -- ~ Marc Andre Paquin
[SQL] date comparision
Is anyone out here can give some advice?
i have a table with a timestamp field.
i should know if there is records for the particular year and month
so i need a query like (in MySQL it was very simple, but i should move to
postgres!):
SELECT something FROM mytable WHERE MONTH('timestamp_field')=07
AND YEAR('timestamp_field')=2000 LIMIT 1;
So, i am looking for date/time functions:
SELECT * FROM table WHERE date_part('year',timestamp 'timestamp_field') =
2000;
but it doesn't work...(ERROR: Bad abstime external representation
'timestamp_field')
may be i need subselect or abstime(timestamp) function??
I suppose it's a lame q, but i cannot solve it today...
Thanks!
Re: [SQL] confused by select.
On Fri, Jul 07, 2000 at 11:44:30AM +0200, Patrick Jacquot wrote: [snip] > > usually many-to-many relationships are handled by a third table, like this: > > create table items (item_id,...) > create table customers (customer_id, ...) > create table orders (customer_id, item_id, quantity_orderered) Hi! In real life situations, I can only see one possible problem with that. Let's say you have a product in stock, for a long period of time, and at some point, you need to change the description of the product... Now what happens when you look at your old invoices? They show up with the new description.. What if you need to delete a product? :) What if the shipping address changes? I have found, as a requirement in many of my projects, that we be able to view invoices/orders /exactly/ like they were.. It involves a different set of tables, one for the 'open' orders, inventory, etc and another set of tables, that are populated for every invoice. (Yes, that means lots of duplication..) > > Hoping it may help > > Patrick JACQUOT > just my 1/50$ CDN (for what it's worth nowadays...) Antoine Reid
Re: [SQL] Altnerate Column Return Names
Thomas Swan writes: > If I do a select * from table1, table2 where table1.id = table2.id I > normally get two columns returned with a column identifier of id. > id | name | data | id | name | data > +--+--++--+-- > Is there anyway to get the result to return > > table1.id | table1.name | table1.data | table2.id | ,,, > ---+-+-+---+- ... select table1.id as "whatever you want", table1.name as "whatever you want", table1.data as "something", ... You get the idea. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [SQL] Search for underscore w/ LIKE
Tom Lane writes: > Exercise for the student: if you need to match a literal backslash > in a LIKE pattern, how many backslashes do you have to write in your > query? I like how Python handles this: You prefix the text literal with an `r' (as in "raw") and the backslashes are not special. Maybe we could offer that as well. blah ~ r'.+\..+' -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
[SQL] How to get a self-conflicting row level lock?
I have become maintainer of a program that uses PostgreSQL 6.5.2 for database functionality. It is littered with code blocks that do the following: 1. SELECT * FROM some_table WHERE foo = bar FOR UPDATE; 2. -- Choose a new value for some_field, which might or might not be based on its original value. 3. UPDATE some_table SET some_field = new_value WHERE foo = bar; I'm worried about concurrent process synchronization. According to the PostgreSQL docs on the LOCK command, SELECT ... FOR UPDATE acquires a "ROW SHARE MODE" lock, which is not self-conflicting. This tells me that when two processes execute the same code block concurrently, this can happen: 1. Process A selects the desired row for update. It now has a copy of the original values in that row. 2. Process B does the same. (This is allowed because ROW SHARE MODE locks do not conflict with each other.) It now has a copy of the original values in that row. 3. Process A chooses a new value for the desired field, based on the original value. 4. Process B does the same. 5. Process A updates the row with its new value, and exits. 6. Process B updates the row with its new value, overwriting the changes made by process A. Is it true that SELECT ... FOR UPDATE only acquires a ROW SHARE MODE lock, and that it isn't self-conflicting? How can I acquire a self-conflicting row level lock? What is the proper way to perform operations like those I'm describing? Thank you, Forest
Re: [SQL] How to get a self-conflicting row level lock?
Forest Wilkinson <[EMAIL PROTECTED]> writes: > I'm worried about concurrent process synchronization. According to the > PostgreSQL docs on the LOCK command, SELECT ... FOR UPDATE acquires a "ROW > SHARE MODE" lock, which is not self-conflicting. That doc is only telling part of the story, I guess. ROW SHARE MODE lock on a *table* is not self-conflicting, and should not be, because you want two different transactions to be able to acquire FOR UPDATE locks on different rows of the table concurrently. But SELECT FOR UPDATE also acquires a *per-tuple* lock on each selected row, and that lock will prevent another transaction from acquiring a FOR UPDATE lock on the same row(s). Try it and see. The reason SELECT FOR UPDATE acquires a table-wide lock at all is just to prevent table-wide conflicts, such as someone else trying to do a VACUUM or DROP TABLE on that table. I suspect that it doesn't really need to get a different kind of table lock than an UPDATE gets, but haven't thought about the details. regards, tom lane
