blocks|key|633271|text|要在插入期间填充列，请使用DEFAULT值：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|633272|CREATE+TABLE+users+(
++id+serial+not+null,
++firstname+varchar(100),
++middlename+varchar(100),
++lastname+varchar(100),
++email+varchar(200),
++timestamp+timestamp+default+current_timestamp
)|code-block|syntax|javascript|633273|请注意，可以通过在INSERT语句中提供一个值来显式覆盖该列的值。如果你想防止这种情况发生，你确实需要一个trigger。|633274|如果需要在每次更新行时更新该列，则还需要触发器(作为mentioned+by+E.J.+Brennan)|633275|请注意，对列名使用保留字通常不是一个好主意。您应该找到与timestamp不同的名称|633276|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/ddl-default.html|1|http://www.postgresql.org/docs/current/static/dml-insert.html|2|http://www.postgresql.org/docs/current/static/plpgsql-trigger.html|3|https://stackoverflow.com/a/9556527/642706^0|D|7|D|7|0|0|0|9|6|9|6|1|1H|7|2|0|Q|P|3|0|S|9|0^^$0|@$1|2|3|4|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@$9|17|A|18|1|19]]|E|$]]|$1|F|3|G|5|H|7|1A|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1B|8|@$9|1C|A|1D|B|C]]|D|@$9|1E|A|1F|1|1G]|$9|1H|A|1I|1|1J]]|E|$]]|$1|M|3|N|5|6|7|1K|8|@]|D|@$9|1L|A|1M|1|1N]]|E|$]]|$1|O|3|P|5|6|7|1O|8|@$9|1P|A|1Q|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1R|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]|Y|$5|T|U|V|E|$W|Z]]|10|$5|T|U|V|E|$W|11]]|12|$5|T|U|V|E|$W|13]]]]

To populate the column during insert, use a <a href="http://www.postgresql.org/docs/current/static/ddl-default.html" rel="noreferrer"><code>DEFAULT</code></a> value:

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

Note that the value for that column can explicitly be overwritten by supplying a value in the <a href="http://www.postgresql.org/docs/current/static/dml-insert.html" rel="noreferrer"><code>INSERT</code></a> statement. If you want to prevent that you do need a <a href="http://www.postgresql.org/docs/current/static/plpgsql-trigger.html" rel="noreferrer">trigger</a>. 

You also need a trigger if you need to update that column whenever the row is updated (as <a href="https://stackoverflow.com/a/9556527/642706">mentioned by E.J. Brennan</a>)

Note that using reserved words for column names is usually not a good idea. You should find a different name than <code>timestamp</code>

blocks|key|633318|text|更新时间戳，仅当值更改时|type|unstyled|depth|inlineStyleRanges|entityRanges|data|633319|基于E.J的链接，并从此链接添加一条if语句(https://stackoverflow.com/a/3084254/1526023)|offset|length|633320|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';|code-block|syntax|javascript|633321|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/3084254/1526023^0|0|N|17|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@$D|U|E|V|1|W]]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Updating timestamp, only if the values changed

Based on E.J's link and add a if statement from this link (<a href="https://stackoverflow.com/a/3084254/1526023">https://stackoverflow.com/a/3084254/1526023</a>)

<pre><code>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';
</code></pre>

blocks|key|633375|text|使用'now()‘作为默认值会自动生成时间戳。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|633376|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Using 'now()' as default value automatically generates time-stamp.

blocks|key|418703|text|要在插入新行时自动更新PostgresSQL中的时间戳字段，可以将current_timestamp设置为其default值：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|418704|CREATE+TABLE+users+(
++++id+serial+not+null,
++++firstname+varchar(100),
++++middlename+varchar(100),
++++lastname+varchar(100),
++++email+varchar(200),
++++timestamp+timestamp+default+current_timestamp
)|code-block|syntax|javascript|418705|除此之外，您可能希望防止任何人在将来更新此字段，这可以通过创建更新触发器并应用它来实现：|418706|CREATE+OR+REPLACE+FUNCTION+stop_change_on_timestamp()
++RETURNS+trigger+AS
$BODY$
BEGIN
++--+always+reset+the+timestamp+to+the+old+value+("actual+creation+time")
++NEW.timestamp+:=+OLD.timestamp;
++RETURN+NEW;
END;
$BODY$|418707|CREATE+TRIGGER+prevent_timestamp_changes
++BEFORE+UPDATE
++ON+users
++FOR+EACH+ROW
++EXECUTE+PROCEDURE+stop_change_on_timestamp();|418708|entityMap^0|X|H|1I|7|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|Q|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|R|$]]

To automatically update the timestamp field in PostgresSQL whenever a new row is inserted, you can set the <code>current_timestamp</code> as its <code>default</code> value:
<pre class="lang-sql prettyprint-override"><code>CREATE TABLE users (
 id serial not null,
 firstname varchar(100),
 middlename varchar(100),
 lastname varchar(100),
 email varchar(200),
 timestamp timestamp default current_timestamp
)
</code></pre>
In addition to this, you might want to prevent anyone from updating this field in the future, and this can be done by creating an update trigger and applying it:
<pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION stop_change_on_timestamp()
 RETURNS trigger AS
$BODY$
BEGIN
 -- always reset the timestamp to the old value (&quot;actual creation time&quot;)
 NEW.timestamp := OLD.timestamp;
 RETURN NEW;
END;
$BODY$
</code></pre>
<pre class="lang-sql prettyprint-override"><code>CREATE TRIGGER prevent_timestamp_changes
 BEFORE UPDATE
 ON users
 FOR EACH ROW
 EXECUTE PROCEDURE stop_change_on_timestamp();
</code></pre>

blocks|key|418722|text|对于update和Liquibase+YAML：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|418723|databaseChangeLog:
++-+changeSet:
++++++id:+CREATE+FUNCTION+set_now_to_timestamp
++++++author:+Konstantin+Chvilyov
++++++changes:
++++++++-+sql:
++++++++++++sql:+CREATE+OR+REPLACE+FUNCTION+set_now_to_timestamp()+RETURNS+TRIGGER+LANGUAGE+plpgsql+AS+'BEGIN+NEW.timestamp+=+NOW();+RETURN+NEW;+END;'

++-+changeSet:
++++++id:+CREATE+TRIGGER+update_users_set_now_to_timestamp
++++++author:+Konstantin+Chvilyov
++++++changes:
++++++++-+sql:
++++++++++++sql:+CREATE+TRIGGER+update_users_set_now_to_timestamp+BEFORE+UPDATE+ON+users+FOR+EACH+ROW+EXECUTE+PROCEDURE+set_now_to_timestamp();|code-block|syntax|javascript|418724|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For update and Liquibase YAML:
<pre><code>databaseChangeLog:
 - changeSet:
 id: CREATE FUNCTION set_now_to_timestamp
 author: Konstantin Chvilyov
 changes:
 - sql:
 sql: CREATE OR REPLACE FUNCTION set_now_to_timestamp() RETURNS TRIGGER LANGUAGE plpgsql AS 'BEGIN NEW.timestamp = NOW(); RETURN NEW; END;'

 - changeSet:
 id: CREATE TRIGGER update_users_set_now_to_timestamp
 author: Konstantin Chvilyov
 changes:
 - sql:
 sql: CREATE TRIGGER update_users_set_now_to_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE set_now_to_timestamp();
</code></pre>

I want the code to be able to automatically update the time stamp when a new row is inserted as I can do in MySQL using CURRENT_TIMESTAMP.

How will I be able to achieve this in PostgreSQL?

<pre><code>CREATE TABLE users (
 id serial not null,
 firstname varchar(100),
 middlename varchar(100),
 lastname varchar(100),
 email varchar(200),
 timestamp timestamp
)
</code></pre>

How do I automatically update a timestamp in PostgreSQL

PostgreSQL

MySQL

我希望代码能够在插入新行时自动更新时间戳，就像我可以使用CURRENT_TIMESTAMP在MySQL中做的那样。我将如何在PostgreSQL中实现这一点？CREATE TABLE users (    id serial not null,    firstname varchar(100),    middlen...

问如何在PostgreSQL中自动更新时间戳
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PostgreSQL中自动更新时间戳EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PostgreSQL中自动更新时间戳
EN