Postgresql – Time interval is returning all records or no records

You can not do that. You need to specify the date yourself, as that clause accepts only literals or MAXVALUE, and not expressions.

So you could just use:

(PARTITION p_first VALUES LESS THAN (DATE'2015-12-12'));

If you really want to calculate it dynamically, you need to use dynamic SQL.

Joe's already hit on most of the points I just spent an hour typing up, in summary:

• highly doubtful you'll every run out of KeyCol values < bigint max (9.2e18), so conversions (if necessary) to/from bigint should not be a problem as long as you limit searches to KeyCol <= 0x00..007FFFFFFFFFFFFFFF
• I can't think of a query that's going to 'efficiently' find a gap all the time; you may get lucky and find a gap near the beginning of your search, or you could pay dearly to find the gap quite a ways into your search
• while I briefly thought about how to parallelize the query, I quickly discarded that idea (as a DBA I would not want to find out that your process is routinely bogging down my dataserver with 100% cpu utilization ... especially if you could have multiple copies of this running at the same time); noooo ... parallelization is going to be out of the question

So, what to do?

Let's put the (repeated, cpu-intensive, brute force) search idea on hold for a minute and look at the bigger picture.

• on an average basis one instance of this search is going to need to scan millions of index keys (and require a good bit of cpu, thrashing of db cache, and a user watching a spinning hour glass) just to locate a single value
• multiply the cpu-usage/cache-thrashing/spinning-hour-glass by ... how many searches do you expect in a day?
• keep in mind that, generally speaking, each instance of this search is going to need to scan the same set of (millions of) index keys; that's a LOT of repeated activity for such minimal benefit

What I'd like to propose is some additions to the data model ...

• a new table that keeps track of a set of 'available to use' KeyCol values, eg: available_for_use(KeyCol binary(64) not null primary key)
• how many records you maintain in this table is up to you to decide, eg, perhaps enough for a month's worth of activity?
• the table can periodically (weekly?) be 'topped off' with a new batch of KeyCol values (perhaps create a 'top off' stored proc?) [eg, update Joe's select/top/row_number() query to do a top 100000]
• you could setup a monitoring process to keep track of the number of available entries in available_for_use just in case you ever start to run low on values
• a new (or modified) DELETE trigger on the >main_table< that places deleted KeyCol values into our new table available_for_use whenever a row is deleted from the main table
• if you allow updates of the KeyCol column then a new/modified UPDATE trigger on the >main_table< to also keep our new table available_for_use updated
• when it comes time to 'search' for a new KeyCol value you select min(KeyCol) from available_for_use (obviously there's a bit more to this since a) you'll need to code for concurrency issues - don't want 2 copies of your process grabbing the same min(KeyCol) and b) you'll need to delete min(KeyCol) from the table; this should be relatively easy to code, perhaps as a stored proc, and can be addressed in another Q&A if necessary)
• in a worst case scenario, if your select min(KeyCol) process finds no available rows, you could kick off your 'top off' proc to generate a new batch of rows

With these proposed changes to the data model:

• you eliminate a LOT of excessive cpu cycles [your DBA will thank you]
• you eliminate ALL of those repetitive index scans and cache thrashing [your DBA will thank you]
• your users no longer have to watch the spinning hour glass (though they may not like the loss of an excuse to step away from their desk)
• there are plenty of ways to monitor the size of the available_for_use table to make sure you never run out of new values

Yes, the proposed available_for_use table is just a table of pre-generated 'next key' values; and yes, there's a potential for some contention when grabbing the 'next' value, but any contention a) is easily addressed through proper table/index/query design and b) is going to be minor/short-lived compared to the overhead/delays with the current idea of repeated, brute force, index searches.