Sfrgttk

How to write the following sign?

Do wooden building fires get hotter than 600°C?

How does the math work when buying airline miles?

How to install press fit bottom bracket into new frame

A term for a woman complaining about things/begging in a cute/childish way

Should I follow up with an employee I believe overracted to a mistake I made?

What would you call this weird metallic apparatus that allows you to lift people?

Localisation of Category

What is the difference between globalisation and imperialism?

Why does it sometimes sound good to play a grace note as a lead in to a note in a melody?

Disembodied hand growing fangs

Should I use a zero-interest credit card for a large one-time purchase?

Significance of Cersei's obsession with elephants?

Denied boarding although I have proper visa and documentation. To whom should I make a complaint?

What was the first language to use conditional keywords?

What is the font for "b" letter?

Effects on objects due to a brief relocation of massive amounts of mass

When a candle burns, why does the top of wick glow if bottom of flame is hottest?

How does light 'choose' between wave and particle behaviour?

Selecting user stories during sprint planning

How were pictures turned from film to a big picture in a picture frame before digital scanning?

Has negative voting ever been officially implemented in elections, or seriously proposed, or even studied?

An adverb for when you're not exaggerating

Can the Great Weapon Master feat's "Power Attack" apply to attacks from the Spiritual Weapon spell?

Get last modified date of table in postgresql

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

0

I want know the last modified date of table in postgresql. In SQL Server can get using

SELECT modify_date FROM sys.objects 

How to get same thing in Postgres? I am using Postgres 9.4

postgresql postgresql-9.4

share|improve this question

edited Mar 3 '18 at 10:35

a_horse_with_no_name

41.8k780117

asked Mar 3 '18 at 9:30

veena hosurveena hosur

15

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  That's not possible. Postgres does not maintain that information
  
  – a_horse_with_no_name
  Mar 3 '18 at 10:35
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  no other way to get information then
  
  – veena hosur
  Mar 3 '18 at 10:37
  

  
  
  
  

add a comment | 

0

I want know the last modified date of table in postgresql. In SQL Server can get using

SELECT modify_date FROM sys.objects 

How to get same thing in Postgres? I am using Postgres 9.4

postgresql postgresql-9.4

share|improve this question

edited Mar 3 '18 at 10:35

a_horse_with_no_name

41.8k780117

asked Mar 3 '18 at 9:30

veena hosurveena hosur

15

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  That's not possible. Postgres does not maintain that information
  
  – a_horse_with_no_name
  Mar 3 '18 at 10:35
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  no other way to get information then
  
  – veena hosur
  Mar 3 '18 at 10:37
  

  
  
  
  

add a comment | 

I want know the last modified date of table in postgresql. In SQL Server can get using

SELECT modify_date FROM sys.objects 

How to get same thing in Postgres? I am using Postgres 9.4

postgresql postgresql-9.4

share|improve this question

edited Mar 3 '18 at 10:35

a_horse_with_no_name

41.8k780117

asked Mar 3 '18 at 9:30

veena hosurveena hosur

15

SELECT modify_date FROM sys.objects 

share|improve this question

edited Mar 3 '18 at 10:35

a_horse_with_no_name

41.8k780117

asked Mar 3 '18 at 9:30

veena hosurveena hosur

15

share|improve this question

edited Mar 3 '18 at 10:35

a_horse_with_no_name

41.8k780117

asked Mar 3 '18 at 9:30

veena hosurveena hosur

15

edited Mar 3 '18 at 10:35

a_horse_with_no_name

41.8k780117

edited Mar 3 '18 at 10:35

a_horse_with_no_name

41.8k780117

asked Mar 3 '18 at 9:30

veena hosurveena hosur

15

asked Mar 3 '18 at 9:30

veena hosurveena hosur

15

1 Answer
1

active

oldest

votes

0

If the file system stores file modification time (mtime), I believe you can use

SELECT pg_relation_filepath('schema.table');

To find the path to the table's heap. From there you can look up the file modification time (mtime). This has a lot of drawbacks,

1. It may be obscured by WAL, data could be written to the write-ahead log pending a checkpoint. In such a case, nothing has even tried to write it to disk.


2. The FS may be journaling the data itself.


3. The data may be in the write cache of the operating system.

Shy of that you may need to write your own mtime-abilities on the rows being updated/inserted, or the table (by creating a meta-table with its own rows).

CREATE TABLE foo (

  id int,

  mtime timestamp with time zone DEFAULT now()

);

INSERT INTO foo(id) VALUES (1);

or, for a meta table

CREATE TABLE foo (

  id serial,

  fqn_table text,

  mtime timestamp with time zone DEFAULT now()

);

share|improve this answer

answered Mar 3 '18 at 19:59

Evan CarrollEvan Carroll

33.8k1079235

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i cant create mtime. Its not in our schema
  
  – veena hosur
  Mar 5 '18 at 6:07
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f199290%2fget-last-modified-date-of-table-in-postgresql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

If the file system stores file modification time (mtime), I believe you can use

SELECT pg_relation_filepath('schema.table');

To find the path to the table's heap. From there you can look up the file modification time (mtime). This has a lot of drawbacks,

1. It may be obscured by WAL, data could be written to the write-ahead log pending a checkpoint. In such a case, nothing has even tried to write it to disk.


2. The FS may be journaling the data itself.


3. The data may be in the write cache of the operating system.

Shy of that you may need to write your own mtime-abilities on the rows being updated/inserted, or the table (by creating a meta-table with its own rows).

CREATE TABLE foo (

  id int,

  mtime timestamp with time zone DEFAULT now()

);

INSERT INTO foo(id) VALUES (1);

or, for a meta table

CREATE TABLE foo (

  id serial,

  fqn_table text,

  mtime timestamp with time zone DEFAULT now()

);

share|improve this answer

answered Mar 3 '18 at 19:59

Evan CarrollEvan Carroll

33.8k1079235

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i cant create mtime. Its not in our schema
  
  – veena hosur
  Mar 5 '18 at 6:07
  

  
  
  
  

add a comment | 

0

If the file system stores file modification time (mtime), I believe you can use

SELECT pg_relation_filepath('schema.table');

To find the path to the table's heap. From there you can look up the file modification time (mtime). This has a lot of drawbacks,

1. It may be obscured by WAL, data could be written to the write-ahead log pending a checkpoint. In such a case, nothing has even tried to write it to disk.


2. The FS may be journaling the data itself.


3. The data may be in the write cache of the operating system.

Shy of that you may need to write your own mtime-abilities on the rows being updated/inserted, or the table (by creating a meta-table with its own rows).

CREATE TABLE foo (

  id int,

  mtime timestamp with time zone DEFAULT now()

);

INSERT INTO foo(id) VALUES (1);

or, for a meta table

CREATE TABLE foo (

  id serial,

  fqn_table text,

  mtime timestamp with time zone DEFAULT now()

);

share|improve this answer

answered Mar 3 '18 at 19:59

Evan CarrollEvan Carroll

33.8k1079235

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i cant create mtime. Its not in our schema
  
  – veena hosur
  Mar 5 '18 at 6:07
  

  
  
  
  

add a comment | 

If the file system stores file modification time (mtime), I believe you can use

SELECT pg_relation_filepath('schema.table');

To find the path to the table's heap. From there you can look up the file modification time (mtime). This has a lot of drawbacks,

1. It may be obscured by WAL, data could be written to the write-ahead log pending a checkpoint. In such a case, nothing has even tried to write it to disk.


2. The FS may be journaling the data itself.


3. The data may be in the write cache of the operating system.

Shy of that you may need to write your own mtime-abilities on the rows being updated/inserted, or the table (by creating a meta-table with its own rows).

CREATE TABLE foo (

  id int,

  mtime timestamp with time zone DEFAULT now()

);

INSERT INTO foo(id) VALUES (1);

or, for a meta table

CREATE TABLE foo (

  id serial,

  fqn_table text,

  mtime timestamp with time zone DEFAULT now()

);

share|improve this answer

answered Mar 3 '18 at 19:59

Evan CarrollEvan Carroll

33.8k1079235

SELECT pg_relation_filepath('schema.table');

CREATE TABLE foo (

  id int,

  mtime timestamp with time zone DEFAULT now()

);

INSERT INTO foo(id) VALUES (1);

CREATE TABLE foo (

  id serial,

  fqn_table text,

  mtime timestamp with time zone DEFAULT now()

);

share|improve this answer

answered Mar 3 '18 at 19:59

Evan CarrollEvan Carroll

33.8k1079235

answered Mar 3 '18 at 19:59

Evan CarrollEvan Carroll

33.8k1079235

answered Mar 3 '18 at 19:59

Evan CarrollEvan Carroll

33.8k1079235