Re: [SQL] select
Knut P Lehre wrote: > I have a table with fields: > id A B C D E F > where id is an int4 primary key. > In this table there is information like: > 1 a1 b1 xxx xxx > 2 a1 b1 xxx xxx xxx xxx > 3 a2 b2 xxx xxx xxx xxx > 4 a2 b2 xxx xxx xxx xxx > 5 a3 b3 xxx xxx xxx > 6 a3 b3 xxx xxx xxx xxx > 7 a3 b3 xxx xxx xxx xxx > 8 a4 b4 xxx xxx xxx xxx > 9 a1 b1 xxx > 10 a3 b3 xxx > 11 a1 b3 xxx > where xxx represents any information. > My problem is: I only want to select one of the records which have the same > combination of information in fields A and B, and that should be the record > with the lowest id. Thus, the resultset should be: > 1 a1 b1 xxx xxx > 3 a2 b2 xxx xxx xxx xxx > 5 a3 b3 xxx xxx xxx > 8 a4 b4 xxx xxx xxx xxx > 11 a1 b3 xxx > Importantly, I might not want the selected records listed in order of increasing > id. They might be sorted according to the data in e.g. the C field. > > Suggestions/comments greatly appreciated. > Does this give you the result you are looking for? SELECT * FROM ( SELECT DISTINCT ON (A,B) * FROM table ORDER BY id ) AS foo ORDER BY C ; Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Suggestions on storing re-occurring calendar events
Hello, I've got an application and there is something that I just
cannot get figured out.
I currently store a bunch of events for various calendars in related
tables that look like this:
ftr_cal_master
Column | Type | Modifiers
+---+--
calendarid | bigint| not null
icon | character varying(12) | not null
showpast | boolean | not null default 't'
headline | text |
paragraph | text |
Indexes: ftr_master_pkey primary key btree (calendarid),
ftr_master_caledarid_key btree (calendarid)
ftr_cal_events
Column| Type | Modifiers
-+--+
eventid | integer | not null default
nextval('"ftr_cal_events_eventid_seq"'::text)
calendarid | bigint | not null
startdate | timestamp with time zone | not null default now()
enddate | timestamp with time zone | not null default now()
title | text |
description | text |
contact | text |
location| text |
Indexes: ftr_cal_events_pkey primary key btree (eventid),
eventid_ftr_cal_events_ukey unique btree (eventid),
calendarid_ftr_cal_events_key btree (calendarid),
enddate_ftr_cal_events_key btree (enddate),
eventid_ftr_cal_events_key btree (eventid),
ftr_cal_events_calendarid_key btree (calendarid),
ftr_cal_events_enddate_key btree (enddate),
ftr_cal_events_eventid_key btree (eventid),
ftr_cal_events_startdate_key btree (startdate),
startdate_ftr_cal_events_key btree (startdate),
I'd like to figure out a way to add simple re-occurrances of events.
I can think of two ways,
[a] Using application logic, create a finite number of future
occurrences --- for example, for 10 occurrences, 10 entries into
ftr_cal_events will be created. This seems like an ugly hack.
[b] Create some new table that will be unioned onto my query to list
events. This seems more elegant and manageable in the long run, and
is specifically what I'm asking for some suggestions on.
You see, no matter how hard I think about it, I can't come up with a
*simple* way to do this. Everything I come up with has become
extremely complex.
The two simplest cases to accommodate are events that re-occurr on a
given day of the month (i.e. the 3rd of each month) and events thht
re-occurr on a given day of the week (i.e. every Tuesday).
As simple as those are, I can't come up with a database structure that
accommodates both.
>From a user-interface stand point, my target users expect simplicity
so I'm not trying to re-invent Outlook with it's myriads of options.
I've been googling and I've seen several interesting ideas, but none
use relational SQL for storage and retrieval.
I'm not asking for people to do my work for me, but does someone have
any interesting suggestions that would help me get started? I'll
admit that I'm lacking in my knowledge of the date/interval handling
functions of postgres, so maybe there's some there of great benefit.
Thanks for any advice,
--
Matthew Nuzum | Makers of "Elite Content Management System"
www.followers.net | View samples of Elite CMS in action
[EMAIL PROTECTED] | http://www.followers.net/portfolio/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] Exception handling from trigger
Hello, I would like to handle the exceptions that a database returns. For example, if a "mycolumn" column is defined as varchar(64), I'd like to catch if the new.mycolumn is larger than 64 with my own trigger (for cutoms, internationalized messages, etc ..). So I did, but the database catch this error before my 'before insert'-trigger (using character_length(new.mycolumn) ...). Is this way of handling exceptions possible in postgres ? If so, what is the normal way to handle this exceptions, from a plpgsql/trigger(rule??) perspective ? Thank you ... Vlad Dimitriu ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Exception handling from trigger
> Is this way of handling exceptions possible in postgres ? > If so, what is the normal way to handle this exceptions, from a > plpgsql/trigger(rule??) perspective ? 8.0 should allow you to do this. 7.4 you need to perform your own checks and catch whether they succeed or fail. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Exception handling from trigger
Vlad Dimitriu <[EMAIL PROTECTED]> writes: > I would like to handle the exceptions that a database returns. For example, > if a "mycolumn" column is defined as varchar(64), I'd like to catch > if the new.mycolumn is larger than 64 with my own trigger You can't. From a logical perspective this is sensible, because the trigger is handed data already formed into a tuple. If the presented tuple contained a mycolumn value wider than 64 characters then it would not be a legal value of the rowtype (any more than if, say, the column value were an integer and not a varchar at all). What I'd suggest if you want this is to remove the datatype restriction and instead rely on your trigger to enforce the limitation. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] surrogate keys and replication.
Sad, > Now I solve the GUID problem, with one sequence of IDs on the main server. > The clients ask the server to lease some IDs via special > (application-layer) protocol. Server remembers who and when and what IDs > have took. > (in terms of segments [a..b],[c..d]... etc) Sorry for long delay on this -- I've been out of town. I don't know what the limitations of your client-side technology are. However, I do have to speak against the approach taken by many "GUID" programs -- to assign a unique numberical key, possibly using allocations or random numbers, which contains no other information. When you're looking to replicate or exchange data, there are 4 pieces of extended "meta-data" you need to have for each row: 1) A row key 2) the table to which the row belongs 3) the server on which the "live" copy of the table currently resides, i.e. the server which currently "owns" the row 4) an absolute timestamp of when the row was last changed In situations like this (1) usually does have to be some form of numeric (surrogate) key unless you'll take the trouble to use hashes, just for code simplicity. But you want the other pieces of information clearly in the GUID key; otherwise you need to do a lot of calculation and querying to figure out, when Server 11 wants to update Row 283432 of Table "status", whether it can be done locally or needs to be "exchanged". -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Exception handling from trigger
Tom Lane wrote: You can't. From a logical perspective this is sensible, because the trigger is handed data already formed into a tuple. If the presented tuple contained a mycolumn value wider than 64 characters then it would not be a legal value of the rowtype (any more than if, say, the column value were an integer and not a varchar at all). What I'd suggest if you want this is to remove the datatype restriction and instead rely on your trigger to enforce the limitation. I just realised that. I would design those columns as text and enforce value length restrictions with triggers. It's a false problem: I define the data type for a field and want to check his value after the exception that I can't fit larger data into my field. Thank you, Rod, Tom ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Suggestions on storing re-occurring calendar events
Matt, > I can think of two ways, > [a] Using application logic, create a finite number of future > occurrences --- for example, for 10 occurrences, 10 entries into > ftr_cal_events will be created. This seems like an ugly hack. > > [b] Create some new table that will be unioned onto my query to list > events. This seems more elegant and manageable in the long run, and > is specifically what I'm asking for some suggestions on. Joe Celko talks about this a bit. Either approach is valid, but one way or another you're going to end up creating a lot of rows procedurally. First, what's wrong with approach [a]? The second option [b] is to add a "reference calendar table". This "refcal" will contain all calendar dates for the next 100 years; for convenience, you may also wish to add flags for weekends and holidays (though keep in mind that holiday dates change from year to year in the US). Then, for your repeats, you can query against the refcal for all dates that suit a particular set of circumstances. For example, if something is to repeat every 7 days for 10 times, then reduce the days difference between the parent event and the repeats to an integer, and any date where the modulo is 0 and is less than 70 is a re-occurance. Overall, though, I've found approach [a] to be easier and more convenient. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Exception handling from trigger
I would design those columns as text and enforce value length restrictions with triggers. It's a false problem: I define the data type for a field and want to check his value after the exception that I can't fit larger data into my field. Thank you, Rod, Tom ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] UPDATE FROM problem, multiple updates of same row don't seem to work
Here is an example: CREATE TABLE tablea( id int PRIMARY KEY, flag int ); CREATE TABLE tableb( aid int REFERENCES tablea(id), flag int ); INSERT INTO tablea VALUES(1,0); INSERT INTO tablea VALUES(2,0); -- Flags for 1st row of tablea - When ORed, should be 7 INSERT INTO tableb VALUES(1,1); INSERT INTO tableb VALUES(1,2); INSERT INTO tableb VALUES(1,4); -- Flags for 2nd row of tablea - When ORed, should be 5 INSERT INTO tableb VALUES(2,1); INSERT INTO tableb VALUES(2,4); UPDATE tablea SET flag = tablea.flag | tableb.flag FROM tableb WHERE tablea.id = tableb.aid; SELECT * from tablea; id | flag +-- 1 |1 2 |1 -- Desired output is id | flag +-- 1 |7 2 |5 Is there a way around this so that I can get the desired output? -- David Stanaway <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
