Jak automatycznie zaktualizować znacznik czasu w PostgreSQL

132

Chcę, aby kod mógł automatycznie aktualizować znacznik czasu po wstawieniu nowego wiersza, tak jak mogę to zrobić w MySQL za pomocą CURRENT_TIMESTAMP.

Jak mogę to osiągnąć w PostgreSQL?

CREATE TABLE users (
    id serial not null,
    firstname varchar(100),
    middlename varchar(100),
    lastname varchar(100),
    email varchar(200),
    timestamp timestamp
)

database postgresql timestamp

— Aaron
źródło

6

Nawiasem mówiąc, typ danych timestampjest zdefiniowany w specyfikacji SQL jako skrót od TIMESTAMP WITHOUT TIME ZONE. Jak wyjaśnił ekspert Postgres, David E. Wheeler, prawie na pewno nie tego chcesz . Drugi typ TIMESTAMP WITH TIME ZONEjest prawdopodobnie tym, czego potrzebujesz, używając wszelkich przekazanych informacji o przesunięciu strefy czasowej, aby dostosować datę i godzinę do czasu UTC (ale w rzeczywistości nie przechowując informacji o strefie czasowej pomimo nazwy typu).

— Basil Bourque

3

Po ponownym nauczeniu się tego, napisałem szczegółowy post na blogu o rejestrowaniu daty i czasu zarówno tworzenia, jak i modyfikacji wiersza przy użyciu wartości domyślnej, funkcji i wyzwalacza. Zawiera kompletny przykładowy kod SQL i PL / pgSQL do użytku w Postgres.

— Basil Bourque

197

Aby wypełnić kolumnę podczas wstawiania, użyj DEFAULTwartości:

CREATE TABLE users (
  id serial not null,
  firstname varchar(100),
  middlename varchar(100),
  lastname varchar(100),
  email varchar(200),
  timestamp timestamp default current_timestamp
)

Należy zauważyć, że wartość dla tej kolumny można jawnie zastąpić, podając wartość w INSERTinstrukcji. Jeśli chcesz temu zapobiec, potrzebujesz wyzwalacza .

Potrzebujesz również wyzwalacza, jeśli chcesz zaktualizować tę kolumnę za każdym razem, gdy aktualizowany jest wiersz (jak wspomniał EJ Brennan )

Zwróć uwagę, że używanie słów zastrzeżonych w nazwach kolumn zwykle nie jest dobrym pomysłem. Powinieneś znaleźć inną nazwę niżtimestamp

— koń bez imienia
źródło

14

Pamiętaj, że Postgres ma funkcje, które informują Cię (1) o aktualnym momencie, (2) o godzinie rozpoczęcia instrukcji i (3) o godzinie rozpoczęcia transakcji. Przykład pokazany tutaj to początek bieżącej transakcji. Możesz tego chcieć lub nie, w przeciwieństwie do pozostałych dwóch możliwości.

— Basil Bourque

6

Dobra zaleta dotycząca unikania nazw, które kolidują ze słowami zastrzeżonymi. Zauważ, że specyfikacja SQL wyraźnie obiecuje, że nigdy nie będzie używać końcowego podkreślenia jako słowa zastrzeżonego. Więc można zrobić timestampw timestamp_. Jeszcze lepsza byłaby nazwa bardziej opisowa, taka jak row_created_.

— Basil Bourque

Pierwsza połowa w ogóle nie jest tym, o co pytał PO, mimo że druga połowa odnosiła się do innej odpowiedzi (która jest poprawna), ale nadal wprowadza w błąd. Nie należy tego przyjmować jako odpowiedzi.

— Eric Wang,

1

Tak mówi w dokumentacji typów Postgres. Varchar ma dodatkowe cykle procesora, aby sprawdzić ograniczenie, co nie występuje w TEXT.

— Rahly

3

nie z jednym polem, ale nigdy nie widziałem użytecznej tabeli z jednym polem. To ograniczenie jest potęgowane przez liczbę pól. Pamiętam, że zmieniłem wszystkie varchary w tabeli na tekst i uzyskałem 15% poprawę pisania. Również mały spadek wydajności nie oznacza spadku wydajności „NIE”.

— Rahly

104

Będziesz musiał napisać wyzwalacz wstawiania i możliwy wyzwalacz aktualizacji, jeśli chcesz, aby zmienił się po zmianie rekordu. Ten artykuł wyjaśnia to całkiem ładnie:

http://www.revsys.com/blog/2006/aug/04/automatically-updating-a-timestamp-column-in-postgresql/

CREATE OR REPLACE FUNCTION update_modified_column()   
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;   
END;
$$ language 'plpgsql';

Zastosuj wyzwalacz w następujący sposób:

CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON customer FOR EACH ROW EXECUTE PROCEDURE  update_modified_column();

— EJ Brennan
źródło

2

To najlepsza odpowiedź, IMO (chociaż domyślna wystarczy do wstawienia).

— kik

2

Co nowego tutaj?

— Naveen

@Naveen postgresql.org/docs/9.2/static/plpgsql-trigger.html

— timqian

Jest to bardziej kompletne rozwiązanie, które działałoby z początkowymi INSERTami, jak również z wszelkimi późniejszymi aktualizacjami (dobre do audytu danych). Zgoda, PO prosi tylko o to pierwsze.

— Pavel Lechev

5

@Naveen not much, co nowego u Ciebie?

— Underyx

57

Aktualizacja sygnatury czasowej, tylko jeśli wartości uległy zmianie

Na podstawie linku EJ i dodaj oświadczenie if z tego linku ( https://stackoverflow.com/a/3084254/1526023 )

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.modified = now(); 
      RETURN NEW;
   ELSE
      RETURN OLD;
   END IF;
END;
$$ language 'plpgsql';

— Jim Yu
źródło

9

Użycie „now ()” jako wartości domyślnej automatycznie generuje znacznik czasu.

— Parichay Mo
źródło