Re: [SQL] date infinity
Ferruccio Zamuner <[EMAIL PROTECTED]> writes: > expire date default 'infinity'); > ERROR: Unrecognized date external representation 'infinity' infinity is a timestamp (or datetime) feature, not a date feature. regards, tom lane
RE: [SQL] Date/Time problem -(((
Hi Boris,
I know Kaare Rasmussen posted a URL to the online documentation already,
but this might be a direct solution to your problem:
DELETE FROM onlineuser WHERE datum < ('now'::datetime - '5
minutes'::interval);
Hope this helps
Francis Solomon
> Hello
>
> I am moving from MySQL to Postgres and while converting my sql code I
> have a lot of problems with this construction:
>
> ($ze is current time - 300 secounds):
>
> delete from onlineuser where datum < FROM_UNIXTIME($ze)
>
> Is there any Date/Time function in PGSQL? I want as shown in this
> example delete entries older than 300 secounds.
>
> I postet to the list because I have found no solution in the
> tutorials and search-engine (maybe searched with wrong words?)
>
> It would be great if someone could help me / point me to the right
> way...
>
> --
> Boris
[SQL] Re[2]: Date/Time problem -(((
Hello Francis,
Tuesday, January 02, 2001, 3:23:49 AM, you wrote:
FS> Hi Boris,
FS> I know Kaare Rasmussen posted a URL to the online documentation already,
FS> but this might be a direct solution to your problem:
FS> DELETE FROM onlineuser WHERE datum < ('now'::datetime - '5
FS> minutes'::interval);
FS> Hope this helps
FS> Francis Solomon
Yes this helps me a lot thanks
--
Boris
Re: [SQL] Looking for comments
Thomas SMETS wrote: > > > ISBN's have a checkdigit; it would be sensible to provide a > function to be used in a CHECK constraint to ensure that the > ISBN is valid. > Here's a URL with more information:http://www.isbn.spk-berlin.de/html/userman/usm4.htm. I've written a similar algorithm in C to create and verify checksums for eight digit codes. The final code is then 9 digits. I did this to help create codes that are the same length as a social security number. If you'd like a copy, let me know. -Ron-
Re: [SQL] Looking for comments
Thomas SMETS writes: > > ISBN's have a checkdigit; it would be sensible to provide a > function to be used in a CHECK constraint to ensure that the > ISBN is valid. > > > OK I'll start searching for it but haven't implemented yet In the PostgreSQL distribution there's a directory contrib/isbn_issn that defines a couple of data types that might help you. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] sql99 / sql3
I just recieved SQL in a Nutshell from O'Reilly. I bought the book because it covers the SQL99 standard. It's my understanding that this is the most recent SQL standard. Am I correct about this? What is the current status of SQL standards? What is the most recent approved standard, and what aspects of SQL are still under discussion? I ask, because I notice that the book doesn't mention recursive unions. In fact I don't see mention of unions at all, nevermind recursive unions, so this may simply be an oversight. However, since this is a feature I've really been looking forward to, I'm now wondering if I'm hoping in vain. What organization is currently responsible for drafting SQL standards? How can I obtain publications describing those standards? Is there anything comprehensive and definitive online? -Ron-
[SQL] About Index
Hi, I have created a table songs as follwoing: CREATE TABLE songs ( song_id serial NOT NULL, title character varying(50) NOT NULL, composer varchar(50), performer varchar(50), artist_id int4 not null, file_size int4, description text, extrInfo text, highmd5 text, lowmd5 text, PRIMARY KEY (song_id), constraint fk_songs_art foreign key( artist_id) references artist ); I also created an other index on attribute title as: create unique index title_idx on songs(title); Then I vacuum analyzed my data base. On command line I type : explain select title from songs where song_id between 1 and 15 order by song_id; NOTICE: QUERY PLAN: Sort (cost=1.70..1.70 rows=14 width=16) -> Seq Scan on songs (cost=0.00..1.44 rows=14 width=16) EXPLAIN One thing I don't uderstand why query planer is using a seq scan on my table. Why it is not using indexing defined. Thank you all for all your help. Regards. Najm
[SQL] FUNCTION returing multiple rows
I have seen in the postings that it is not possible to return multiple rows from a function. However, when I look at the programmers guide (Ch. 4), it almost leads me to believe otherwise: -8<--- The following more interesting example takes a single argument of type EMP, and retrieves multiple results: select function hobbies (EMP) returns set of HOBBIES as 'select (HOBBIES.all) from HOBBIES where $1.name = HOBBIES.person' language 'sql'; -8<--- I know that it only says 'multiple results' but with the 'HOBBIES.all', it almost leads me to believe that it is retrieving all of the fields. Is 'all' a keyword, or is this just one field in HOBBIES? Also, is there any type of function return multiple rows (plpgsql, pltcl, etc)? If not, then is there anyway to fake it out to do this. Could I concatenate the fields together to form one field per row. This would look like multiple results of 1 field to PG. Also, if I did this, is there a delimiter I should use? for example: select field1 || ':' || field2 || ':' || field3 from some_table; Where ":" is the delimiter to send back. Sorry for this question, but I am porting over an app from Oracle, and they do not want to change the java code that calls this; so I need to make this work somehow. thanks, --brett
[SQL] Release date for 7.5?
Hi all. I'm hoping to migrate from mysql to postgresql but need some of the features in 7.5. Any idea when it will be out? Thanx, Mike Diehl.
Re: [SQL] Release date for 7.5?
I assume you mean 7.1. We are in beta now. That usually takes a month. [ Charset ISO-8859-1 unsupported, converting... ] > Hi all. > > I'm hoping to migrate from mysql to postgresql but need some of the features > in 7.5. Any idea when it will be out? > > Thanx, > Mike Diehl. > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Release date for 7.5?
On Tue, 2 Jan 2001, Diehl, Jeffrey wrote: > Hi all. > > I'm hoping to migrate from mysql to postgresql but need some of the features > in 7.5. Any idea when it will be out? well, we have to get through 7.1, 7.2, 7.3 and 7.4 first ... at a 6mo turnaround on each release, sometime in '03? :) seriously though, i fyou mean 7.1, beta2 is schedualed for this Friday, if nothing comes up between now and then ... release is looking like early Feb at this rate ...
[SQL] Weird problem with script...
Hi, I'm building a script to create the tables in my database. Everything works fine except one thing with this part of my script: create table tbl_resume_free_text_type ( type_id int, type text ); -- insert into tbl_resume_free_text_type (type_id, type) values (1, 'Hobbies'); -- insert into tbl_resume_free_text_type (type_id, type) values (2, 'Special Talents'); If I uncomment the two insert statements I get an error message stating that tbl_resume_free_text_type doesn't exist. With the lines commented out the table gets created (and the rest of the script runs error-free) and I can manually insert these rows afterwards without a problem... I'm running PSQL 7.0.3. Any ideas? Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6999 North Vancouver, BC, V7M 2J5
Re: [SQL] Rules
Peeter Smitt wrote: > Hi > > I'm trying to make updateable view useing rules. > > CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD > SELECT fun1(new); > > Thing is that backend gives this error. > ERROR: parser: parse error at or near ")" > > What i'm doing wrong? Are there any other ways to make updatebale multi-table views? There are, but you haven't read the documentation about the rule system, which is a common mistake. Read chapter 8 of the PostgreSQL Programmers manual for details on how to setup updateable views. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] Weird problem with script...
[EMAIL PROTECTED] writes: > I'm building a script to create the tables in my database. Everything > works fine except one thing with this part of my script: > create table tbl_resume_free_text_type ( > type_id int, > type text > ); > -- insert into tbl_resume_free_text_type (type_id, type) values (1, 'Hobbies'); > -- insert into tbl_resume_free_text_type (type_id, type) values (2, 'Special >Talents'); > If I uncomment the two insert statements I get an error message > stating that tbl_resume_free_text_type doesn't exist. Odd. The three statements work just fine for me when executed by hand in 7.0.3. Anyone else able to reproduce a problem? regards, tom lane
[SQL] order by day or month, etc
Hello first time I post something... good morning everyone! short presentation: Leo Xavier, Lisbon - Portugal, 17 years, my home-made site: www.megabenfica.com Sql7, win 2000... The question: How for example do I select all entrys from a certain month (of a certain year, of course) ? Or from a certain day? And how from a certain period, between day X and Y, i.e? The solution i found is to create three columns in the table: one with the day, a second with the month, a thir with the year... but this really is a little bit unprofessional ... doing this: SELECT to_char(field, 'DD/MM/') AS new_date as Francis Solomon said, didnt work... "unrecognized function" ... can anyone help me?? Leo Xavier
Re: [SQL] FUNCTION returing multiple rows
Brett Schwarz <[EMAIL PROTECTED]> writes: > However, when I look at the programmers guide (Ch. 4), it almost leads me to > believe otherwise: > The following more interesting example takes a single argument of type EMP, > and retrieves multiple results: > select function hobbies (EMP) returns set of HOBBIES > as 'select (HOBBIES.all) from HOBBIES > where $1.name = HOBBIES.person' > language 'sql'; This bit of the docs seems not to have been updated since PostQuel days. The ".all" syntax is definitely out of date --- ".*" is the SQL equivalent. There is a descendant of this example in the current regression tests: CREATE FUNCTION hobbies(person) RETURNS setof hobbies_r AS 'select * from hobbies_r where person = $1.name' LANGUAGE 'sql'; (Note that `person' is being used in two ways here, first as a datatype and then as a field name --- not the least confusing example I could have thought of...) > Also, is there any type of function return multiple rows (plpgsql, pltcl, > etc)? In 7.0 and prior releases, *only* SQL functions can return multiple rows. The 7.1 function manager removes that hardwired assumption, but in 7.1 the plpgsql, pltcl, plperl function language handlers don't take advantage of the capability --- so the current state of affairs is that you can do it in SQL or C functions only. Hopefully this will be improved in future releases. regards, tom lane
Re: [SQL] order by day or month, etc
Hello Leo, which version of PostgreSQL are you running? I tested: select to_char(abm_dat, 'DD/MM/') AS new_date from t_dummy; And it works fine: new_date 31/03/1992 15/06/1994 11/09/1993 19/11/1993 ... Regards, Jens Leo Xavier schrieb: > [...] > doing this: > SELECT to_char(field, 'DD/MM/') AS new_date > > as Francis Solomon said, didnt work... "unrecognized function" ... > [...] = Jens Hartwig - debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil: +49 (0)170 167-2648 E-Mail : [EMAIL PROTECTED] =
