Question 114224 in PostgreSQL Question posted in
The official documentation can be found here.

How index will work on separate date and time column in PostgreSQL

I have two separate columns in Postgres Database for Date and time
I had Index in Date column and time column. As earlier I need to filter the data using date filter only Index worked perfectly. But In recent requirement I need to filter the data using both date and time filter.
when I filter the data by combing date and time like
concat(date_column, ‘ ‘, time_column) ::timestamp>='{var_date_time}’ Date and time Index does not work . It takes time to fetch the data.

any way where I can create index by combining both Date and time columns?

Tags:

3

Answers


  1. 0

    You need to create an index on exactly the expression you are going to use in your WHERE clause. You don’t need to cast between a text and a timestamp, just add the two columns:

    where date_column + time_column >= ...

    Needs the following index:

    create index on the_table ( (date_column + time_column) );

    You might want to think about changing that into a single column in the long run.

    Login or Signup to reply.
  2. 0

    That is probably not a good way to store your data. It would be better as a single timestamptz column.

    With your existing table and index, you can use a tuple comparison:

    WHERE (date_column,time_column)>(date_constant,time_constant);

    It is not clear to me if your existing structure is one multi-column index or two single-column indexes. Either way, it should use (one of) the indexes, but the multi-column would be better.

    Login or Signup to reply.
  3. 0

    Is your recent requirement for date + time a replacement for the existing or an addition to the existing. If replacing the current date only requirement then replacing with a single column, as suggested, is the likely the best solution. However, if this is an additional requirement there is an alternative: add a timestamp as as a generated column then create an index on that column:

    alter table <table> add ts_col timestamp generated always as (date_col+time_col) stored;
create index test_ts_col_ndx on <table> (ts_col);

    With that your existing queries and dml statements do not change, you only need the new queries where date+time is required. ( see demo)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search