Re: [SQL] getting the current date
--- Joseph Syjuco <[EMAIL PROTECTED]> wrote: > > how can i get the current date (without the time > part) in sql. --> try SELECT current_date(); ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Slow performance on MAX(primary_key)
On Thursday 17 Oct 2002 12:46 am, Keith Gray wrote: > Getting MIN() adn MAX() seems fairly trivial to me. > > When is on an index or more importantly Primary > Key it must be a common SQL. > > Would it be possible in the code to look at > the field in MIN() or MAX() and if it is > indexed use a similar method to the suggested > SQL work around? > > Can I help this to happen? Subscribe to pgsql-hackers and talk through a proposed hack there. The developers always seem happy to receive contributions (you've just got to look at the contrib folder for evidence). -- Richard Huxton ---(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] Use of reference table ('look-up-table') and FK constraint
Josh & Stephan, This helps a lot - thanks! Any idea what datatype 'Identifier_type IDENTITY' specifies? I don't see this in the postgres documentation. I'll see what I can find in sybase lingo. CREATE TABLE phone ( phone_id Identifier_type IDENTITY, phone_number varchar(20) NOT NULL, community_id numeric(12) NOT NULL, type varchar(10) NULL REFERENCES phone_types(type) ON DELETE SET NULL ); regards, Charles ---(end of broadcast)--- TIP 3: 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] functions that return a dataset or set of rows
"Brian Ward" <[EMAIL PROTECTED]> wrote in message news:aofqbd$10v5$1@;news.hub.org... > How do I create a function that returns a set of row; > > I can't seem to find the datatype that the return set should be declared as. You should wait for Postgres 7.3 currently in beta. Ciao Gaetano ---(end of broadcast)--- TIP 3: 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] Messy Casts, Is there a better way?
Larry, > If I do that, and enter '72 months' in the contract_term field, how > can > I convince PostgreSQL to output the interval back in months? It > wants > to give me '5 Years'. That's on the TODO list, I'm afraid. From the sound of it, you should stick with your current schema and custom function. -Josh Berkus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Messy Casts, Is there a better way?
On Thu, 2002-10-17 at 17:50, Josh Berkus wrote: > Larry, > > > If I do that, and enter '72 months' in the contract_term field, how > > can > > I convince PostgreSQL to output the interval back in months? It > > wants > > to give me '5 Years'. > > That's on the TODO list, I'm afraid. From the sound of it, you should > stick with your current schema and custom function. Thanks. At least I know I'm not missing something here. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] object oriented vs relational DB
Perhaps a mistake to ask this here (heresy I suspect) but I would be interested in the perspectives of people who make a living at this. Background: I am a molecular biologist trying to build a database containing genomic data, strain details, community info (people, publications etc). Everything except the genomic data is currently housed in an object-oriented acedb database. In conversations with other groups who are going through the same transition we are someone stated that: ".is perhaps evolving its schema _more_ now that sequencing is finished and annotation is really picking up speed, you should not think that the changes are going to get less ! You will have to deal with problems like genes that get renamed but you still need the old name around, how to cross reference oligos to many parts of sequence, how to organise RNAi data that may end up matching multiple chromosomes etc. etc. I don't want to put you off, but as far as I aware, constantly evolving schema is not one of relational databases strengths." Their solution is to maintain a mixed system of acedb and a relational db: where the working dbs are all acedb and the web site is a mixture of an acedb server and a relational relational db. The data in the mysql database is directly derived from the acedb reference database. Any thoughts? regards, Charles ---(end of broadcast)--- TIP 3: 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] Use of reference table ('look-up-table') and FK constraint
On 17 Oct 2002, Charles Hauser wrote: > Josh & Stephan, > > This helps a lot - thanks! > > Any idea what datatype 'Identifier_type IDENTITY' specifies? I don't > see this in the postgres documentation. I'll see what I can find in > sybase lingo. As a rough guess, I'd say probably a serial column, "phone_id serial primary key" is likely. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] object oriented vs relational DB
Charles, > ".is perhaps evolving its schema _more_ now that sequencing is > finished and annotation is really picking up speed, you should not > think that > the changes are going to get less ! You will have to deal with > problems like > genes that get renamed but you still need the old name around, how to > cross > reference oligos to many parts of sequence, how to organise RNAi data > that may > end up matching multiple chromosomes etc. etc. I don't want to put > you off, but > as far as I aware, constantly evolving schema is not one of > relational databases > strengths." All of the above issues are easily solvable by a SQL expert. "You show me somebody preaching about the 'limitations' of relational databases, and I'll show you somebody with a poor knowledge of the SQL standard, or who is using an inferior SQL RDBMS." > Their solution is to maintain a mixed system of acedb and a > relational > db: where the working dbs are all acedb and the web site is a mixture > of > an acedb server and a relational relational db. The data in the > mysql > database is directly derived from the acedb reference database. > > Any thoughts? Hey, if it works for them. Apparently they have an AceDB expert on staff, but no SQL expert on staff. Greater technology decisions than the above have been made on that basis -- you have to work with the staff you have. Just don't let them justify their *pragmatic* technology decision with a bunch of theoretical BS. Now, for the soapbox. (YMMV): I've looked into OODBMS for my business. However, I've kept from using any in production for one simple reason: lack of a standard. There is no international standard for OODBMS, meaning that each OODBMS is its own animal and databases are not at all portable between different software packages. Nor is your knowledge of one OODBMS even 20% tranferrable to another. In comparison to relational databases, this is like turning the clock back to 1980, when every database implementation was idiosyncratic and using two database systems in the same enterprise was darn near impossible. While modern programming languages make this easier through middeware than it was then, it's still not a desirable situation. I'll check on the progress of OODBMS in another couple of years. Who knows, ANSI might codify OODB-UML as a standard, and then we'll have something to build on. But until then ... the RDBMS model has been around for 32 years, and the SQL standard for 20, and that a lot of accumulated wisdom to throw away casually. -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] object oriented vs relational DB
On Thursday 17 Oct 2002 4:03 pm, Josh Berkus wrote: > Charles, > > > as far as I aware, constantly evolving schema is not one of > > relational databases > > strengths." > > All of the above issues are easily solvable by a SQL expert. "You > show me somebody preaching about the 'limitations' of relational > databases, and I'll show you somebody with a poor knowledge of the SQL > standard, or who is using an inferior SQL RDBMS." A "constantly evolving schema" is a limitation of an RDBMS to be fair. What seems to be missed by people who have problems with this is that a constantly evolving schema is a sign of design problems too. The schema is supposed to represent the *meaning* of your data - if the schema is evolving your analysis was incorrect at the start. How would you feel about a library with "constantly evolving categories" for filing its books? Anyway Charles, I agree with Josh's comments and just thought I'd point you at the following site that has plenty of discussion on relational vs object - oriented database systems. http://www.dbdebunk.com/ -- Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Messy Casts, Is there a better way?
On Tue, 2002-10-15 at 13:54, Peter Eisentraut wrote: > Larry Rosenman writes: > > > I have a table with the following, in part: > > > > contract_start date > > contract_term int (term in MONTHS) > > Store contract_term as interval? If I do that, and enter '72 months' in the contract_term field, how can I convince PostgreSQL to output the interval back in months? It wants to give me '5 Years'. I can't seem to find a function for that. > > -- > Peter Eisentraut [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
