Re: [SQL] Querying date_time for date only ?
Aarni Ruuhim?ki wrote:
> Hello List,
>
> I have a time stamp without time zone field, -MM-DD hh:mm:ss, in
> my table. I want to also find something just for a particular day
> regardless of the time.
>
> (Pg)SQL way to do this ?
Yes. You can use date_trunc():
test=> select date_trunc('day', '2004-01-04 04:02:03'::timestamp);
date_trunc
-
2004-01-04 00:00:00
(1 row)
so it would be date_trunc('day', col) = '2004-01-05'. The problem with
this is that you cannot use an index unless you create an expression
index on the date_trunc() function call. Another option is to do
something like:
WHERE col >= '2004-01-04' AND col < '2004-01-05'
If the date isn't a constant, you have to use date_trunc() on those, and
add one day to the second comparison:
WHERE col >= date_trunc('day', col2) AND
col < date_trunc('day', col2) + '1 day';
--
Bruce Momjian| http://candle.pha.pa.us
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to increase row deletion efficiency?
Have only just had a chance to try this. There were several missing indexes on the referencing columns including one on a table of circa 150K records. It now completes in a couple of minutes. Also it appears to be quicker if one doesn't defer the constraints. Thanks for the advice. Regards, Alex. Tom Lane wrote: Alexander Stanier <[EMAIL PROTECTED]> writes: I am currently trying to separate two environments contained in one database. Essentially I need to break that one database down into two with a portion of the data going to each new database. I am intending to achieve this by duplicating the database and then stripping out the data that is not required in each database. I have started by trying to delete data from a set of 28 related tables, however the performance appears to be terrible. I am deleting from a table called document which cascades down to 27 tables underneath it linked by various cascading foreign key constraints. Some of these subsidiary tables have as many as a couple of million records. Do you have indexes on the referencing columns? PG enforces an index on the referenced column, but not on the other end, and DELETE is where it will hurt if you haven't got one. regards, tom lane
Re: [SQL] Does VACUUM reorder tables on clustered indices
On Wed, Dec 21, 2005 at 06:36:45PM -0500, Chris Browne wrote: > If I'm considering clustering the Slony-I "sl_log_1" table, forcing it > into memory *is* something I'll consider doing in order to minimize > the time that would-be writers are blocked from writing... Given what Tom Lane recently reported (and assuming I understood his remarks), I think it's a Mighty Bad Idea to CLUSTER sl_log_1. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
