Your question, your audience. Choose who sees your identity—and your question—with question security.
How to create audit fields on a table in Postgresql
I need to create a Postgresql table that contains 4 'audit' fields:
These fields should be populated through the use of defaults and / or triggers. For example, it is fine to use default values for all 4 fields when a record is created, but after creation, the "user id created" and "date created" fields should never change. On the other hand, I want the "user id modified" and "date modified" to change every time that the record gets updated.
I am looking for someone to help me complete the following "create table" statement:
- User ID Created
- User Id Modified
- Date Created
- Date Modified
These fields should be populated through the use of defaults and / or triggers. For example, it is fine to use default values for all 4 fields when a record is created, but after creation, the "user id created" and "date created" fields should never change. On the other hand, I want the "user id modified" and "date modified" to change every time that the record gets updated.
I am looking for someone to help me complete the following "create table" statement:
create table test_table (id serial
, name character varying
, user_id_created
, user_id_modified
, datetime_created
, datetime_modified
)
Asked:
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
DENY UPDATE ON test_table ( user_id_created) TO <users or group as you want>
to fill them on creation:
user name is a system function: current_user or user:
https://www.postgresql.org/docs/9.1/static/functions-info.html
and date/time functions:
https://www.postgresql.org/docs/9.1/static/functions-datetime.html
to specify the "default" in the syntax:
https://www.postgresql.org/docs/9.2/static/ddl-default.html
to update the "update" fields, use a trigger on the table:
https://www.postgresql.org/docs/9.4/static/sql-createtrigger.html