CODEWITHSUNDEEP
postgresql
edi
edi

Reputation: 233

Postgres how to create table with automatic create_by

If I want to create a table with column create_by automatically filled with the user who create the entry, what is the DDL look like? Wonder whether Postgres can do this similar to create_at

e.g. create_at TIMESTAMP NOT NULL DEFAULT NOW()

kind of thing.

Upvotes: 1

Views: 3624

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE foo
    (
        id serial primary key
      , "bar" varchar(1)
      , created_by text NOT NULL DEFAULT current_user
      , created_at timestamp DEFAULT current_timestamp
    )
;

INSERT INTO foo
    ("bar")
VALUES
    ('a'),
    ('b'),
    ('c')
;

Query 1:

select *
from foo

Results:

| id | bar |    created_by |                  created_at |
|----|-----|---------------|-----------------------------|
|  1 |   a | user_17_3a66a | 2017-11-04T05:05:18.161681Z |
|  2 |   b | user_17_3a66a | 2017-11-04T05:05:18.161681Z |
|  3 |   c | user_17_3a66a | 2017-11-04T05:05:18.161681Z |

Upvotes: 6

Related Questions