[SQL] internal format of timstamp?
Hi, I am trying to recover a lot of deleted rows from a database ( pg 8.2.3 ) , not my database, I promise….. When using the tool pgfsck I get good results, but timestamp is not implemented. When trying to export as int8 i get fx. 4735129360236469258 representing december 29, 2011, 16:30 But how should I do the conversion from the numeric value to the actual timestamp ? Regards, Lars. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] internal format of timstamp?
On 2011-12-29, Lars Gustafsson wrote: > Hi, > > I am trying to recover a lot of deleted rows from a database ( pg > 8.2.3 ) , not my database, I promise….. >> When using the tool pgfsck I get good results, but timestamp is not >> implemented. > > When trying to export as int8 i get fx. 4735129360236469258 > representing december 29, 2011, 16:30 > > But how should I do the conversion from the numeric value to the actual > timestamp ? possibly that's a floating point timestamp try it as float8 instead of int8. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] internal format of timstamp?
On Dec 29, 2011, at 7:08 AM, Lars Gustafsson wrote: > Hi, > > I am trying to recover a lot of deleted rows from a database ( pg 8.2.3 ) , > not my database, I promise….. > > When using the tool pgfsck I get good results, but timestamp is not > implemented. > > When trying to export as int8 i get fx. 4735129360236469258 > representing december 29, 2011, 16:30 > > But how should I do the conversion from the numeric value to the actual > timestamp ? > > Regards, > Lars. > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql I'm not sure what the internal timestamp representation is, but I thought it went down to fractions of a second. http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-DATETIME-INTERNALS Brent. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] internal format of timstamp?
On 12/29/2011 12:42 PM, Jasen Betts wrote: On 2011-12-29, Lars Gustafsson wrote: Hi, I am trying to recover a lot of deleted rows from a database ( pg 8.2.3 ) , not my database, I promise….. When using the tool pgfsck I get good results, but timestamp is not implemented. When trying to export as int8 i get fx. 4735129360236469258 representing december 29, 2011, 16:30 But how should I do the conversion from the numeric value to the actual timestamp ? possibly that's a floating point timestamp try it as float8 instead of int8. I am not an internals person, but suspect that your first step should be to check pg_config to see if the server from which you are attempting to recover data was compiled with --enable-integer-datetimes. Cheers, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Getting a list of a table's attributes that are sortable sorted by uniqueness
Hi, I frequently use pg_dump to dump databases and compare them with diff. To get rid of most "false positives", I'd like to patch pg_dump to sort the table so that its dumped order isn't changed more than necessary by insertions & Co. So I'm looking for a query that will return a list of a table's attributes that are sortable (e. g. no XML fields) and sorted by "uniqueness", i. e. first attributes repre- senting the primary key, then other unique keys, then the rest. Before I dive into the depths of PostgreSQL's system cata- logues, has anyone already solved this problem? TIA, Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Current transaction is aborted, commands ignored until end of transaction block
Hi, Maybe there is a simple solution for PostgreSQL behaviour that is annoying me... I've got users making updates to a master table and a number of detail tables. All changes to the master record and related detail records are encapsulated in a transaction so everything can be rolled back if necessary and also to lock those master and related records for the user making the changes. When they do something that violates a constraint (for example adding a duplicate detail record where that is not allowed), PostgreSQL aborts the transaction. What I would much rather have is that PostgreSQL returns an error but does not cancel the transaction as it's perfectly OK (from a user's point of view) to try to do something that violates a constraint. What annoys me is that I don't think that a constraint violation made by a user should result in an aborted transaction. There is probably a very good reason to do that however the logic escapes me... Of course I can start testing existing values in the database before accepting them in the user interface but that's putting the horse behind the cart. I much rather use the constraints at the database level to tell me a particular update can't be done and do that without loosing everything else I happened to have done in that transaction until that point. Any suggestions? Jan smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Current transaction is aborted, commands ignored until end of transaction block
On Dec 29, 2011, at 23:25, Jan Bakuwel wrote: > Hi, > > Maybe there is a simple solution for PostgreSQL behaviour that is > annoying me... > > I've got users making updates to a master table and a number of detail > tables. All changes to the master record and related detail records are > encapsulated in a transaction so everything can be rolled back if > necessary and also to lock those master and related records for the user > making the changes. > > When they do something that violates a constraint (for example adding a > duplicate detail record where that is not allowed), PostgreSQL aborts > the transaction. What I would much rather have is that PostgreSQL > returns an error but does not cancel the transaction as it's perfectly > OK (from a user's point of view) to try to do something that violates a > constraint. > > What annoys me is that I don't think that a constraint violation made by > a user should result in an aborted transaction. There is probably a very > good reason to do that however the logic escapes me... > > Of course I can start testing existing values in the database before > accepting them in the user interface but that's putting the horse behind > the cart. I much rather use the constraints at the database level to > tell me a particular update can't be done and do that without loosing > everything else I happened to have done in that transaction until that > point. > > Any suggestions? > > Jan > > Start a "savepoint" before each sub-update and rollback to the savepoint if the update fails, and then try again with different data. If it succeeds you then release the savepoint anad move on. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
