Re: [SQL] How do I convice postgres to use an index?
Try casting the constant to the type of the field, i.e. WHERE timestamp >= '7/12/2004'::"timestamp without time zone" (iirc the quotes are necessary) Also, I'd try to avoid naming attributes like (built-in) types. (iirc "timestamp" is a type without time zone in 7.3, and with time zone in 7.4 :) "timestamptz" is the opposite in both cases) G. %--- cut here ---% \end - Original Message - From: "Vic Ricker" <[EMAIL PROTECTED]> Sent: Tuesday, July 13, 2004 10:29 PM > I apologize for the following stupid question. I have been doing some > searching and haven't found anything really helpful. > > The problem is that postgres (7.4.2) keeps choosing to do a sequential > scan on a table when an index scan would be significantly faster. > > The queries that I'm using look at daily statistics from events logged > by our Checkpoint firewall and generate graphs. Since they are bit > complicated, I simplified it to "select count(*) from log where > timestamp>='7/12/2004'" for testing. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How do I convice postgres to use an index?
O kyrios SZUCS Gαbor egrapse stis Jul 15, 2004 : > Try casting the constant to the type of the field, i.e. > > WHERE timestamp >= '7/12/2004'::"timestamp without time zone" > > (iirc the quotes are necessary) > > Also, I'd try to avoid naming attributes like (built-in) types. (iirc > "timestamp" is a type without time zone in 7.3, and with time zone in 7.4 :) ^^ Are you sure about it?? > "timestamptz" is the opposite in both cases) > > G. > %--- cut here ---% > \end > > - Original Message - > From: "Vic Ricker" <[EMAIL PROTECTED]> > Sent: Tuesday, July 13, 2004 10:29 PM > > > > I apologize for the following stupid question. I have been doing some > > searching and haven't found anything really helpful. > > > > The problem is that postgres (7.4.2) keeps choosing to do a sequential > > scan on a table when an index scan would be significantly faster. > > > > The queries that I'm using look at daily statistics from events logged > > by our Checkpoint firewall and generate graphs. Since they are bit > > complicated, I simplified it to "select count(*) from log where > > timestamp>='7/12/2004'" for testing. > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] 'UPDATE OR INSERT' command
Is there a postgresql SQL idiom to perform an UPDATE, which becomes an INSERT if the primary key does not exist? I'm not sure I *should* use it in my application, I just want to know if it can be done. Thanks. ---(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] 'UPDATE OR INSERT' command
On Thu, Jul 15, 2004 at 13:20:57 -0500, Jeff Kowalczyk <[EMAIL PROTECTED]> wrote: > Is there a postgresql SQL idiom to perform an UPDATE, which becomes an > INSERT if the primary key does not exist? > > I'm not sure I *should* use it in my application, I just want to know if > it can be done. Thanks. There isn't a single statement that does this. This has been discussed a number of times. The archives will have some different examples. You end up needing to either lock the table or check for failure since postgres doesn't have predicate locking. Which technique is best will depend on the details of your situation. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] 'UPDATE OR INSERT' command
* Jeff Kowalczyk ([EMAIL PROTECTED]) wrote: > Is there a postgresql SQL idiom to perform an UPDATE, which becomes an > INSERT if the primary key does not exist? > > I'm not sure I *should* use it in my application, I just want to know if > it can be done. Thanks. Unfortunately, I don't believe there's one in PostgreSQL yet. MERGE is similar to this, and is defined in the latest SQL spec. Hopefully it'll be implemented in PostgreSQL sometime soon. Stephen signature.asc Description: Digital signature
