Skip to content

Instantly share code, notes, and snippets.

@incyclum

incyclum/postgresql_updated_at.sql

Last active May 1, 2018 07:28
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save incyclum/a19bcf9d2b405b38a5f509fd1a8fed66 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save incyclum/a19bcf9d2b405b38a5f509fd1a8fed66 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. David Delobel renamed this gist May 1, 2018. 1 changed file with 0 additions and 0 deletions.
    0 updated_at.sql → postgresql_updated_at.sql
    View file
    File renamed without changes.
  2. David Delobel created this gist May 15, 2017.
    21 changes: 21 additions & 0 deletions updated_at.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,21 @@
    CREATE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS $$
    BEGIN
    NEW.updated_at = now();
    RETURN NEW;
    END;
    $$ language 'plpgsql';

    CREATE TABLE account (
    account_id SERIAL PRIMARY KEY,
    username VARCHAR(25) UNIQUE CONSTRAINT username_regex CHECK (username ~ '^[a-zA-Z0-9]{2,25}$') NOT NULL,
    email VARCHAR(254) NOT NULL,
    password CHAR(60) NOT NULL, -- bcrypt
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_login TIMESTAMP
    );

    CREATE TRIGGER update_account_updated_at BEFORE UPDATE
    ON account FOR EACH ROW EXECUTE PROCEDURE
    update_updated_at_column();