[SQL] protecting a field after creation
Hello, Is there a way (outside of RULEs and TRIGGERs) to make a field read-only once it is INSERTed or assigned its default value? I'm thinking, for example, of the "created" column that I add to most tables, holding the row's creation timestamp. Thanks in advance, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Murphy's Guide to Science: If it's green or squirms, it's biology. If it stinks, it's chemistry. If it doesn't work, it's physics.
[SQL] sum of agreggates in one SELECT?
Hello, I have the following query/result: auction=# select max(b.lot) as quantity,max(b.price) as price,p.login from bid b, person p where b.auction_id = 84 and p.id = b.person_id group by p.login order by max(price); quantity | price | login --+---+--- 1 | 5000 | papy 12 | 5750 | cunctator 8 | 6000 | vindex (3 rows) Now I would like to sum() all results from the quantity column and return it with one SELECT statement. Is that possible? I am trying: auction=# select sum(b.lot) from bid b, person p where b.auction_id = 84 and p.id = b.person_id ; sum - 52 (1 row) But this is wrong because it sums all quantities. I don't know how to apply a valid WHERE clause in that case. Thanks in advance for any help, cheers, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org
Re: [SQL] sum of agreggates in one SELECT?
On Tue, Sep 19, 2000 at 01:17:01PM -0500, John McKown wrote: > Well, it's not a single SELECT, but why not use something like: > > SELECT MAX(b.lot) AS quanity, max(p.price) AS price, p.login > INTO TEMPORARY TABLE temp1 > FROM bid b, person p > WHERE b.auction_id=84 AND p.id=b.person_id > GROUP BY p.login > ORDER BY max(price); > > SELECT SUM(quanity) from temp1; > > If you need the output from the original SELECT then you can print it by > simply doing: > > SELECT * FROM temp1; > > Hope this is of some use to you, Very useful, as it demonstrates that (as in perl) there is sometimes more than one way to do it. Your solution works fine, and along the way I learned to use temporary tables. Thanks a lot for your input, cheers, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org How's my posting? Call 1-800-DEV-NULL
Re: [SQL] sum of agreggates in one SELECT?
On Tue, Sep 19, 2000 at 11:06:06AM -0700, Josh Berkus wrote: > > Unfortunately, Louis-David, I don't see any way around subselects in the > FROM clause as Tom mentions, which are not currently supported. I'd > suggest using a Function to create a temporary table or view and > summarizing from that. I did create a pl/pgsql function in the end, to compute my total: FOR bid IN SELECT max(b.lot) AS price FROM bid b WHERE b.auction_id = $1 GROUP BY b.person_id LOOP i := i + bid.price; END LOOP; RETURN i; Thanks for your input, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org "Faith strikes me as intellectual laziness." -- Robert A. Heinlein
[SQL] no ORDER BY in subselects?
auction=# SELECT (select b.lot from bid b where b.auction_id = a.id and b.person_id = buyer.id order by b.price limit 1) as last_lot,auction_status(a.id) > 0 AS current, a.lot, a.person_id, next_price(a.id), seller.mail AS seller_mail, buyer.mail AS buyer_mail, seller.locale AS seller_locale, buyer.login AS buyer_login, num_bid(a.id), seller.login AS seller_login, t.name AS auction_type FROM auction* a, person seller, person buyer, auction_type t WHERE a.id = 84 AND seller.id = a.person_id AND COALESCE(a.type,1) = t.id AND buyer.id = 2; ERROR: parser: parse error at or near "order" Aren't ORDER BY clauses allowed in subselects? -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Black holes are where God divided by zero.
[SQL] ERROR: replace_vars_with_subplan_refs (!?)
How should I interpret that error? auction=# SELECT (select max(b.price) from bid b where b.auction_id = a.id and b.person_id = buyer.id) as last_lot,auction_status(a.id) > 0 AS current, a.lot, a.person_id, next_price(a.id), seller.mail AS seller_mail, buyer.mail AS buyer_mail, seller.locale AS seller_locale, buyer.login AS buyer_login, num_bid(a.id), seller.login AS seller_login, t.name AS auction_type FROM auction* a, person seller, person buyer, auction_type t WHERE a.id = 84 AND seller.id = a.person_id AND COALESCE(a.type,1) = t.id AND buyer.id = 2; ERROR: replace_vars_with_subplan_refs: variable not in subplan target list Thanks, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Marijuana is nature's way of saying, "Hi!".
Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]
On Wed, Sep 20, 2000 at 09:20:25AM -0700, Josh Berkus wrote: > > At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: > > > > > >ERROR: parser: parse error at or near "order" > > > > > >Aren't ORDER BY clauses allowed in subselects? > > > > > > > It is a very very sad fact, but, no, they're not. > > H ... can't say as I've ever seen an ORDER BY in a subselect before. > Why would you want one? If only to do a "LIMIT 1" on it. But this is probably considered very ugly to exprienced DB users (I'm only recently self-taught on that subject). > And if you do want one, Louis-David, you can always use a temporary > table as previously described. I found another workaround to the problem, finally. Thanks -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Your mouse has moved. Windows must be restarted for the change to take effect. Reboot now?
[SQL] missing "day(s)" in interval (was: Convert from Seconds-Since-Epoch to Timestamp)
On Thu, Sep 21, 2000 at 01:25:05PM -0700, Jie Liang wrote: > Hi, there, > > urldb=# create table foo(sec int4); > CREATE > urldb=# insert into foo values(54321); > INSERT 382942319 1 > urldb=# select interval(reltime (sec||'secs')) from foo; > interval > -- > 15:05:21 > (1 row) By the way, is it normal that the "day" word doesn't appear in the interval? auction=# select interval(now()::abstime::int4); interval ---- 30 years 9 mons 3 15:42:09 (1 row) -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org "Perl Guy and Executive Glue Sniffer" (stolen from Aaron Sherman's .sig)
[SQL] knowing which columns have beend UPDATEd inside a TRIGGER?
Hello, Is there a way to know which columns are being UPDATEd or INSERTEd from inside a trigger, either in C or pl/pgsql? Thanks in advance, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org If at first you don't succeed, redefine success.
Re: [SQL] knowing which columns have beend UPDATEd inside a TRIGGER?
On Tue, Oct 24, 2000 at 06:51:03PM -0400, Tom Lane wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > > Is there a way to know which columns are being UPDATEd or INSERTEd from > > inside a trigger, either in C or pl/pgsql? > > Huh? An INSERT always inserts all columns, by definition. Some of them > might be null and/or equal to their default values, but they're all > there. *slap* Doh! Thanks for clearing up my mind about this ;-) > For an UPDATE, you could check to see whether old.col = new.col. > This would miss the case where an UPDATE command is explicitly setting > a column to the same value it already had; dunno if you care or not. That is so obvious I didn't think about it, and it's exactly what I need. Thanks a lot, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Hi. This is my friend, Jack Shit, and you don't know him.
[SQL] Re: Compiling "C" Functions
On Thu, Dec 28, 2000 at 09:36:57AM -0500, Ron Peterson wrote: > Tulio Oliveira wrote: > > > > I appreciate any "C" Function complete samples, including de command > > line for > > the compiler. > > I've attached a generic GNU make snippet for compiling .so files. > Adjust to suite your tastes. Like my math textbooks used to say > "writing the C code is trivial, and is left as an excercise for the > reader." ;) > > CC = /usr/bin/gcc > TARGET = debug > #TARGET = final > DFLAGS = -Wall -g > FFLAGS = -Wall -O2 > SFLAGS = -fpic -shared > MYINCLUDES = -I/usr/local/include > -I/usr/local/src/postgresql/src/include -I/usr/local/postgresql/include > MYLIBS = -L/usr/local/lib -L/usr/local/postgresql/lib -lpq > > ifeq ($(TARGET),final) > MYCFLAGS = $(FFLAGS) > else > MYCFLAGS = $(DFLAGS) > endif > > %.so: > $(CC) $(MYCFLAGS) $(MYINCLUDES) $(MYLIBS) $(*F).c -c -o $(*F).o > $(CC) $(SFLAGS) $(*F).o -o $(*F).so > [ -d $(TARGET) ] || mkdir $(TARGET) > mv $(*F).so $(TARGET) > rm *.o Or using implicit rules, only type "make my_file.so": INCLUDES = -I /usr/include/postgresql CFLAGS = -g -Wall $(INCLUDES) -fPIC %.so: %.o ld -shared -soname $@ -o $@ $< $(LIBS) -- THERAMENE: Elle veut quelque temps douter de son malheur, Et ne connaissant plus ce héros qu'elle adore, Elle voit Hippolyte et le demande encore. (Phèdre, J-B Racine, acte 5, scène 6)
[SQL] Re: Maybe a Bug, maybe bad SQL
On Wed, Mar 21, 2001 at 10:49:41AM -0500, Bruce Momjian wrote: > > Note also that it's a mailing list cultural thing: many lists operate > > in a 'post only to the list' mode. Those of us on the pgsql lists do the > > 'list and person' thing, in response to direct questions, for the reasons > > Bruce and D'Arcy point out. Note that by knowing the reasons, one may > > then make informed decisions, like my posting of this message directly > > to the list only, since it's a peripheral issue and multiple people > > are involved in the conversation. It's not uncommon, when debugging > > a particular problem, or discussing implementation of a new feature, > > to have a thread of discussion by CC'ing three or four developers, > > plus the HACKERS list for archiving and general interest. > > My mailer would have trouble sending just to the list and not to both. > To do list-only, the mailing list software would have to set the > Reply-To to be to the list. Sorry, but what an inappropriate answer coming from an IT professionnal. You MUA doesn't support answering to a mailing list? Why not consider upgrading to a modern MUA that _does_ support that functionality? Are we condemned to use obsolete software? Are we stuck in old habits for ever? > Marc had it set up that way a few times, but most didn't like it. In > fact, the big problem with that setup is that you can't easily reply > just to the poster. The "reply-to: list"? Oh, I see: that one almost made it on the pgsql-* lists... *shiver* As if subject mangling and annoying footers were not enough. > Most mailers have a 'reply to user' and 'reply to group' mode. Reply to > user goes only to the poster, while reply-to group goes to both. Hint: http://www.mutt.org > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 I wish people could also lose the preaching in their signatures. 1) what does "christ" mean to muslim or hindu or atheist pgsql users? 2) it might be offensive to them 3) why not talk about what we have in common (hint: databases), not the most divisive issue in the history of humanity: religion 4) were I Jesus, I wouldn't appreciate being held as a mere "backup" ;-) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Re: [SQL] Permissons on database
On Wed, Mar 07, 2001 at 03:40:44PM -0500, Roland Roberts wrote: > > "bk" == Boulat Khakimov <[EMAIL PROTECTED]> writes: > > bk> How do I grant permissions on everything in the selected > bk> databes? > > bk> GRANT doesnt take as on object database name nor does it > bk> accept wild chars > > Attached is some Perl code I wrote long ago to do this. This > particular code was done for Keystone, a problem tracking database and > it would do a "GRANT ALL". Modify it as needed. Last I checked it > worked with both PostgreSQL 6.5.x and 7.0.x A simple two-line shell script to apply any command to a list of tables: for i in `psql mydatabase -c '\dt' -P tuples_only | cut -f2 -d ' '` do psql mydatabase -c "grant all on $i to public"; done > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl These mailing list footers really suck. Please consider removing them. They reflect poorly on the general level of the pgsql-* lists. Instead do send a one-time "welcome" message containing all your "tips" when people subscribe to a list. Probably a lost cause but the subject mangling [GENERAL], [HACKERS] etc. (especially that one! a "hacker" should know how to filter his mail) really sucks too. Educate, don't stoop. Even Outlook Express has great filtering capabilities which don't require any subject mangling. -- slashdot: I miss my free time, Rob. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] selecting latest record
Hi, I have a simple table price(id_product, price, date) which records price changes for each id_product. Each time a price changes a new tuple is created. What is the best way to select only the latest price of each id_product? Thanks, -- 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] selecting latest record
On Tue, Sep 22, 2009 at 11:56:54AM +0200, Pavel Stehule wrote: > > there are more ways - depends on what you wont. > > one way is > > SELECT * >FROM price > WHERE (id_product, date) = (SELECT id_product, max(date) >FROM price > GROUP BY > id_product) Nice. I didn't know one could have several args in a single WHERE clause. Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] 'image' table with relationships to different objects
Hello, In my database I have different object types (person, location, event, etc.) all of which can have several images attached. What is the best way to manage a single 'image' table with relationships to (potentially) many different object types while keeping referrential integrity (foreign keys)? Thanks, -- 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] 'image' table with relationships to different objects
On Tue, Feb 09, 2010 at 11:59:14AM +, Richard Huxton wrote: > On 09/02/10 07:49, Louis-David Mitterrand wrote: > >Hello, > > > >In my database I have different object types (person, location, event, > >etc.) all of which can have several images attached. > > > >What is the best way to manage a single 'image' table with relationships > >to (potentially) many different object types while keeping referrential > >integrity (foreign keys)? > > The "clean" way to do this would be with a number of joining tables: > > images(img_id, file_name, title ...) > persons (psn_id, first_name, last_name, ...) > locations (loc_id, loc_name, lat, lon, ...) > events(evt_id, evt_name, starts_on, ends_on, ...) > > person_images (psn_id, img_id) > location_images (loc_id, img_id) > event_images(evt_id, img_id) Thank you Richard, this looks like the best solution. And the view is handy. -- http://www.critikart.net -- 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] 'image' table with relationships to different objects
On Tue, Feb 09, 2010 at 08:01:35AM -0700, Rob Sargent wrote: > You can also invert this, making all the image owner share a common base > table and then images are dependent on that base > > base (id, type) where type is an enumeration or some such > person (id, name, etc) where id is FK to base id > locations (id, address, etc) where id is FK to base.id > events(id, date, etc) where id is FK to base.id > images(id, baseid) where baseid is FK to base.id > > views across base to the "data" tables for easier sql if desired > ORM: person location and event would inherit from base This is intriguing. How do I manage the auto-incrementing 'id' serial on children tables 'person', 'location' and 'event'? Thanks, -- http://www.critikart.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] "left join" not working?
Hi, This query: select c.id_currency,max(p.modified_on) from currency c left join price_line p using (id_currency) where p.id_line=1 group by c.id_currency; doesn't list all c.id_currency's, only those with a price_line. However this one does: select c.id_currency,max(p.modified_on) from currency c left join price_line p on (p.id_currency = c.id_currency and p.id_line=1) group by c.id_currency; How come? Thanks, -- 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] "left join" not working?
On Fri, Feb 12, 2010 at 11:35:02AM -, Oliveiros C, wrote: > My first guess is that > NULL fails the condition on your WHERE clause, > p.id_line = 1 > > So your WHERE clause introduces an additional level of filtering > that filters out the NULLs coming from the LEFT JOIN... So, if I understand correctly, a WHERE filters all results regardless of join conditions and can turn an OUTER JOIN into an INNER JOIN. Thanks for pointing that out! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] complex join question
Hi, Here is my schema: cruise_line -> ship(id_line) -> cruise_type(id_ship) -> cruise(id_cruise_type) -> price(id_cruise, id_currency) <- currency (USD,GBP,EUR,CAD) (a 'cruise' is a 'cruise_type' + a date) I am trying to display a count of cruise's for each ship and each currency even if that count is 0. But I am having trouble building the query, as some 'cruise's might not (yet) have a 'price' in all currencies and so no link to 'currency'. Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] UNION or LEFT JOIN?
Here is the basic schema: -->id_ship>--- || [SHIP]->id_ship->[CABIN]->id_cabin->[PRICE]<-id_cruise<-[CRUISE] It's a database of cruise prices. Each 'price' object has a reference to 'cabin' and 'cruise' 'cabin' belongs to a 'ship', so does 'cruise' I'm trying to select all cabins of cruise N°1 with prices OR nothing if there is no price (meaning cabin not available). I want all cabins listed, price or no price. Also when doing the query I don't have the id_ship, only the id_cruise. What is the best way of doing it? UNION or LEFT JOIN? I tried the latter without success and am unsure on how do do the former. Thanks, -- 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] UNION or LEFT JOIN?
On Tue, Feb 16, 2010 at 03:33:23PM +, Oliveiros wrote: > Louis-David, > > Please advice me, Hi Oliveiros, > if some cabin doesn't have a price i.e. it is not available, is there some > way to tell > which cruise it belongs to? In fact a cabin belongs to a ship and CAN be associated to a 'cruise' event with a price(id_cruise,id_cabin) object. > You have PRICE table which seems to me to be an associative table between > cruise and cabin, is this correct? Yes, > But, if the price doesn't have a register for that pair > (îd_cabin,id_cruise), how do you know that cabin belongs to that cruise, in > this case, cruise nº 1? I am trying to display a list of all cabins of a ship for a certain cruise even if some prices are missing, so the user sees what cabins are not available. After much trial and error I was finally able to build a left join query that works. Thanks a lot for offering your help! -- 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] UNION or LEFT JOIN?
On Tue, Feb 16, 2010 at 09:38:19PM +, Tim Landscheidt wrote: > Louis-David Mitterrand wrote: > > > Here is the basic schema: > > > -->id_ship>--- > > || > > [SHIP]->id_ship->[CABIN]->id_cabin->[PRICE]<-id_cruise<-[CRUISE] > > > It's a database of cruise prices. > > > Each 'price' object has a reference to 'cabin' and 'cruise' > > > 'cabin' belongs to a 'ship', so does 'cruise' > > > I'm trying to select all cabins of cruise N°1 with prices OR nothing if > > there is no price (meaning cabin not available). I want all cabins > > listed, price or no price. > > > Also when doing the query I don't have the id_ship, only the id_cruise. > > > What is the best way of doing it? UNION or LEFT JOIN? I tried the latter > > without success and am unsure on how do do the former. > > Was does "without success" mean? The objective seems to be > straight-forward: > > - Select all cabins that belong to the ship that belongs to > the cruise id_cruise. > - Left join that with the prices of the cruise id_cruise. Definitely the way to go. As the real schema is quite a bit more complicated I was struggling with very long statements, but finally succeded with a simple left join. Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] join with an array
Hi, I'm trying the following query: select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id = any(array_agg)) group by t1.col1; but I get this error: ERROR: column "array_agg" does not exist I tried aliasing array_agg(t1.id) without success. Thanks for any suggestions, -- 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] join with an array
On Wed, Feb 24, 2010 at 02:09:09PM +0100, A. Kretschmer wrote:
> In response to Louis-David Mitterrand :
> > Hi,
> >
> > I'm trying the following query:
> >
> > select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id =
> > any(array_agg)) group by t1.col1;
> >
> > but I get this error: ERROR: column "array_agg" does not exist
> >
> > I tried aliasing array_agg(t1.id) without success.
> >
> > Thanks for any suggestions,
> I can't really understand what you want to achieve, but maybe this is
> what you are looking for:
Here is a test case I built. I want to list all cruises by cruise_type
but after merging cruise_type that have the same cruise_type_name:
drop table cruise;
drop table cruise_type;
create table cruise_type (
id_cruise_type serial primary key,
cruise_type_name text
);
create table cruise (
id_cruise serial,
id_cruise_type integer references cruise_type,
cruise_date timestamp default now()
);
insert into cruise_type (cruise_type_name) values
('5 day eastern carribean cruise'),
('5 day western carribean cruise'),
('5 day eastern carribean cruise'),
('5 day western carribean cruise')
;
insert into cruise (id_cruise_type) values
(1),
(2),
(3),
(4),
(1),
(2),
(3),
(4)
;
select array_agg(ct.id_cruise_type),ct.cruise_type_name from cruise_type ct
join cruise c on (c.id_cruise = any(array_agg)) group by cruise_type_name;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] check constraint on multiple tables?
Hi, I've got this chain of tables: ship --> (id_ship) --> cabin_type --> (id_cabin_type) --> cabin_category --> (id_cabin_category) --> cabin The 'cabin' table has (cabin_number, id_cabin_category ref. cabin_category) How can I guarantee unicity of cabin_number per ship? For now I added a unique(cabin_number,id_cabin_category) but this does not guarantee unicity for (cabin_number,ship.id_ship). What is the best solution? Adding an id_ship to 'cabin'? Or check'ing with a join down to 'ship'? (if possible). Thanks, -- 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] check constraint on multiple tables?
On Wed, Mar 03, 2010 at 07:14:29AM -0800, Richard Broersma wrote: > On Wed, Mar 3, 2010 at 7:02 AM, Louis-David Mitterrand > wrote: > > > > What is the best solution? Adding an id_ship to 'cabin'? Or check'ing > > with a join down to 'ship'? (if possible). > > Can you post simplified table definitions for the relations involved? Sure, here they are: CREATE TABLE ship ( id_ship serial primary key, ship_name text unique not null ); CREATE TABLE cabin_type ( id_cabin_type serial primary key, id_ship integer references ship, cabin_type_name text, cabin_type_code text, unique(cabin_type_code, id_ship) ); CREATE TABLE cabin_category ( id_cabin_category serial primary key, id_cabin_type integer references cabin_type, cabin_cat_name text, cabin_cat_code text, unique(cabin_cat_code, id_cabin_type) ); CREATE TABLE cabin ( id_cabin serial primary key, id_cabin_category integer references cabin_category, cabin_number integer not null, unique(id_cabin_category, cabin_number) ); -- 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] check constraint on multiple tables?
On Wed, Mar 03, 2010 at 07:29:22AM -0800, Richard Broersma wrote: > On Wed, Mar 3, 2010 at 7:19 AM, Louis-David Mitterrand > wrote: > > > CREATE TABLE cabin_type ( > > > CREATE TABLE cabin_category ( > > > CREATE TABLE cabin ( > > I'm just curious about a few things. > > 1) What is the difference between a cabin_type and a cabin_category. A cabin_type is: large suite, junior suite, balcony cabin, interior, etc. A cabin_category is, for say a "balcony cabin", on which deck it is located (price increases as the deck is higher). > 2) Does each ship have an exclusive set of cabin_types that no other > ship can have? The table definitions imply that this is so. Each ship is different and has specific cabin types and categories. Of course there is some overlap between ships but I thought it simpler (or more elegant) to use that hierarchy. Maybe my schema is wrong? > I'm just guessing here since I don't really understand the > relationships involved in a ship's cabins. However, I would expect > that a cabin should be directly related to a ship. Each cabin is > defined by a category according the set in the cabin_category table. I could add an id_ship to 'cabin' but that would make two (potentialy conflicting) relations to 'ship'. -- 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] check constraint on multiple tables?
On Wed, Mar 03, 2010 at 10:03:05AM -0600, Little, Douglas wrote: > Hello, > > I would have designed as ship > cabin (PK of ship_id, Cabin_id) > And a separate chain of cabin_type > cabin_category > cabin Ah, now I'm having second thoughts about my schema ;) > Type, and category are group classifiers and shouldn't be used to > define the uniqueness of a cabin. Yes, but some ships have quite unique cabin types and categories. > Take an example where the cabin category and type are defined globally > for the entire fleet. Currently you'll have to duplicate the type, > category defintions for each ship. Each ship is unique (more or less, a cruise line has several classes of ships). So are its types and cats so it's not so clear cut. -- 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] check constraint on multiple tables?
On Wed, Mar 03, 2010 at 04:05:43PM -, Oliveiros wrote: > > As your table names seem to imply, type and category are cabin's > characteristics , not ship characteristics. > Am I right? Yes and no :) - I'm scanning cruise line web sites with a spider to collect prices so I'm building cabin_type's and cabin_category's on the fly, - each ship class (a cruise line has several) has its own particular types (Celebrity Xpedition is the only one with "Xpedition suite" type, etc.) > As Richard pointed out, maybe you could add a relationship between > cabin and ship and drop the relationship between ship and > cabin_category you now have > Then you could add that uniqueness restriction. That's one option. > Also, the relationship between type and category is one to many ? Or > can it be many to many? Put other way, is this overlap between the > categories that belong to different "types" ? One cabin_type to many cabin_category's, for example: - "Sunset Veranda Stateroom" (type) can be on "Vista", "Panorama", etc. decks (category) with a different price, But it's true that there is some overlap in categories between different ships. > If the later applies, maybe > you could have cabin refer to both type and category tables and drop > the relation between type and category. > > The cabin table would then work as an associative table between > category and type. > > Ain't saying your schema is wrong, maybe you have strong reasons to > do that that way, that I am not realizin by now... You got me thinking about it. Thank you for your interesting comments. -- 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] check constraint on multiple tables?
On Wed, Mar 03, 2010 at 10:13:48AM -0600, Little, Douglas wrote: > Hey Louis, > > Ship rooms are just like Hotel rooms. There are lots of ways to > describe. But there needs to be some consistency between the > classifiers for them to have any meaning. > > A junior suite should mean the same thing regardless of the ship it's on. Not so simple, cruise lines like to slice and dice (segment in marketing speak) their offerings by creating ship-specific cabin types with (ever so slightly) different features and prices. -- 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] check constraint on multiple tables?
On Wed, Mar 03, 2010 at 10:29:44AM -0600, Little, Douglas wrote: > Louis, > Interesting discussion. Always fun to think about real world stuff. Indeed. > We have a similar problem for comparing hotel rooms. > So the issue is that you aren't originating the data, just classifying it. > I'd move toward a scheme where you reclassify the line marketing speak > to common lay terms. You're trying to help consumers compare. > Exactly what the marketers don't want them to do. I'm leaning towards exactly that: letting the data settle, spot overlap and general trends, reclassify. > After all a silk purse is just a sow's ear without marketing. > :) True but cruise customers buy into that marketing and insist on those slight distinctions between almost identical cabins. It's all a question of status and it's what cruise lines sell. Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] an aggregate to return max() - 1 value?
Hi, With builtin aggregates is it possible to return the value just before max(col)? Thanks, -- 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] an aggregate to return max() - 1 value?
On Thu, Mar 04, 2010 at 08:53:10PM +, Greg Stark wrote: > SELECT col FROM tab ORDER BY col DESC OFFSET 1 LIMIT 1 > > In 8.4 OLAP window functions provide more standard and flexibility > method but in this case it wouldn't perform as well: > > postgres=# select i from (select i, rank() over (order by i desc) as r > from i) as x where r = 2; > i > > 99 > (1 row) > > postgres=# select i from (select i, dense_rank() over (order by i > desc) as r from i) as x where r = 2; > i > > 99 > (1 row) Wow, I didn't know about window functions until now. It's exactly what I need. Thanks Greg, and also thanks to others who sent their suggestion. Cheers, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] window function to sort times series data?
Hi, I have time series data: price(id_price int, price int, created_on timestamp) I'd like to select the latest price before, say, 2010-03-10 and the latest price after that date. Using "group by" and self-joins I was able to build a (quite large :) working query. But I wonder if there is a cleaner, shorter solution with a window function. I tried something like: select * from (select first_value(p.id_price) over w as first_id_price, first_value(p.price) over w as first_price, first_value(p.created_on::date) over w as first_date, nth_value(p.id_price,2) over w as second_id_price, nth_value(p.price,2) over w as second_price, nth_value(p.created_on::date,2) over w as second_date, p.id_price from price p window w as (order by p.created_on > '2010-03-10, p.id_price desc rows between unbounded preceding and unbounded following)) as t where first_id_price=id_price; But this doesn't return correct results. Thanks for any suggestions, -- 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] window function to sort times series data?
On Wed, Mar 24, 2010 at 03:29:36PM +0100, A. Kretschmer wrote: > In response to Louis-David Mitterrand : > > Hi, > > > > I have time series data: price(id_price int, price int, created_on > > timestamp) > > > > I'd like to select the latest price before, say, 2010-03-10 and the > > latest price after that date. > > test=*# select * from price ; > id_price | price | created_on > --+---+- > 1 |10 | 2010-01-01 00:00:00 > 1 |12 | 2010-02-01 00:00:00 > 1 | 8 | 2010-03-01 00:00:00 > 1 |15 | 2010-03-10 00:00:00 > 1 |13 | 2010-03-20 00:00:00 > (5 rows) > > test=*# select * from ( > select distinct on(id_price) id_price, price, created_on from price where > created_on < '2010-02-20'::date order by id_price, created_on desc > ) foo union all select * from ( > select distinct on(id_price) id_price, price, created_on from price where > created_on > '2010-02-20'::date order by id_price, created_on asc > ) bar order by id_price,created_on ; > id_price | price | created_on > --+---+- > 1 |12 | 2010-02-01 00:00:00 > 1 | 8 | 2010-03-01 00:00:00 > (2 rows) > > That's okay for you? Yes, that works, but I forgot in my specs (!) that I'd like the two prices (pre and post 2010-03-10) to be returned on the same row and only if a post-2010-03-10 price exists. Thanks, -- 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] window function to sort times series data?
On Wed, Mar 24, 2010 at 05:29:46PM +0100, Andreas Kretschmer wrote:
> A. Kretschmer wrote:
>
> Well, and now i'm using 8.4 windowing-functions:
>
> test=*# select * from price order by price_id, d;
> price_id | price | d
> --+---+
> 1 |10 | 2010-03-12
> 1 |11 | 2010-03-19
> 1 |12 | 2010-03-26
> 1 |13 | 2010-04-02
> 1 |14 | 2010-04-09
> 1 |15 | 2010-04-16
> 1 |16 | 2010-04-23
> 1 |17 | 2010-04-30
> 2 |20 | 2010-03-12
> 2 |21 | 2010-03-19
> 2 |22 | 2010-03-26
> 2 |23 | 2010-04-02
> (12 Zeilen)
>
> -- now i'm searching for 2010-03-20:
>
> Zeit: 0,319 ms
> test=*# select price_id, sum(case when d < '2010-03-20'::date then price
> else 0 end) as price_old, sum(case when d > '2010-03-20'::date then
> price else 0 end) as price_new, max(case when d < '2010-03-20'::date
> then d else null end) as date_old, max(case when d > '2010-03-20'::date
> then d else null end) as date_new from (select price_id, price, d,
> lag(d) over(partition by price_id order by d), lead(d) over(partition by
> price_id order by d) from price) foo where '2010-03-20'::date between
> lag and lead group by price_id;
> price_id | price_old | price_new | date_old | date_new
> --+---+---++
> 1 |11 |12 | 2010-03-19 | 2010-03-26
> 2 |21 |22 | 2010-03-19 | 2010-03-26
> (2 Zeilen)
Nice use of lag() and lead() functions.
In my db id_price is a serial so it's easy to use in an aggregate to
determine the latest.
I also looked at window functions and did the following:
select p3.price as first_price,
p4.price as second_price
from (select
first_value(max(p.id_price)) over w as
first_id_price,
nth_value(max(p.id_price),2) over w as
second_id_price,
p.created_on > '2010-03-20' as is_new_price
from price p
group by p.created_on > '2010-03-20'
window w as (order by p.created_on > '2010-03-20'
desc rows between unbounded preceding and unbounded
following)
) as t
join price p3 on (t.first_id_price=p3.id_price)
left join price p4 on (t.second_id_price=p4.id_price)
where t.is_new_price is true
test=# \e
first_price | second_price
-+--
17 | 11
(1 row)
Is there some potential optimizations or flaws?
Here is the test database:
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: price; Type: TABLE; Schema: public; Owner: ldm; Tablespace:
--
CREATE TABLE price (
id_price integer NOT NULL,
price integer,
created_on timestamp without time zone
);
ALTER TABLE public.price OWNER TO ldm;
--
-- Name: price_id_price_seq; Type: SEQUENCE; Schema: public; Owner: ldm
--
CREATE SEQUENCE price_id_price_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.price_id_price_seq OWNER TO ldm;
--
-- Name: price_id_price_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ldm
--
ALTER SEQUENCE price_id_price_seq OWNED BY price.id_price;
--
-- Name: price_id_price_seq; Type: SEQUENCE SET; Schema: public; Owner: ldm
--
SELECT pg_catalog.setval('price_id_price_seq', 8, true);
--
-- Name: id_price; Type: DEFAULT; Schema: public; Owner: ldm
--
ALTER TABLE price ALTER COLUMN id_price SET DEFAULT
nextval('price_id_price_seq'::regclass);
--
-- Data for Name: price; Type: TABLE DATA; Schema: public; Owner: ldm
--
COPY price (id_price, price, created_on) FROM stdin;
1 10 2010-03-12 00:00:00
2 11 2010-03-19 00:00:00
3 12 2010-03-26 00:00:00
4 13 2010-04-02 00:00:00
5 14 2010-04-09 00:00:00
6 15 2010-04-16 00:00:00
7 16 2010-04-23 00:00:00
8 17 2010-04-30 00:00:00
\.
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
--
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] Does IMMUTABLE property propagate?
On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: > Petru Ghita writes: > > "..immediately replaced with the function value" doesn't mean that the > > results of a previously evaluated function for the same parameters are > > stored and reused? > > No, it means what it says: the function is executed once and replaced > with a constant representing the result value. So for example a function like: CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS text AS $$ declare outtext text; begin outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', '', 'i')); return outtext; end; $$ LANGUAGE plpgsql; could/should be declared immutable? Thanks, -- 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] Does IMMUTABLE property propagate?
On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote: > 2010/3/25 Louis-David Mitterrand : > > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: > >> Petru Ghita writes: > >> > "..immediately replaced with the function value" doesn't mean that the > >> > results of a previously evaluated function for the same parameters are > >> > stored and reused? > >> > >> No, it means what it says: the function is executed once and replaced > >> with a constant representing the result value. > > > > So for example a function like: > > > > > > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS > > text > > AS $$ > > declare > > outtext text; > > begin > > outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', > > '', 'i')); > > return outtext; > > end; > > $$ > > LANGUAGE plpgsql; > > > > yes it should be declared as immutable. plpgsql function is black box > for executor, so you have to use some flag. language sql is different, > executor see inside, so there you can not do it. Hmm, that's interesting. So for simple functions (like my example) it is better to write them in plain sql? And in that case no 'immutable' flag is necessary? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] graphing time series data
Hi, I have times series data in a 'price' table: price(id_price, price, id_product, created, modified) Prices are polled daily and a new 'price' row is created only if the price of id_product changes, else modified is updated to now(). Now, I'd like to make a graph of average prices per week, per id_product. As some prices don't vary much, distribution would not be ideal if I simply 'group by extract(week from p.modified)'. Ideally I'd generate_series() a list of weeks between min(p.created) and max(p.modified) and then average prices 'group by p.modified < week'. What would be the best way to tackle this? Thanks, -- 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] graphing time series data
On Wed, Apr 14, 2010 at 08:46:13AM -0700, Richard Broersma wrote:
> On Wed, Apr 14, 2010 at 7:54 AM, Louis-David Mitterrand
> wrote:
>
> > Now, I'd like to make a graph of average prices per week, per
> > id_product. As some prices don't vary much, distribution would not be
> > ideal if I simply 'group by extract(week from p.modified)'.
>
> I created a view for a similar problem that I had. Only I was
> calculating the counts per day. this query could be crafted to work
> for you.
>
> CREATE OR REPLACE VIEW opendiscrepencydailycounts AS
> WITH opendays(day) AS (
> SELECT gs.day::date AS day
>FROM generate_series((( SELECT
> min(discrepencylist.discstartdt) AS min
>FROM discrepencylist))::timestamp without time
> zone, 'now'::text::date::timestamp without time zone, '1
> day'::interval) gs(day)
> )
> SELECT opendays.day, ds.resolvingparty, count(opendays.day) AS
> opendiscrepancies
>FROM discrepencylist ds, opendays
> WHERE opendays.day >= ds.discstartdt AND opendays.day <=
> LEAST('now'::text::date, ds.resolutiondate)
> GROUP BY opendays.day, ds.resolvingparty
> ORDER BY opendays.day, ds.resolvingparty;
You confirm my modus operandi. I tried the following which seems to give
me optimal price distribution:
select w.week,count( p.id_price) from
(select generate_series(min(p.created_on),max(p.modified_on),'1
week') as week from price p) as w join price p on (p.created_on
<
w.week + '7 days' and p.modified_on > w.week + '7 days') group
by
w.week order by w.week
week | count
+
2010-02-10 15:32:18+01 | 125369
2010-02-17 15:32:18+01 | 126882
2010-02-24 15:32:18+01 | 128307
2010-03-03 15:32:18+01 | 126742
2010-03-10 15:32:18+01 | 133596
2010-03-17 15:32:18+01 | 149019
2010-03-24 15:32:18+01 | 149908
2010-03-31 15:32:18+02 | 147617
The rest should be easy from there!
Thanks for your input,
--
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] graphing time series data
On Wed, Apr 14, 2010 at 06:06:59PM +0200, Pavel Stehule wrote:
> I don't understand well. Why you don't use a function date_trunc(),
>
> select date_trunc('week', created), count(*)
> from price
> group by date_trunc('week', created)
Because if a price doesn't change for more than a week, then some weeks
will have bad statistical distribution (i.e not including prices which
only have their 'modified' updated). So I (think I) need to (1) generate
the weeks separately and (2) average prices that are current for each
week.
But I could be missing something obvious.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] best paging strategies for large datasets?
Hi, I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and am in the process of developping a pager to let users leaf through it (30K rows). Ideally I'd like to know when requesting any 'page' of data where I am within the dataset: how many pages are available each way, etc. Of course that can be done by doing a count(*) query before requesting a limit/offset subset. But the main query is already quite slow, so I'd like to minimize them. But I am intrigued by window functions, especially the row_number() and ntile(int) ones. Adding "row_number() over (order by )" to my query will return the total number of rows in the first row, letting my deduce the number of pages remaining, etc. row_number() apparently adds very little cost to the main query. And ntile(buckets) seems nice too but I need the total row count for it to contain a 'page' number: ntile(row_count/page_size). What better "paging" strategies are out there? Thanks, -- 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] best paging strategies for large datasets?
On Wed, May 12, 2010 at 12:26:17PM -0400, Justin Graf wrote: > oops typos > On 5/12/2010 1:41 AM, Louis-David Mitterrand wrote: > > Hi, > > > > I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and > > am in the process of developping a pager to let users leaf through it > > (30K rows). > > That's not that big of a record set. Well for me it's a big one :) But then again it's my first serious web app. > > Ideally I'd like to know when requesting any 'page' of data where I am > > within the dataset: how many pages are available each way, etc. > > > > Of course that can be done by doing a count(*) query before requesting a > > limit/offset subset. But the main query is already quite slow, so I'd > > like to minimize them. > > > > What do you mean by quite slow?? Like several seconds. I have to cache the results. > On a 30K record table count() and query speed should not be a problem.. This query is a large multi-join of times series data, not a single table. And it's not (prematurely :) optimized. I'm planning a materialized view for it. > > But I am intrigued by window functions, especially the row_number() and > > ntile(int) ones. > > > > Adding "row_number() over (order by)" to my query will > > return the total number of rows in the first row, letting my deduce the > > number of pages remaining, etc. row_number() apparently adds very little > > cost to the main query. > > That will get a sequential number, but you still don't know how many > records are in the table, limit and offset block that value. > I don't see how this helps? > > Limit and Offset with Total Record count tell us where we are in the > record set and which page we are on. Hmm, good to know. I hadn't tried that yet. > RecordCount/Limit = Number of pages > CurrentPage = (offset%RecordCount)/Limit These simple formulas we bill handy. > to complicate things further what if the site allows user to change the > number of records displayed per page. The pager logic needs to figure > out how many records need to be return per page, and what the next and > previous iterations are. Without the total record count I don't see how > that is even possible. > > I have written pagers in ASP and PHP Thanks for your input. I now realize I'll have to get a total count in a separate (cached) query, or else I'll only be able to provide a basic "previous/next" pager. Cheers, -- 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] best paging strategies for large datasets?
On Thu, May 13, 2010 at 11:36:53AM +0400, silly sad wrote: > On 05/12/10 09:41, Louis-David Mitterrand wrote: > >Hi, > > > >I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and > >am in the process of developping a pager to let users leaf through it > >(30K rows). > > > >Ideally I'd like to know when requesting any 'page' of data where I am > >within the dataset: how many pages are available each way, etc. > > > >Of course that can be done by doing a count(*) query before requesting a > >limit/offset subset. But the main query is already quite slow, so I'd > >like to minimize them. > > nowadays i tend to bet on AJAX. > in other words i propose to move some calculations to a client side at all. > > and this particular situation might looks similar to the following: > > First u count(*) the rows and select a requested page > returning to a client the count result bundled "with a page of rows" > > (1) client renders the acquired rows > (2)__memorize__ what part of the data he just got > (3) and stores the count result to calculate "the pager div" > > all the subsequent clicks on "the pager div" should not immediately > generate requests and decides if the request is needed. Yes, rendering the results throught ajax is a good idea, but one has to be careful not to expose one's LIMIT and OFFSET to the client, but only the "page" number. Or else the client could query the whole data set. A lot of "professional" web site have that hole. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] safely exchanging primary keys?
Hi,
I have this function which swaps primary keys for cabin_types (so that
id_cabin_type ordering reflects natural data ordering):
CREATE OR REPLACE FUNCTION swap_cabin_types(id1 integer, id2 integer)
RETURNS integer
AS $$
declare
tmp integer;
begin
tmp := nextval('cabin_type_id_cabin_type_seq');
update cabin_type set id_cabin_type=tmp where id_cabin_type=id1;
update cabin_type set id_cabin_type=id1 where id_cabin_type=id2;
update cabin_type set id_cabin_type=id2 where id_cabin_type=tmp;
return tmp;
end;
$$
LANGUAGE plpgsql;
'id_cabin_type' is a foreign key for two other tables, 'cabin_category'
and 'alert_cabin_type', which have an "on update cascade" clause.
When I run that function it seems the foreign keys are not properly
updated and the data ends up in a mess.
Did I forget something?
Thanks,
--
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] safely exchanging primary keys?
On Mon, May 24, 2010 at 10:51:01AM +0200, Louis-David Mitterrand wrote: > Hi, > > I have this function which swaps primary keys for cabin_types (so that > id_cabin_type ordering reflects natural data ordering): Actually this function works fine. My problem was elsewhere. Sorry for barking up the wrong tree. -- 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] safely exchanging primary keys?
On Mon, May 24, 2010 at 02:38:39PM +, Tim Landscheidt wrote: > Louis-David Mitterrand wrote: > > What does "are not properly updated" mean? Anyhow, why don't Hi, I did follow-up on my own post: the problem was elsewhere. > you use something simple like (untested): > > | UPDATE cabin_type > | SET id_cabin_type = > | CASE > | WHEN id_cabin_type = id1 THEN > | id2 > | ELSE > | id1 > | END > | WHERE id_cabin_type IN (id1, id2); Nice, thanks. -- 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] safely exchanging primary keys?
On Mon, May 24, 2010 at 07:00:30PM +0200, Louis-David Mitterrand wrote: > On Mon, May 24, 2010 at 02:38:39PM +, Tim Landscheidt wrote: > > you use something simple like (untested): > > > > | UPDATE cabin_type > > | SET id_cabin_type = > > | CASE > > | WHEN id_cabin_type = id1 THEN > > | id2 > > | ELSE > > | id1 > > | END > > | WHERE id_cabin_type IN (id1, id2); > > Nice, thanks. Ah, but this won't work as the UNIQUE PK constraint is in force. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] "compressing" consecutive values into one
Hi, On time series price data I'm trying to remove consecutive identical prices and keep only the latest. I tried: delete from price where id_price in (select t.id_price2 from (select first_value(p.id_price) over w as id_price1, nth_value(p.id_price, 2) over w as id_price2, first_value(p.price) over w as price1, nth_value(p.price,2) over w as price2 from price p window w as (partition by p.id_rate,p.id_cabin_category,p.id_cruise order by p.id_price desc rows between unbounded preceding and unbounded following)) as t where price1 = price2); and it mostly works but I have to do several runs to completely eliminate identical consecutive prices. Is there a better, one-pass, way? Thanks, -- 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] "compressing" consecutive values into one
On Tue, Nov 23, 2010 at 03:31:59PM -, Oliveiros d'Azevedo Cristina wrote: > Salut, Louis-David, > > Can you please state the columns belonging to price table > and give a concrete example? > Say, data before and data after you want to do? Hi Cristina, Data before: id_price | price 1| 23 3| 45 4| 45 6| 45 8| 45 9| 89 Data after: id_price | price 1| 23 8| 45 9| 89 Thanks, -- 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] "compressing" consecutive values into one
On Tue, Nov 23, 2010 at 04:19:18PM +0100, Louis-David Mitterrand wrote: > Hi, > > On time series price data I'm trying to remove consecutive identical > prices and keep only the latest. I tried: > > delete from price where id_price in (select t.id_price2 from (select > first_value(p.id_price) over w as id_price1, > nth_value(p.id_price, 2) over w as id_price2, > first_value(p.price) over w as price1, > nth_value(p.price,2) over w as price2 > from price p > window w as (partition by > p.id_rate,p.id_cabin_category,p.id_cruise > order by p.id_price desc rows between unbounded > preceding and > unbounded following)) as t where price1 = price2); > > and it mostly works but I have to do several runs to completely > eliminate identical consecutive prices. Actually I found the answer to my own question. It's the WINDOW lag/lead functions that I needed and this time one pass is enough: delete from price where id_price in ( select t.id_price1 from (select lead(p.id_price) over w as id_price1, lead(p.price) over w as price1, p.id_price, p.price from price p window w as (partition by p.id_rate,p.id_cabin_category,p.id_cruise order by p.id_price rows between unbounded preceding and unbounded following)) as t where t.price = t.price1); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] organizing cron jobs in one function
Hi, I'm planning to centralize all db maintenance jobs from a single pl/pgsql function called by cron every 15 minutes (highest frequency required by a list of jobs). In pseudo code: CREATE or replace FUNCTION cron_jobs() RETURNS void LANGUAGE plpgsql AS $$ DECLARE rec record; BEGIN /* update tbl1 every 15 minutes*/ select name, modified from job_last_update where name='tbl1' into rec; if not found or rec.modified + interval '15 minutes' < now() then perform tbl1_job(); update job_last_update set modified=now() where name='tbl1'; end if; /* update tbl2 every 2 hours */ select name, modified from job_last_update where name='tbl2' into rec; if not found or rec.modified + interval '2 hours' < now() then perform tbl2_job(); update job_last_update set modified=now() where name='tbl2'; end if; /* etc, etc.*/ END; $$; The 'job_last_update' table holds the last time a job was completed. Is this a good way to do it? Thanks, -- 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] organizing cron jobs in one function
On Sun, Nov 18, 2012 at 07:27:54PM +0800, Craig Ringer wrote: > On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote: > > Hi, > > > > I'm planning to centralize all db maintenance jobs from a single > > pl/pgsql function called by cron every 15 minutes (highest frequency > > required by a list of jobs). > It sounds like you're effectively duplicating PgAgent. > > Why not use PgAgent instead? Sure, I didn't know about PgAgent. Is it still a good solution if I'm not running PgAdmin and have no plan doing so? -- 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] organizing cron jobs in one function
On Mon, Nov 19, 2012 at 08:31:10AM +0800, Craig Ringer wrote: > On 11/19/2012 01:11 AM, Louis-David Mitterrand wrote: > > On Sun, Nov 18, 2012 at 07:27:54PM +0800, Craig Ringer wrote: > >> On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote: > >>> Hi, > >>> > >>> I'm planning to centralize all db maintenance jobs from a single > >>> pl/pgsql function called by cron every 15 minutes (highest frequency > >>> required by a list of jobs). > >> It sounds like you're effectively duplicating PgAgent. > >> > >> Why not use PgAgent instead? > > Sure, I didn't know about PgAgent. > > > > Is it still a good solution if I'm not running PgAdmin and have no plan > > doing so? > > > It looks like it'll work. The main issue is that if your jobs run > over-time, you don't really have any way to cope with that. Consider > using SELECT ... FOR UPDATE, or just doing an UPDATE ... RETURNING > instead of the SELECT. > > I'd also use one procedure per job in separate transactions. That way if > your 4-hourly job runs overtime, it doesn't block your 5-minutely one. > > Then again, I'd also just use PgAgent. In my last question I was asking about the ability to run PgAgent _without_ PgAdmin. Is that possible? From the docs it seems PgAgent requires a GUI to configure jobs, and I'd rather avoid that if possbile. Otherwise thanks for the advice, especially the 'overtime' issue (which when a new job starts when the old one is not over yet, right?) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] list variable attributes in one select
Hello, I've got the following tables: person: - id_person - firstname - lastname - type person_to_type: - id_person references person - type references person_type; person_type: - type "person_type" contains differents caracteristics for a person (actor, director, author, etc.) who can have several types, hence the need for the person_to_type table. I'd like to know if I can list in one SELECT command a person and all of its types, given that the number of types can be 0 to n. For example, for a given person I'd like to obtain: "John Doe", "actor", "playright", "author" or "Jane Doe", "director" in one select. Is that possible? ---(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
[SQL] sub-limiting a query
Hello, I've got a table of shows with different types (movie, theater, ballet,etc.) and I am looking for a select that can return the 10 last entered shows AND at most 2 of each type. Is that possible in one query? The table looks basically like: created_on | timestamp without time zone show_name | text id_show | integer show_type | text id_show_subtype | integer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] sub-limiting a query
On Sat, Feb 17, 2007 at 07:21:40PM +0100, M.P.Dankoor wrote: > Louis-David Mitterrand wrote: > > > I thought of another solution, actually it's of those top n query tricks > that I picked up somewhere, can't remember > where. > Assuming that your table is called shows, the following query should > give you the results you want (hope so) > > SELECT * > FROM shows a > WHERE 3 > (SELECT COUNT(*) > FROM shows b > WHERE b.created_on >= a.created_on > and a.show_type = b.show_type) This is stunning and it works! I can barely understand the query: it's so terse it hurts :) /me goes back studying it Thanks a lot! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] simple web search
Hello, I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. What is the most elegant way to build a single query to match search words with multiple columns? Thanks, ---(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] simple web search
On Fri, Feb 23, 2007 at 10:01:22AM -0800, chester c young wrote: > > I'm considering implementing a search box on my review web site > > http://lesculturelles.net and am looking for a simple way to match > > entered words against several columns on related tables: > > show.show_name, story.title, person.firtname, person.lastname, etc. > > one solution would be a view: > > create view search_v as select > 'show'::name as tab_nm, > show_id as tab_pk, > 'Show Name' as description, > show_name as search > from show > union select > 'story'::name, > story_id, > 'Story Title', > title > from story > union ... > > your query would be > select * from search_v where '$string' ilike search > > this would return a list the user could use to drill down further. Thanks, this looks promising. The union and view ideas are indeed inspiring. What is that ::name cast for? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] simple web search
On Fri, Feb 23, 2007 at 01:31:14PM -0500, Joe wrote: > Hello Louis-David, > > On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: > > I'm considering implementing a search box on my review web site > > http://lesculturelles.net and am looking for a simple way to match > > entered words against several columns on related tables: show.show_name, > > story.title, person.firtname, person.lastname, etc. > > > > What is the most elegant way to build a single query to match search > > words with multiple columns? > > You may want to take a look at contrib/tsearch2. Thanks Joe, I initially wanted to avoid dipping my toe into tsearch2 but it might be what I need after all :) ---(end of broadcast)--- TIP 1: 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
[SQL] getting at sequence previous/next values
Hello, I've got a table that looks basically like: id_show | serial show_name | text show_type | text created_on | timestamp without time zone When looking at a row with an id_show of value n, I'd like to have an easy way of knowing the preceding and next values of id_show in the sequence (which might have holes). Is there some convenient way to get that info, or must I do a full-blown select? Thanks, ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] query to select a linked list
Hi,
To build a threaded forum application I came up the following schema:
forum
--
id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass)
id_parent| integer|
subject | text | not null
message | text |
Each message a unique id_forum and an id_parent pointing to the replied
post (empty if first post).
How can I build an elegant query to select all messages in a thread?
Thanks,
---(end of broadcast)---
TIP 1: 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] query to select a linked list
On Wed, May 09, 2007 at 02:24:22PM +0100, Gregory Stark wrote: > "Louis-David Mitterrand" <[EMAIL PROTECTED]> writes: > > > Each message a unique id_forum and an id_parent pointing to the replied > > post (empty if first post). > > > > How can I build an elegant query to select all messages in a thread? > > You would need recursive queries which Postgres doesn't support. There is a > patch out there to add support but I don't think it's up-to-date with 8.2 and > in any case the resulting queries can be quite intense. > > I would recommend you look into the contrib module named "ltree". It's easy to > use and works well with the gist indexes. It does require changing your data > model denormalizing it slightly which makes it hard to "reparent" children, > but if that isn't an operation you have to support I think it makes most other > operations you might want to do much easier to support. After looking around a little I came to the same conclusions. Thanks for you help, Cheers, ---(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] query to select a linked list
On Wed, May 09, 2007 at 02:55:20PM +0200, Louis-David Mitterrand wrote:
> Hi,
>
> To build a threaded forum application I came up the following schema:
>
> forum
> --
> id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass)
> id_parent| integer|
> subject | text | not null
> message | text |
>
> Each message a unique id_forum and an id_parent pointing to the replied
> post (empty if first post).
>
> How can I build an elegant query to select all messages in a thread?
I am trying to write a recursive pl/sql function to return all thread
children:
create or replace function forum_children(integer) returns setof forum as $$
declare
rec record;
begin
for rec in select * from forum where $1 in (id_parent,id_forum) loop
select * from forum_children(rec.id_forum);
return next rec;
end loop;
return;
end;
$$ language 'plpgsql';
But it does not work as intended (infinite loop?).
What did I miss?
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] query to select a linked list
On Wed, May 09, 2007 at 04:30:21PM +0200, Louis-David Mitterrand wrote:
> On Wed, May 09, 2007 at 02:55:20PM +0200, Louis-David Mitterrand wrote:
> > Hi,
> >
> > To build a threaded forum application I came up the following schema:
> >
> > forum
> > --
> > id_forum | integer| not null default
> > nextval('forum_id_forum_seq'::regclass)
> > id_parent| integer|
> > subject | text | not null
> > message | text |
> >
> > Each message a unique id_forum and an id_parent pointing to the replied
> > post (empty if first post).
> >
> > How can I build an elegant query to select all messages in a thread?
>
> I am trying to write a recursive pl/sql function to return all thread
> children:
>
> create or replace function forum_children(integer) returns setof forum as $$
> declare
> rec record;
> begin
>
> for rec in select * from forum where $1 in (id_parent,id_forum) loop
Oops, I meant :
for rec in select * from forum where id_parent=$1 loop
which works fine.
Sorry,
> select * from forum_children(rec.id_forum);
> return next rec;
>
> end loop;
>
> return;
>
> end;
> $$ language 'plpgsql';
>
>
> But it does not work as intended (infinite loop?).
>
> What did I miss?
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] query to select a linked list
On Thu, May 10, 2007 at 09:49:32AM +1000, Robert Edwards wrote: > > Hi Louis-David, > > I also have written a forum application using PostgreSQL. > > My schema has a "threadid" for each posting, which is actually also the > "messageid" of the first posting in the thread, but that is irrelevant. > > I can then just select all messages belonging to that thread. The actual > hierarchy of messages (which posting is in response to which) is dealt > with by a "parentid", identifying the messageid of the post being > responded to. Sorting that out is done by the middleware (PHP in this > case) - the SQL query simply returns all messages in the thread in a > single query. Because our database is somewhat busy, I have opted to > keep the queries to the database simple and let the middleware sort > out the heirarchical structure (which it is quite good at). > > I hope this helps. This helps a lot, thanks. I just wrote a little pl/sql function to compensate for the absence of a threadid in my schema: create or replace function forum_children(integer) returns setof forum as $$ declare rec record; subrec record; begin for rec in select * from forum where id_parent=$1 loop return next rec; for subrec in select * from forum_children(rec.id_forum) loop return next subrec; end loop; end loop; return; end; $$ language 'plpgsql'; But in the end it might just be more convenient and clear to have that threadid column as you did. Sorting in middleware (perl in my case) also seems like good compromise. Cheers, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] is there a 'table' data type in pg?
Hello, In my forum app a new post can be related to several types of objects: person, location, event, simple text subject, etc. so in my 'forum' table I plan to add an id_subject column which can contain a reference to any number of different tables (location, person, etc.). What I need to know is to _what_ table the id_subject belongs. Can I use a another column to store the type of the id_subject (ie: the tabled it belongs to) ? Then I would be able to query that table for additional info to print alongside the forum posts. Thanks for your insights, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] is there a 'table' data type in pg?
On Tue, Jul 24, 2007 at 03:10:44PM +0100, Gregory Stark wrote: > "Louis-David Mitterrand" <[EMAIL PROTECTED]> writes: > > > Can I use a another column to store the type of the id_subject (ie: the > > tabled it belongs to) ? Then I would be able to query that table for > > additional info to print alongside the forum posts. > > There are ways to identifier tables in Postgres but there's no way to run a > query against a table using them. Bummer, I suspected as much. > I would strongly recommend you define your own list of "object_types", > probably even have an object_type table with a primary key, a description > column, and a table_name column. Then you can in your application construct > the appropriate query depending on the object_type. Good fallback solution. > One alternative you could do is have a set-returning plpgsql function which > has a big if statement and performs the right kind of query. I think the > records would have to all be the same -- they can't be different kinds of > records depending on the type of object. Will look at that one, always willing to dig deeper into pg's more complex ways :) Thanks for your help, ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] inheriting a rule or a trigger?
Hi, I'm trying to protect created_by and created_on columns from accidental update. Most of my tables inherit from a 'source' table that has those columns, so I was thinking of creating a rule or trigger that does nothing on update to these columns. But apparently rules and triggers don't apply to child tables. Is there another way to have the same effect, short of a rule/trigger on each table? ---(end of broadcast)--- TIP 1: 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
[SQL] dynmic column names inside trigger?
Hi, I've got this trigger to clean up text entered in web forms: CREATE or replace FUNCTION sanitize_text() RETURNS "trigger" AS $$ declare begin if old.story is not null and new.story != old.story then new.story = translate(new.story, E'\x92\x96', '''-'); new.story = regexp_replace(new.story, E'\x9c', 'oe', 'g'); new.story = regexp_replace(new.story, E'\x85', '...', 'g'); end if; return new; end; $$ LANGUAGE plpgsql; CREATE TRIGGER sanitize_text_trig BEFORE INSERT or update ON story FOR EACH ROW EXECUTE PROCEDURE sanitize_text(); I'd like to use it on other tables an columns but how can the column name be dynamic inside the procedure. Passing the column name in the trigger declaration and using it as NEW.TG_ARGV[0] seems out of the question. Is there another solution out there? Thanks, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] dynmic column names inside trigger?
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote: > I would do something like this (not tested, but conceptually working): Hello, > BEGIN > if old.story is not null and new.story != old.story then > new.story = sanitize_text(new.story); > end if; > --checks on other field can be included here, eg > if old.otherfield is not null and new.otherfield != old.otherfield then > new.otherfield = sanitize_text(new.otherfield); > end if; But if I test a non-existent column for not being null I will have an exception, no? Otherwise this is a nice way of doing it. Thanks, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] dynmic column names inside trigger?
On Tue, Nov 20, 2007 at 11:56:02AM -0500, Tom Lane wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > > I'd like to use it on other tables an columns but how can the column > > name be dynamic inside the procedure. > > It can't --- plpgsql has no support for that. You could probably make > it work in some of the other PL languages, such as plperl or pltcl, > which are less strongly typed. Hi Tom, What the performance penality of using plperl vs. plpgsql ? Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] dynmic column names inside trigger?
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote: > I would do something like this (not tested, but conceptually working): > > CREATE or replace FUNCTION sanitize_text(webtext IN text, cleantext OUT text) > AS > $body$ > BEGIN > cleantext = translate(webtext, E'\x92\x96', '''-'); > cleantext = regexp_replace(cleantext, E'\x9c', 'oe', 'g'); > cleantext = regexp_replace(cleantext, E'\x85', '...', 'g'); > END; > $body$ > LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT; Hi, I was curious as to why you created this function with a prototype of func(intext IN text, outtext OUT text) ... returns NULL instead of the usual func(intext text) ... returns TEXT Is that a more efficient way? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] passing a multiple join to a function?
Hi,
I've got this ugly case statement that I'd like to hide in a function:
select
...
case when
d.start_date <= CURRENT_DATE and
case when w.show_type in ('cinéma','livre')
then d.start_date >= CURRENT_DATE - 21
else (d.end_date >= CURRENT_DATE or d.end_date is null) end
then '0_actualite'
when
d.start_date > CURRENT_DATE
then '1_agenda'
else '2_archive'
end
as timing
...
from story s
join show w on (s.id_show = w.id_show)
join show_date d on (d.id_show = w.id_show and
d.start_date = (select d2.start_date from show_date d2
where d2.id_show = w.id_show
order by d2.end_date >= CURRENT_DATE desc,
d2.start_date limit 1)
)
...
I could very well create a show_timing(int) function that accepts an
id_show and performs its own, additional, multiple join complex query on
story, show_date, and show.
Is there a way of feeding enough data to the function to avoid another
query?
Thanks,
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] passing a multiple join to a function?
On Mon, Dec 17, 2007 at 12:27:34PM -0500, Rodrigo De León wrote: > On 12/17/07, Louis-David Mitterrand <[EMAIL PROTECTED]> wrote: > > I've got this ugly case statement that I'd like to hide in a function: > > Why don't you hide the entire query in a VIEW? That is probably the best solution. Thanks ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] returning an array as a list fo single-column rows?
Hi, is there a way to return a Pg array as a list of single-column row values? I am trying to circumvent DBI's lack of support for native database arrays and return the list of values from an ENUM as a perl array. Thanks, ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] returning an array as a list fo single-column rows?
On Sun, Dec 23, 2007 at 10:19:26PM +0100, Pavel Stehule wrote: > Hello > > try > > create or replace function unpack(anyarray) > returns setof anyelement as $$ > select $1[i] > from generate_series(array_lower($1,1), array_upper($1,1)) g(i); > $$ language sql; > > postgres=# select * from unpack(array[1,2,3,4]); > unpack > > 1 > 2 > 3 > 4 > (4 rows) Beautiful. Thank you. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] returning an array as a list fo single-column rows?
On Sun, Dec 23, 2007 at 10:27:09PM +0100, Pavel Stehule wrote: > On 23/12/2007, Louis-David Mitterrand > <[EMAIL PROTECTED]> wrote: > > Hi, > > > > is there a way to return a Pg array as a list of single-column row > > values? > > > > I am trying to circumvent DBI's lack of support for native database > > arrays and return the list of values from an ENUM as a perl array. > > > > Thanks, > > > > you can solve this problem with conversion to string with const separator > > Like: > > postgres=# select array_to_string(array[1,2,3,4],'|'); > array_to_string > - > 1|2|3|4 > (1 row) > > [EMAIL PROTECTED] ~]$ perl > @a = split(/\|/, "1|2|3"); > print $a[1]; Yes I thought about it, but would rather have Pg do the array splitting. For instance if the separator occurs in an array element there is no built-in escaping: % select array_to_string(array['ee','dd','rr','f|f'],'|'); array_to_string - ee|dd|rr|f|f ... and then perl would have it all wrong. ---(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] EPOCH TIMESTAMP Conversion Problem
On Wed, Nov 07, 2007 at 10:35:08AM -0500, Tom Lane wrote: > "Amitanand Chikorde" <[EMAIL PROTECTED]> writes: > > I want to convert MAX(mydate) from myTable to Epoch. > > Do you mean > SELECT EXTRACT(EPOCH FROM MAX(mydate)) FROM myTable Is using casts for the same purpose deprecated? SELECT current_date::timestamp::abstime::int4; Or less efficient? ---(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
[SQL] dynamic events categorization
Hello,
I'm looking for a more efficient way of dynamically categorizing some
events. The following view definition looks into each event's latest
event_date object (a theater play can have several, a book only one) to
tell whether the event is current, past or future:
SELECT s.id_event_subtype, s.subtype, t.id_event_type, t.type,
e.id_event, e.created_by, e.created_on, e.modified_by, e.modified_on,
e.id_image, e.show_name, e.length, d.id_date,
d.start_date, d.end_date, d.low_price, d.high_price, d.id_location,
d.showtime,
CASE
WHEN d.start_date <= 'now'::text::date AND
CASE
WHEN t.type = 'movie'::text THEN
d.start_date >= ('now'::text::date - 21)
WHEN t.type = 'book'::text THEN
e.created_on >= ('now'::text::date - 28)
ELSE d.end_date >= 'now'::text::date OR
d.end_date IS NULL
END THEN '0_current'::text
WHEN d.start_date > 'now'::text::date THEN
'1_future'::text
WHEN d.start_date IS NOT NULL THEN
'2_past'::text
ELSE ''::text
END AS timing
FROM event e
NATURAL JOIN event_type2 t
LEFT JOIN event_subtype2 s USING (id_event_subtype)
LEFT JOIN show_date d USING (id_event);
This view is widely used in my application, including as a basis for
further views, as I almost always need to know the 'timing' category of
an event (past, current, future). But I have nagging doubts about its
efficiency. It also seems pretty slow in its current form.
Any suggestion on how to improve it (including schema modifications) are
more than welcome.
Thanks,
--
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] dynamic events categorization
On Thu, Jun 26, 2008 at 05:06:14PM +0200, Marc Mamin wrote: > > Hello, Hi Marc, > I guess that the time offsets (now-21 and now-28) are evaluated each > time the corresponding condition is met. Excellent suggestion, this makes the query ~ 15% faster. Every bit counts. > It may be faster to put them into a separate sub query. I'm not sure > about putting "now" itself within the sub query... Where would you put it? > It may also be better to put your query in a procedure where you can put > these constants into variables instead of using a sub query. > > Depending of the distribution of a) 2_past,1_future,0_current and '' and > b) t.type, it may be worth to have different queries, bound with UNION > ALL. This would simplify the "CASE" construct and at least part of the > tests should happen on indexes only. Could you give a very short example? > If the query is run very often, you may want to add a boolean column > is_past on show_date, and have a separate job that put the concerned > records to true every x minutes ... That would require a cron job (?). I'm trying to keep the app self-contained for now. > HTH, It sure does, thanks! Cheers, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] exclusion query
Hi, I've got five related tables: - person_type: id_person_type integer type_fr text - person: id_person integer ... - person_to_event: id_person -> person id_person_type -> person_type (e.g: actor, director, producer, ...) id_event-> event - event: id_eventinteger id_event_type -> event_type ... - event_type: id_event_type integer type_fr text To select person_type's used in a certain event_type I have this query: select distinct pt.type from person_type pt natural join person_to_event join event e using (id_event) natural join event_type et where et.type_fr='théâtre'; Now, I'd like to select person_type's _not_ used in a certain particular event (say id_event=219). I can see how to build a quey to that effect, but is there a more obvious, clean, short solution? Something that looks like the above query maybe? Thanks, -- 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] exclusion query
On Mon, Sep 22, 2008 at 04:34:14PM +0200, Louis-David Mitterrand wrote: > Hi, > > I've got five related tables: > > - person_type: > id_person_type integer > type_fr text > > - person: > id_person integer > ... > > - person_to_event: > id_person -> person > id_person_type -> person_type (e.g: actor, director, producer, ...) > id_event-> event > > - event: > id_eventinteger > id_event_type -> event_type > ... > > - event_type: > id_event_type integer > type_fr text > > To select person_type's used in a certain event_type I have this query: > > select distinct pt.type > from person_type pt > natural join person_to_event > join event e using (id_event) > natural join event_type et > where et.type_fr='théâtre'; > > Now, I'd like to select person_type's _not_ used in a certain particular > event (say id_event=219). To be more precise: not used in a particular event _but_ used in other events of type 'theatre'. > I can see how to build a quey to that effect, but is there a more > obvious, clean, short solution? Something that looks like the above > query maybe? > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- 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] exclusion query
On Mon, Sep 22, 2008 at 09:39:08AM -0700, Mark Roberts wrote: > > Taking your second email into account, I came up with: > > select distinct pt.type_fr > from person_to_event pte > inner join person_type using (id_person_type) > where id_person_type in ( > select id_person_type > from person_to_event pte > inner join event using (id_event) > inner join event_type using (id_event_type) > where type_fr = 'theatre' > ) and id_person_type not in ( > select id_person_type > from person_to_event > where id_event = 219 > ) > > I feel like there's a solution involving group by tugging at the back of > my mind, but I can't quite put my finger on it. Sorry if this isn't > quite what you're asking for. Hi, That works very nicely (with minor adaptations). I also had that solution-without-a-subselect in the back of my mind but this does the job just fine! Cheers, -- 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] exclusion query
On Thu, Sep 25, 2008 at 02:11:23PM +0100, Oliveiros Cristina wrote: > Hi, Louis-David, > > I guess you already have your problem solved, but just for the sake of > curiosity, another > way to do it might be to tweak a little your original query, I've written > on Capitals the things I've added. > Should you need to exclude more than one event you can add the conditions > to the commented line (ORed ) That LEFT JOIN + GROUP BY trick is wicked! :-) I spent the last half hour struggling to understand it. You solution is a great learning tool and you obviously know your way around SQL. Thanks! > Best, > Oliveiros > > select distinct pt.type > from person_type pt > natural join person_to_event > join event e using (id_event) > LEFT JOIN event e2 > ON e.id_event = e2.id_event > AND e2.id_event=219 -- put here the id of the event you wanna exclude > join event_type et > ON e.id_event_type = et.id_event_type > where et.type_fr='théâtre' > GROUP BY pt.type_fr > HAVING SUM(e2.id_event) IS NULL; > > - Original Message - From: "Louis-David Mitterrand" > <[EMAIL PROTECTED]> > To: > Sent: Tuesday, September 23, 2008 9:18 AM > Subject: Re: [SQL] exclusion query > > >> On Mon, Sep 22, 2008 at 09:39:08AM -0700, Mark Roberts wrote: >>> >>> Taking your second email into account, I came up with: >>> >>> select distinct pt.type_fr >>> from person_to_event pte >>> inner join person_type using (id_person_type) >>> where id_person_type in ( >>> select id_person_type >>> from person_to_event pte >>> inner join event using (id_event) >>> inner join event_type using (id_event_type) >>> where type_fr = 'theatre' >>> ) and id_person_type not in ( >>> select id_person_type >>> from person_to_event >>> where id_event = 219 >>> ) >>> >>> I feel like there's a solution involving group by tugging at the back of >>> my mind, but I can't quite put my finger on it. Sorry if this isn't >>> quite what you're asking for. >> >> Hi, >> >> That works very nicely (with minor adaptations). >> >> I also had that solution-without-a-subselect in the back of my mind but >> this does the job just fine! >> >> Cheers, >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] many-to-many relationship
Hi, Say you have several objects (tables): person, location, event, etc. all of which can have several images attached. What is the best way to manage relations between a single 'image' table and these different objects? For now each 'image' row has pointers to id_person, id_location, id_event, etc. (only one of which is used for any given row). Is there a better way, more elegant way to do it, without using redundant id_* pointers on each row and yet still enforce foreign keys? Thanks, -- http://www.lesculturelles.net -- 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] many-to-many relationship
On Mon, Oct 06, 2008 at 09:25:09AM -0400, Dan McFadyen wrote: > Hello, > > Simplest way I can think of is create 3 relation tables, a person/image > table, location/image table and event/image table. > > Each is just made up for 2 foreign keys to the first ID and image ID, > using both as the PK for the table. On Mon, Oct 06, 2008 at 09:30:41AM -0400, Dave Steinberg wrote: > The typical way to do this would be to have your image table be just > about images, and then to isolate the relationship information into > mapping tables. Those would look like: > > image <=> people > (image_id, person_id), with the primary key being the pair of columns. > In SQL, roughly: Thanks Dan and Dave, you suggested the same solution which seems the most reasonable. -- http://www.lesculturelles.net -- 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] many-to-many relationship
On Tue, Oct 07, 2008 at 05:16:39PM -0700, Steve Midgley wrote: > > I think the relationship tables method works pretty well but I have > another suggestion. You could store the Foreign table name within image > table as well as the Foreign key. > > |id|image_url|f_table|f_key > |1 |url..|person |1234 > |2 |url2.|event |5678 > > I think this is called a "polymorphic join" but I could be wrong about > that. I'd guess you could construct a rule or trigger to validate the > foreign key data on insert/update but that's out of my skill area. Hi Steve, So in your solution the f_table column is just text which needs to be validated by a custom trigger? -- http://www.lesculturelles.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] adding "order by" to a "group by" query
Hi,
This query:
select p.id_person, person_name(p), array_accum(distinct pt.type_fr)
from person p
left join person_to_event x using (id_person)
left join person_type pt using (id_person_type)
where person_name(p) ilike '%will%'
group by p.id_person,person_name(p);
returns:
id_person | person_name | array_accum
---+--+---
181 | William Eggleston| {comédien}
200 | William H.Macy | {comédien}
242 | William Nicholson| {auteur}
309 | William Friedkin | {réalisateur}
439 | William Shakespeare | {auteur}
591 | William Christie | {musicien}
786 | Paul Andrew Williams | {réalisateur}
1015 | William Mesguich | {comédien,"metteur en scène"}
But if I append this
order by pt.type_fr = 'comédien';
I get this error:
ERROR: column "pt.type_fr" must appear in the GROUP BY clause or be
used in an aggregate function
It seems I am using pt.type_fr in an aggregate function (array_accum()),
yet I get the error.
Is there a way to to have a certain pt.type_fr bubble up (or down) in my
search?
Thanks,
--
http://www.critikart.net
--
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] adding "order by" to a "group by" query
On Sat, Dec 06, 2008 at 06:26:06PM +0100, Andreas Kretschmer wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> schrieb: > > > > But if I append this > > > > order by pt.type_fr = 'comédien'; > > > > I get this error: > > > > ERROR: column "pt.type_fr" must appear in the GROUP BY clause or be > > used in an aggregate function > > > > It seems I am using pt.type_fr in an aggregate function (array_accum()), > > yet I get the error. > > > > Is there a way to to have a certain pt.type_fr bubble up (or down) in my > > search? > > You can use a subquery like my example: > > test=*# select i, comma(t) from (select distinct i,t from foo) bar group by i; > i | comma > ---+- > 1 | a, b, c > (1 row) > > Time: 0.554 ms > test=*# select i, comma(t) from (select distinct i,t from foo order by t > desc) bar group by i; Thanks Andreas, that would be good solution. (still curious about the "must be used in an aggregate function" error though... because I do use it in an aggregate) -- http://www.critikart.net -- 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] adding "order by" to a "group by" query
On Sat, Dec 06, 2008 at 06:24:25PM +, John Lister wrote: > >(still curious about the "must be used in an aggregate function" error > >though... because I do use it in an aggregate) > > You're original query grouped on the person id and name, therefore you > can only return (and order by) these functions or the result of an > aggregate function on other columns (such as the array_accum function). > > I'm no expert, but I think the error is slightly misleading, normally > you would order by the result of an aggregate function but maybe the > parser does this implicitly for you sometimes. does > > select p.id_person, person_name(p), array_accum(distinct pt.type_fr) >from person p >left join person_to_event x using (id_person) >left join person_type pt using (id_person_type) >where person_name(p) ilike '%will%' group by > p.id_person,person_name(p) >order by 3; > > work for you? Not quite. But thanks for your suggestion John: I just learned that one can supply an index to an order clause. Actually what I'd like to be able to do is: put the (say) 'actors' in front of the list. The catch is that a person can have several person_type's (through the person_to_event table: id_person, id_event, id_person_type). -- http://www.critikart.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] optimizing a query
Hi, I have an 'event' table and an 'event_date' table pointing to it containing (potentially) several event dates (start and (optionnaly) end) for the event in the past, present and future. I'm trying to build a query to select the most "relevant" date: 'current' or 'next' or 'last' (by order of priority). Actually I already have a view of event+"most relevant"event_date: CREATE VIEW event_story_review AS SELECT d.* FROM event_list_story_review d WHERE (d.id_date = (SELECT d2.id_date FROM event_date d2 WHERE (d2.id_event = d.id_event) ORDER BY d2.end_date is not null desc, (d2.end_date >= d.today) DESC, d2.start_date LIMIT 1)); This works but I am bothered by the subquery which has a slight performance impact on all queries using this view (there are many in my app). Is there a better way of doing it? maybe without a subquery? Thanks, -- http://www.critikart.net -- 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] optimizing a query
On Sun, Dec 14, 2008 at 02:51:24PM -0800, Michal Szymanski wrote: > On 14 Gru, 20:22, vindex+lists-pgsql-...@apartia.org (Louis-David > Mitterrand) wrote: > > > > I have an 'event' table and an 'event_date' table pointing to it > > containing (potentially) several event dates (start and (optionnaly) > > end) for the event in the past, present and future. > > > > I'm trying to build a query to select the most "relevant" date: > > 'current' or 'next' or 'last' (by order of priority). > > > > Actually I already have a view of event+"most relevant"event_date: > > > > CREATE VIEW event_story_review AS > > SELECT d.* FROM event_list_story_review d > > WHERE (d.id_date = (SELECT d2.id_date FROM event_date d2 > > WHERE > > (d2.id_event = d.id_event) > > ORDER BY d2.end_date is not null desc, > > (d2.end_date >= d.today) DESC, > > d2.start_date LIMIT 1)); > > > > This works but I am bothered by the subquery which has a slight > > performance impact on all queries using this view (there are many in my > > app). > > > > Is there a better way of doing it? maybe without a subquery? > > The question is how do you plan to use your view ? Do you select all > rows from viev or you select only few tow from view using additional > filters? Usually you can rewrite subquery to JOINsbut without > information how do you plan use view it is hard to say is it bettter > solution. > It is important how many row do you plan in each table. I usually select all rows from the view with additional filters. If you have an example of rewriting the query with a join (instead of subquery) would you care sending it? So that I could run some tests. Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] archiving or versioning data?
Hi, I'd like to find a way to archive versions of my data in an elegant and extensible way. When a user modifies certain entries I'd like the database to keep the previous versions (or a limited, definable number of versions). Wiki-style. Would that be a good use of postgres' arrays? So I'm looking for "best practices" (tm) on that subject. Thanks in advance for your suggestions, -- http://www.critikart.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using max() aggregate
Hello, I am trying to return the most recently updated record from a table: SELECT max(stopdate) FROM auction; and this works but only returns the timestamp, however if I try to get another column with the aggregate it fails: SELECT title,max(stopdate) FROM auction; ERROR: Attribute auction.title must be GROUPed or used in an aggregate function Ok, so I group it now: SELECT title,max(stopdate) FROM auction GROUP BY title; title | max ---+ dfsdfsdf | 2000-07-10 05:00:00+02 dssdfsdfsdfsf | 2000-07-09 16:00:00+02 sdfsdfsdfsdf | 2001-04-10 15:00:00+02 (3 rows) But the problem is that I now get three rows when I only want the max() item. How should I do it? Thanks in advance, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr "Logiciels libres : nourris au code source sans farine animale."
Re: Antw: [SQL] using max() aggregate
On Fri, Jun 16, 2000 at 09:28:27AM +0200, Gerhard Dieringer wrote: > > I am trying to return the most recently updated record from a table: > > > > SELECT max(stopdate) FROM auction; > > > > and this works but only returns the timestamp, however if I try to get > > another column with the aggregate it fails: > > > > SELECT title,max(stopdate) FROM auction; > > ERROR: Attribute auction.title must be GROUPed or used in an aggregate function > > > > Ok, so I group it now: > > > > SELECT title,max(stopdate) FROM auction GROUP BY title; > > title | max > > ---+ > > dfsdfsdf | 2000-07-10 05:00:00+02 > > dssdfsdfsdfsf | 2000-07-09 16:00:00+02 > > sdfsdfsdfsdf | 2001-04-10 15:00:00+02 > > (3 rows) > > > > But the problem is that I now get three rows when I only want the max() > > item. > > SELECT title,stopdate > FROM auction > WHERE stopdate = (SELECT max(stopdate) FROM auction); > > should work. Thanks for your suggestion. Yes this would work nicely but if I need to add more conditional clauses I have to duplicate them in the main SELECT and in the sub-SELECT: SELECT title,max(stopdate) FROM auction WHERE stopdate = (SELECT max(stopdate) FROM auction AND stopdate > now()) AND stopdate > now(); Or am I missing something? Tom Lane suggested using: SELECT title,stopdate FROM auction ORDER BY stopdate LIMIT 1; which seems the best solution (I was a bit concerned about performance, but then again the max() aggregate does a scan of all rows as well). Cheers, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr This is Linux Country. On a quiet night you can hear Windows NT reboot.
Re: Antw: [SQL] using max() aggregate
On Fri, Jun 16, 2000 at 10:56:04AM +0200, Gerhard Dieringer wrote: > > Yes this would work nicely but if I need to add more conditional clauses > > I have to duplicate them in the main SELECT and in the sub-SELECT: > > > > SELECT title,stopdate > > FROM auction > > WHERE stopdate = (SELECT max(stopdate) FROM auction WHERE stopdate > now()) > > AND stopdate > now(); > > > > Or am I missing something? > > > > Tom Lane suggested using: > > > > SELECT title,stopdate FROM auction ORDER BY stopdate LIMIT 1; > > > > which seems the best solution (I was a bit concerned about performance, > > but then again the max() aggregate does a scan of all rows as well). > > ... > > I don't see why you repeat your conditions in the outer select. The > condition in the inner select drops all records that violate the > conditions, so the same conditions in the outer select have nothing to > do and you can leave them away. Maybe mine was a bad example but if, for instance, you add a condition on the "login" attribute (that it should start with a 'm'), then if you omit the clause from the outer select you risk having a false match if two records have the same stopdate: SELECT title,login,stopdate FROM auction WHERE stopdate = (SELECT max(stopdate) FROM auction WHERE login LIKE 'm%'); > Tom's solution has the drawback, that if you have more than one record > with the same max value you only get one of them, but may be that you > want to see all of them. True. Thanks, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr Save the whales. Feed the hungry. Free the mallocs.
[SQL] finding (and recycling) holes in sequences
If one has a unique-id generating sequence that sometimes is bound to have holes in it (ie: it could happen that a nextval(seq) happens without a corresponding INSERT in the table), then how could one efficiently scan for these holes to recycle them in subsequent INSERTs? I'm just looking for a "standard" way of doing this if such a thing exists. TIA -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr "For a list of the ways which technology has failed to improve our quality of life, press 3."
