Thread: Most efficient way of selecting by date?
Hi all, I have a table which includes a field for the date and time an item was added to it. What's the most efficient way of selecting all of the items added on a specific date (this is a really basic question but I'm new to Postgres)? Also, would it be worth putting an index on the date column as most of my selects will be using it? Thanks, Simon
On Tue, Sep 09, 2003 at 21:30:31 +0100, Simon Willison <cs1spw@bath.ac.uk> wrote: > > I have a table which includes a field for the date and time an item was > added to it. What's the most efficient way of selecting all of the items > added on a specific date (this is a really basic question but I'm new to > Postgres)? Also, would it be worth putting an index on the date column > as most of my selects will be using it? Probably by using a range. Something like: select whatever from wherever where added < '2003-01-03' and added >= '2003-01-02';
On Tue, 2003-09-09 at 15:30, Simon Willison wrote: > Hi all, > > I have a table which includes a field for the date and time an item was > added to it. What's the most efficient way of selecting all of the items > added on a specific date (this is a really basic question but I'm new to > Postgres)? Also, would it be worth putting an index on the date column > as most of my selects will be using it? If you need to record the date and time a record was inserted, but will usually be querying by date, how about 2 fields? CREATE TABLE foo ( <blah> INSERT_DATE DATE, INSERT_TIME TIME ); CREATE INDEX foo_insdt_idx ON foo (INSERT_DATE, INSERT_TIME); Then, this will go pretty fast: SELECT * FROM foo WHERE INSERT_DATE = '2003-09-11'; -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA 4 degrees from Vladimir Putin
Hi, I'd like to know 2 things that should be added to the FAQ: How many tables can you create in a database? How many databases can you create on a single PostgreSQL server? A potential customer asked me this and I didn't know the answers. Thanks, Marc
On Fri, 2003-09-12 at 11:05, M. Bastin wrote: > Hi, > > I'd like to know 2 things that should be added to the FAQ: > > How many tables can you create in a database? > > How many databases can you create on a single PostgreSQL server? I think that to both questions the answer is that there is no practical limit within the database. A table takes up some rows in system tables, and it creates files on disk for its data and its indexes. A database creates a sub-directory containing its tables. Since tables are able to hold many millions of rows, I cannot imagine there could be any practical limit there. You will run out of inodes or disk space long before you run out of database resources. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Let the wicked forsake his way, and the unrighteous man his thoughts; and let him return unto the LORD, and He will have mercy upon him; and to our God, for he will abundantly pardon." Isaiah 55:7