Postgres Insert Trigger or update with Java?

floatfoo 0
Viewed 40

I am learning how to use ORM with Postgres in Java and I want to maintain in the table column date record that will hold exactly the following format:

2022-05-28T21:12:01.000Z

So I see two path:

  1. Create a Postgres Insert trigger
  2. Just care about date insertint in dao logic

Second is pretty intuitive for me, but what should I choose? Maybe flyway migration may be of use here?

EDIT:: I've been said in the comment that Postgres saves date in binary format, so I'll handle this. But automatic insertion of 'date' is still unclear to me

Thanks in advance!

flyway java postgresql
1 Answers

It is generally recommended to store date types in all DBs as date types rather than as strings. Because date types has a internal structures and you can shown this data to users in any formats. For example: 13 September 2022 or 13.09.2022 or 2022-09-13T12:24:45 and etc. You can use all date functions for this field such as: year > 2022 and etc. And all this does not depend on the fact that we use DB triggers or Java DAO.

But, triggers for that, for example, when there is an insert data into a table, we want to change another record in another table or in same table. We have two variants for doing this process:

  1. Doing this using PostgreSQL triggers.
  2. To do this in Backend (Java dao logic)

Option 1- not recommended. Because triggers wil be runs always, even when we will be execute any SQL command for importing data. Or in the Future may be we need different business logic such as: for some records we don't need changing another records.

Option 2 - recommended. Because all our business logics will be store in backend. And where ever we need to change another record before inserting, we will write the change code. And where not but we will not use the change code.

But sometimes it happens that before inserting data, you always (necessarily) need to change another record. In these circumstances, it is recommended to use triggers because suddenly you forget to write the change code somewhere. And triggers will always work no matter what

Ramin Faracov0
Related