Re: [GENERAL] casting... adding integer to timestamp
Yet another way, this time being more explicit. SELECT play_length - play_seconds * '1 second'::interval Cheers. On Saturday 24 June 2006 22:23, Mark Gibson wrote: > If play_length is a timestamp, I can do this: > > SELECT play_length - INTERVAL '13 seconds' ... > > But what if play_seconds is a column? > > SELECT play_length - INTERVAL 'play_seconds seconds' ... > > This doesn't work. > > ERROR: invalid input syntax for type interval: "play_seconds seconds" > > Can anyone help? > > Thanks, > Mark > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(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: [GENERAL] VACUUM hanging on idle system
Tom Lane wrote: > Um. Extract from the 8.0.5 CVS logs: > > 2005-12-08 14:19 tgl > > * src/backend/: postmaster/bgwriter.c, utils/resowner/resowner.c > (REL8_0_STABLE): Fix bgwriter's failure to release buffer pins and > open files after an error. This probably explains bug #2099 and > could also account for mysterious VACUUM hangups. > > 8.0 branch is currently at 8.0.8 ... Thanks, Tom ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] oh dear - have i messed up my OIDs?
I have a table which required some modifications, but making these changes meant that i had to adjust some functions and types, as well. As the DB is not "live" yet, i simply dumped everything, and copied the bits that needed changes to a new file, made the changes, and ran that. Unfortunately, when it came it the inital data i had (only ~50 rows), i used the part of the script i had created to INSERT them, instead of using the COPY that pg_dump had provided. So, no OIDs. The reason i did this was because i was adding another couple of rows at the same time. This seems to have been a big mistake. Now, any new rows i insert have their primary key beginning back from the beginning. So, i have double primary keys as "1", "2", etc. I realise that i should have left the COPY run in place, after some editing, but at the time it seemed easy to edit the old INSERT statements with a search & replace. My question is, can i safely run my modifications again, but swap out the INSERTs for the COPY WITH OIDs bit from the dump (adding my new INSERTs after)? Would those OIDs still be "good", so to speak? The script i had created for the mods is way too long to add here, but here are some of the statements that i had copied from the dump: -- snip -- SET client_encoding = 'SQL_ASCII'; SET check_function_bodies = false; SET client_min_messages = warning; SET search_path = public, pg_catalog; ALTER TABLE ONLY public.event_discipline DROP CONSTRAINT fk_event_discipline_did; DROP INDEX public.event_discipline_e_id_idx; DROP INDEX public.event_discipline_d_id_idx; ALTER TABLE ONLY public.event_discipline DROP CONSTRAINT event_discipline_e_id_key; DROP TABLE public.event_discipline; DROP TABLE public.event; DROP FUNCTION public.getmonthevents(this_month date, discipline_id integer, region_id integer); DROP FUNCTION public.getmonthevents(this_month date, region_id integer); DROP FUNCTION public.getdateevents(this_date date, discipline_id integer, region_id integer); DROP TYPE public.month_event; DROP TYPE public.date_event; -- snip -- After that, i defined my types, tables, and functions, and added the ALTER statements to grant me permissions. ANY advice appreciated. brian ---(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
Re: [GENERAL] casting... adding integer to timestamp
Michael Glaesemann wrote: On Jun 25, 2006, at 14:23 , Mark Gibson wrote: SELECT play_length - INTERVAL 'play_seconds seconds' ... The column isn't interpolated into the string. Try SELECT play_length - play_seconds * INTERVAL '1 second' That worked great! Thanks! Mark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Casting and Timestamp
I have unexpected results when trying to cast a string to a timestamp: test=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00 GMT-5' test-# ; timestamptz 2006-06-24 20:00:00-05 Seems that what I get is about 10 hours earlier than I expect... Any ideas why this is happening? Did I assume too much in the cast? Thanks, Mark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Casting and Timestamp
Mark Gibson wrote: I have unexpected results when trying to cast a string to a timestamp: test=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00 GMT-5' test-# ; timestamptz 2006-06-24 20:00:00-05 Seems that what I get is about 10 hours earlier than I expect... Any ideas why this is happening? Did I assume too much in the cast? Answered my own question - apparently the 'GMT' portion isn't understood by postgresql: template1=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00-05' template1-# template1-# ; timestamptz 2006-06-25 06:00:00-05 (1 row) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Casting and Timestamp
Mark Gibson <[EMAIL PROTECTED]> writes: > I have unexpected results when trying to cast a string to a timestamp: > test=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00 GMT-5' > test-# ; >timestamptz > > 2006-06-24 20:00:00-05 IIRC, the semantics of the 'GMT+-x' annotation is defined by a POSIX standard that has the opposite sign convention to what the SQL committee adopted. So 'foo-05' in timestamptz output means 'foo GMT+5' in the POSIX notation (ie, in both cases "5 hours west of Greenwich" is meant). Yeah, it sucks ... want to arrange a standards-committee shootout? One reference among many: http://www.twinsun.com/tz/tz-link.htm says Numeric time zone abbreviations typically count hours east of UTC, e.g., +09 for Japan and -10 for Hawaii. However, the POSIX TZ environment variable uses the opposite convention. For example, one might use TZ="JST-9" and TZ="HST10" for Japan and Hawaii, respectively. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Idea for vacuuming
The verbose output shows the table being vacuumed last. Maybe it changed after 8.0 Greg Stark wrote: Jim Nasby <[EMAIL PROTECTED]> writes: My RFE: When vacuuming a table, pg should try to vacuum the primary key first. If that results in 0 recovered entries, then assume the table has no updates/deletes and skip the rest of that table. That makes no sense. Vacuum starts by scanning the table itself, not the indexes. It only goes to the indexes after it has found tuples that need cleaning up. There's nothing to look at in the indexes that would tell it whether there are any tuples to clean up. ---(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
