[SQL] Check set of date intervals
Hi all, have such relation A: PERIOD_ID | DATE_START | DATE_END | OTHER_ATTRIBUTES... 1 | 01.01.2010 | 01.02.2010 2 | 03.02.2010 | 04.03.2010 . .. I want to search among periods for the set of periods which completely covers passed search period. As example - if I pass search period '15.01.2010 to 15.02.2010' the result set must be empty because there is gap between 01.02 and 03.02. please, suggest an idea how to implement this in SQL without writing a procedure. Thanks, Anton
[SQL] checking the gaps in intervals
Hi dear community, Have probably quite simple task but cannot find the solution, Imagine the table A with 2 columns start and end, data type is date start end 01 dec. 10 dec 11 dec. 13 dec 17 dec. 19 dec . If I have interval, for example, 12 dec-18 dec, how can I determine that the interval cannot be fully covered by values from table A because of the gap 14-16 dec? Looking for solution and unfortunately nothing has come to the mind yet... Thanks, Anton -- 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] sql basic question
Do the child Select min, max from... Group by side Then you can do whatever is required... Thanks, Anton On Dec 28, 2012, at 12:23, Antonio Parrotta wrote: Hi All, I have this table: LABEL ID Distance SIDE "15"; 119006; 0.10975569030617;1 "14"; 64054;0.118448307450912; 0 "16"; 64055;0.176240407317772; 0 "20"; 64057;0.39363711745035;0 "19"; 64056;0.41205442839764;1 "24"; 119007; 0.59758734628752;0 What I want to achieve is a result table with min and max distance for each side, limiting to 2 (basically the boundaries of each SIDE) so the result should be: LABELID Distance SIDE "15"; 119006; 0.10975569030617;1 "19"; 64056;0.41205442839764;1 "14"; 64054;0.118448307450912; 0 "24"; 119007; 0.59758734628752;0 Thanks a lot - Antonio y
Re: [SQL] sql basic question
Antonio, but then you can do join between minmax select and source table by distance and get required columns... Thanks, Anton On Dec 28, 2012, at 12:43, Antonio Parrotta wrote: Hi Anton, I need column LABEL and ID as well. By grouping on SIDE these column cannot be included in the query. Thanks - Antonio On 28 December 2012 12:38, Anton Gavazuk wrote: > Do the child Select min, max from... Group by side > > Then you can do whatever is required... > > Thanks, > Anton > > On Dec 28, 2012, at 12:23, Antonio Parrotta > wrote: > > Hi All, > > I have this table: > LABEL ID Distance SIDE > "15"; 119006; 0.10975569030617;1 > "14"; 64054;0.118448307450912; 0 > "16"; 64055;0.176240407317772; 0 > "20"; 64057;0.39363711745035;0 > "19"; 64056;0.41205442839764;1 > "24"; 119007; 0.59758734628752;0 > > What I want to achieve is a result table with min and max distance for > each side, limiting to 2 (basically the boundaries of each SIDE) > > so the result should be: > LABELID Distance SIDE > "15"; 119006; 0.10975569030617;1 > "19"; 64056;0.41205442839764;1 > "14"; 64054;0.118448307450912; 0 > "24"; 119007; 0.59758734628752;0 > > > Thanks a lot > > > - Antonio > y > >
Re: [SQL] Setting a default value for a select statement without results
Hi Jorge, Look on http://www.postgresql.org/docs/8.1/static/functions-conditional.html Thanks, Anton On Feb 6, 2013, at 0:23, JORGE MALDONADO wrote: > I have an UPDATE query with the following general structure: > > UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY > order_field LIMIT 1) > > Is it possible to assign a default value in case no results are returned by > the SELECT statement? > > Respectfully, > Jorge Maldonado -- 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] Correct implementation of 1:n relationship with n>0?
Hi, Can you explain what you are trying to achieve because it's not clear... There are 2 types of relationships which might be used in your case: 1) unidirectional relationship from list_item to list through foreign key on list 2) bidirectional relationship implemented through join table which contains references between both tables These are pretty standard generic techniques applied many times and don't require any "programming" Thanks, Anton On Apr 30, 2013, at 16:39, Wolfgang Keller wrote: > It hit me today that a 1:n relationship can't be implemented just by a > single foreign key constraint if n>0. I must have been sleeping very > deeply not to notice this. > > E.g. if there is a table "list" and another table "list_item" and the > relationship can be described as "every list has at least one > list_item" (and every list_item can only be part of one list, but > this is trivial). > > A "correct" solution would require (at least?): > > 1. A foreign key pointing from each list_item to its list > > 2. Another foreign key pointing from each list to one of its list_item. > But this must be a list_item that itself points to the same list, so > just a simple foreign key constraint doesn't do it. > > 3. When a list has more than one list_item, and you want to delete the > list_item that its list points to, you have to "re-point" the foreign > key constraint on the list first. Do I need to use stored proceures > then for all insert, update, delete actions? > > (4. Anything else that I've not seen?) > > Is there a "straight" (and tested) solution for this in PostgreSQL, that > someone has already implemented and that can be re-used? > > No, I definitely don't want to get into programming PL/PgSQL myself. > especially if the solution has to warrant data integrity under all > circumstances. Such as concurrent update, insert, delete etc. > > TIA, > > Sincerely, > > Wolfgang > > > -- > 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] Advice on key design
Hi Jorge, In your case it would be lpp_id as PK, and lpp_person_id,lpp_language_id as unique constraint Thanks, Anton On Jul 23, 2013, at 23:45, JORGE MALDONADO wrote: > I have 2 tables, a parent (tbl_persons) and a child > (tbl_languages_per_person) as follows (a language table is also involved): > > -- > tbl_persons > -- > * per_id > * per_name > * per_address > > -- > tbl_languages_per_person > -- > * lpp_person_id > * lpp_language_id > * lpp_id > > As you can see, there is an obvious key in the child table which is > "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a > unique key which is a field that contains a consecutive number of type serial. > > My question is: what should I configure as the primary key, "lpp_person_id + > lpp_language_id" or "lpp_id"? > Is the role of a primary key different from that of a unique index? > > With respect, > Jorge Maldonado > > > > > > -- 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] Advice on key design
The reason is simple - as you need the artificial PK lpp_id, then everything else becomes an constraint Thanks, Anton On Jul 24, 2013, at 0:28, JORGE MALDONADO wrote: >> In your case it would be lpp_id as PK, and >> lpp_person_id,lpp_language_id as unique constraint >> >> Thanks, >> Anton Is there a reason to do it the way you suggest? Regards, Jorge Maldonado On Tue, Jul 23, 2013 at 5:02 PM, Anton Gavazuk wrote: > Hi Jorge, > > In your case it would be lpp_id as PK, and > lpp_person_id,lpp_language_id as unique constraint > > Thanks, > Anton > > On Jul 23, 2013, at 23:45, JORGE MALDONADO wrote: > > > I have 2 tables, a parent (tbl_persons) and a child > (tbl_languages_per_person) as follows (a language table is also involved): > > > > -- > > tbl_persons > > -- > > * per_id > > * per_name > > * per_address > > > > -- > > tbl_languages_per_person > > -- > > * lpp_person_id > > * lpp_language_id > > * lpp_id > > > > As you can see, there is an obvious key in the child table which is > "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a > unique key which is a field that contains a consecutive number of type > serial. > > > > My question is: what should I configure as the primary key, > "lpp_person_id + lpp_language_id" or "lpp_id"? > > Is the role of a primary key different from that of a unique index? > > > > With respect, > > Jorge Maldonado > > > > > > > > > > > > >
