[SQL] how to use a date range in a join
trying to do something like select d.day, c.name from [dates between day1 and day2] d left join c.some_table; but cannot figure out what to put into the brackets. Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097 ---(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] how to use a date range in a join
chester c young <[EMAIL PROTECTED]> schrieb: > trying to do something like > > select d.day, >c.name > from [dates between day1 and day2] d > left join c.some_table; > > but cannot figure out what to put into the brackets. Perhaps something like this: test=# select current_date + (generate_series(1,10)||'day')::interval; ?column? - 2007-03-13 00:00:00 2007-03-14 00:00:00 2007-03-15 00:00:00 2007-03-16 00:00:00 2007-03-17 00:00:00 2007-03-18 00:00:00 2007-03-19 00:00:00 2007-03-20 00:00:00 2007-03-21 00:00:00 2007-03-22 00:00:00 (10 rows) If not, sorry, then please define your problem. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Installing with libs of postgresql-libs
Hi list, Could someone tell me what libs are packed in the rpm below ? postgresql-libs-8.2.3-1PGDG.i686.rpm We are concerned about the problem of compatibility with Oracle and we prefer to check all the libs to not put the Oracle system down. I think it is just a metter of rpm -qa postgresql-libs-8.2.3-1PGDG.i686.rpm but I do not have the adm permissions. Any help would be glad Ezequias ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Installing with libs of postgresql-libs
<[EMAIL PROTECTED]> writes: > Could someone tell me what libs are packed in the rpm below ? > postgresql-libs-8.2.3-1PGDG.i686.rpm If it is somewhere accessible through HTTP: rpm -qpl http://url-to-it/postgresql-libs-8.2.3-1PGDG.i686.rpm If it is local: rpm -qpl postgresql-libs-8.2.3-1PGDG.i686.rpm > I think it is just a metter of rpm -qa postgresql-libs-8.2.3-1PGDG.i686.rpm > but I do not have the adm permissions. You don't need them to query or list the contents of uninstalled packages. -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] A form of inheritance with PostgreSQL
Hello, and thank you to Steven and everyone else that submitted input on this issue. After reading a few more methods of doing things, I went with the simplest one, as 1. time is of the essence, and 2. I'm stuck with PostgreSQL 7.1 on the server I have to develop for. I set the primary key of the parent class to a serial. Children have an integer column with constraints as the primary key and foreign key to the parent primary key column. Thanks again, - Greg Steve Midgley wrote: Hi Greg, While not in a C++ framework, you might find that it's not too hard to implement something similar in your system - It's called "Single Table Inheritance." References to the Ruby on Rails implementation here: http://wiki.rubyonrails.org/rails/pages/SingleTableInheritance It's based on Martin Fowler's Patterns of Enterprise Architecture book - please find references to his original patterns here: http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html The key, I believe, is simply adding a "type" and a "parent_id" to the "class" table, so you can model all your types and their hierarchical relations. Fowler's diagram is pretty clear. I think then you would store the data in another table (or tables) and link into this inheritance structure to establish ancestry for any piece of data (some people try to store the data in this table too, but I think that's a mistake personally). If I understand what you're trying to do, you can use this design pattern in your application language to implement an inheritance scheme without any special database features (i.e. in a SQL-standard manner). I hope this is helpful, Steve At 12:28 AM 3/9/2007, [EMAIL PROTECTED] wrote: Date: Thu, 08 Mar 2007 13:01:51 -0500 From: Greg Toombs <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Subject: A form of inheritance with PostgreSQL Message-ID: <[EMAIL PROTECTED]> Hello. I'm trying to figure out how to nicely implement a C++ class-like system with PostgreSQL. Consider the following: Tables Fruit, Apple, Orange I want to design the foreign key scheme such that there are relations between fruit and apple, and fruit and orange, that imply that apple is a fruit, and orange is a fruit. I don't want to eliminate the existence of Apple and Orange tables, because there will be columns specific to both Apple and Orange; if I include these columns in Fruit, then if Fruit is an Orange, the Apple columns will be needlessly present in Apple rows. The different ways of implementing this scheme that I've thought of (some uglier than others): - Have Fruit contain foreign keys to both Apple and Orange, and write a check constraint in Fruit specifying that exactly one of (Apple FK, Orange FK) needs to be non-null. The disadvantage of this method is that it isn't exactly loosely coupled. For every other fruit type table I implemented I'd have to go back and add a foreign key in Fruit. - Have a foreign key in Apple to Fruit, and in Orange to Fruit; then somehow create a constraint that imposes uniqueness on the union of foreign keys in both Apple and Orange. To figure out what type of fruit a Fruit row is, run a query for foreign keys in Orange and Apple matching the primary key of Fruit. You'd also want to somehow create a constraint that the result of this query should always return exactly one row (perhaps with a trigger?) Any advice will be appreciated! As I'm relatively new to Postgre, I might need some help with the actual implementation as well. Thank you. - Greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] A form of inheritance with PostgreSQL
Greg Toombs <[EMAIL PROTECTED]> writes: > After reading a few more methods of doing things, I went with the > simplest one, as 1. time is of the essence, and 2. I'm stuck with > PostgreSQL 7.1 on the server I have to develop for. Egad. *Please* do not tell us you are intending to use 7.1 for production ... if some PHB is trying to force that on you, I suggest resigning from the project before you get blamed for the inevitable disaster. regards, tom lane ---(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] A form of inheritance with PostgreSQL
What disaster do you foresee? Is that version unstable? Tom Lane wrote: Greg Toombs <[EMAIL PROTECTED]> writes: After reading a few more methods of doing things, I went with the simplest one, as 1. time is of the essence, and 2. I'm stuck with PostgreSQL 7.1 on the server I have to develop for. Egad. *Please* do not tell us you are intending to use 7.1 for production if some PHB is trying to force that on you, I suggest resigning from the project before you get blamed for the inevitable disaster. regards, tom lane
Re: [SQL] A form of inheritance with PostgreSQL
Greg Toombs wrote: > What disaster do you foresee? Is that version unstable? Yes. There are known, unfixed bugs, and architectural problems that cannot be fixed. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] A form of inheritance with PostgreSQL
Hoookay. I'm currently negotiating with IX Webhosting to upgrade their prehistoric software. Fellows, consider this a word of warning that, if they don't upgrade, anyone wanting to use this host will be stuck with PostgreSQL 7.1.3. If they do upgrade, I'll happily revoke this warning. Anyway, thanks for the heads-up. Alvaro Herrera wrote: Greg Toombs wrote: What disaster do you foresee? Is that version unstable? Yes. There are known, unfixed bugs, and architectural problems that cannot be fixed.
Re: [SQL] A form of inheritance with PostgreSQL
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Greg Toombs wrote: >> What disaster do you foresee? Is that version unstable? > Yes. There are known, unfixed bugs, and architectural problems that > cannot be fixed. We abandoned maintenance of the 7.1 branch in 2001. 7.2 was the first version that anyone thought was good enough to maintain over the long haul (mainly because it was the first version that wouldn't crash and burn at 4G transactions), and even that was abandoned in 2005 because of its unfixable-without-initdb security bugs. I don't know of anyone who would recommend today that you undertake new development on a server version older than 8.1.x. regards, tom lane ---(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
