postgres.functions module

[theelderbeever]

I think it would be great to have a way to directly input database specific functions in queries and column defaults.

This might already exist but one that comes to mind is Postgres' NOW() function.

Given the following it would be nice to use the database NOW() for the default when doing bulk inserts. It can be pretty valuable to have all records in the insert share an identical creation timestamp. Right now when generating records you have to explicitly pass a pre-instantiated datetime.now() value to the records rather than letting the database handle it. Not a big deal but a nice to have.

class Task(Table):
  created_at: Timestamptz(default=postgres.functions.NOW)

Basically these could be function delegates

# postgres.functions.py
class NOW(FunctionDelegate):
  def __str__(self):
    return 'NOW()'

[dantownsend]

@theelderbeever There's a workaround for this at the moment, but it's completely undocumented ...

When you create a Timestamp or Timestmaptz column, it sets the default value in the database to current_timestamp.

The reason for this was if someone bypassed the ORM, the default value would still be correct in the database.

For this schema:

class Task(Table):
    title = Varchar()
    created_at = Timestamp()

If you wanted to insert a bunch of rows which uses the databases current_timestamp value (rather than Python assigning datetime.datetime.now() to it), you can do this:

from piccolo.columns.column_types import DEFAULT

await Task.insert(
    Task(title="Do washing up", created_at=DEFAULT),
    Task(title="Send package", created_at=DEFAULT)
)

Or this instead if you prefer:

from piccolo.columns.column_types import Unquoted

await Task.insert(
    Task(title="Do washing up", created_at=Unquoted('current_timestamp')),
    Task(title="Send package", created_at=Unquoted('current_timestamp'))
)

It doesn't work as well for:

task = Task(title="Do washing up", created_at=DEFAULT),
await task.save()
>>> task.created_at
DEFAULT  # This should now be the datetime assigned by the database, rather than still being DEFAULT

This can be fixed once this PR is finally merged in: #203

[dantownsend]

Unquoted isn't the greatest name, so would probably be changed before documenting this as a public API.

[theelderbeever]

@dantownsend Thanks for suggesting that. I wonder if SQLRaw, Raw, or SQLExpression are more accurate/intuitive? Otherwise, Unquoted also isn't the worst name either... its very true to what it does.