Thread: getting back autonumber just inserted

getting back autonumber just inserted

From
mail TechEvolution
Date:
hello

i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0 
(windowsinstaller) on a xp prof platform

i would like to get back the autonumber from the last record inserted, 
for other SQL db (m$ sql db ...) i could use:
SELECT @@ IDENTITY

can someone help me by informing me what the SQL syntax is to be used 
with PostGreSQL db and get the same result, the last autonumber inserted?

greeTz

wes


Re: getting back autonumber just inserted

From
Alvaro Herrera
Date:
On Thu, Jul 07, 2005 at 07:50:16PM +0200, mail TechEvolution wrote:
> hello
> 
> i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0 
> (windowsinstaller) on a xp prof platform
> 
> i would like to get back the autonumber from the last record inserted, 
> for other SQL db (m$ sql db ...) i could use:
> SELECT @@ IDENTITY
> 
> can someone help me by informing me what the SQL syntax is to be used 
> with PostGreSQL db and get the same result, the last autonumber inserted?

You use the currval() function, using the name of the involved sequence
as parameter.  There is a pg_get_serial_sequence() function, to which
you give the table name and column name, and it will give you the
sequence name.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Oh, oh, las chicas galacianas, lo harán por las perlas,
¡Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)


Re: getting back autonumber just inserted

From
mail TechEvolution
Date:
hi Alvaro Herrera

>> You use the currval() function, using the name of the involved sequence
>> as parameter.  There is a pg_get_serial_sequence() function, to which
>> you give the table name and column name

thx for the information

and is there a function i can use to get the last added autonumber without knowing wich table or row? (exactly as
select@@ IDENTITY does?) i have read in the messages that there must be such a function for version 8.0, but i could
notfind it in the documentation, any idea on it?
 

greetZ

wes



Alvaro Herrera schreef:

>On Thu, Jul 07, 2005 at 07:50:16PM +0200, mail TechEvolution wrote:
>  
>
>>hello
>>
>>i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0 
>>(windowsinstaller) on a xp prof platform
>>
>>i would like to get back the autonumber from the last record inserted, 
>>for other SQL db (m$ sql db ...) i could use:
>>SELECT @@ IDENTITY
>>
>>can someone help me by informing me what the SQL syntax is to be used 
>>with PostGreSQL db and get the same result, the last autonumber inserted?
>>    
>>
>
>You use the currval() function, using the name of the involved sequence
>as parameter.  There is a pg_get_serial_sequence() function, to which
>you give the table name and column name, and it will give you the
>sequence name.
>
>  
>



Re: getting back autonumber just inserted

From
Alvaro Herrera
Date:
On Thu, Jul 07, 2005 at 08:21:12PM +0200, mail TechEvolution wrote:
> hi Alvaro Herrera
> 
> >>You use the currval() function, using the name of the involved sequence
> >>as parameter.  There is a pg_get_serial_sequence() function, to which
> >>you give the table name and column name
> 
> thx for the information
> 
> and is there a function i can use to get the last added autonumber without 
> knowing wich table or row? (exactly as select @@ IDENTITY does?) i have 
> read in the messages that there must be such a function for version 8.0, 
> but i could not find it in the documentation, any idea on it?

There is no such function in 8.0.  There will be in 8.1, however.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)


Re: getting back autonumber just inserted

From
Theodore Petrosky
Date:
you have to use currval inside a transaction...

begin;
insert something that increments the counter;
select currval('sequence_name');
end;

using currval inside a transaction guarantees that the
value is correct for your insert statement and has not
changed by another insert statement.

Ted


--- mail TechEvolution <mail@techevolution.be> wrote:

> hello
> 
> i ame a newbie to PostGreSQL, I ame using PostGreSQL
> 8.0 
> (windowsinstaller) on a xp prof platform
> 
> i would like to get back the autonumber from the
> last record inserted, 
> for other SQL db (m$ sql db ...) i could use:
> SELECT @@ IDENTITY
> 
> can someone help me by informing me what the SQL
> syntax is to be used 
> with PostGreSQL db and get the same result, the last
> autonumber inserted?
> 
> greeTz
> 
> wes
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 


    
____________________________________________________
Sell on Yahoo! Auctions – no fees. Bid on great items.  
http://auctions.yahoo.com/


Re: getting back autonumber just inserted

From
Scott Marlowe
Date:
On Thu, 2005-07-07 at 15:14, Theodore Petrosky wrote:
> you have to use currval inside a transaction...
> 
> begin;
> insert something that increments the counter;
> select currval('sequence_name');
> end;
> 
> using currval inside a transaction guarantees that the
> value is correct for your insert statement and has not
> changed by another insert statement.

Actually, whether you use it inside a transaction or not, as long as
it's in the same session / connection, it is guaranteed to "do the right
thing."

Test it, you'll see.


Re: getting back autonumber just inserted

From
mail TechEvolution
Date:
THX to all guys, it is working great !!

greetZ

wes


Re: getting back autonumber just inserted

From
Michael Fuhr
Date:
On Thu, Jul 07, 2005 at 01:14:33PM -0700, Theodore Petrosky wrote:
> 
> you have to use currval inside a transaction...

Not true.  Have you observed otherwise?

> begin;
> insert something that increments the counter;
> select currval('sequence_name');
> end;
> 
> using currval inside a transaction guarantees that the
> value is correct for your insert statement and has not
> changed by another insert statement.

currval() returns the most recently obtained value from the sequence
in the current session, regardless of what other sessions are doing
or whether the current session is in a transaction.  See the
documentation and the FAQ:

http://www.postgresql.org/docs/8.0/static/functions-sequence.html

"Notice that because this is returning a session-local value, it
gives a predictable answer whether or not other sessions have
executed nextval since the current session did."

http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3

"4.11.3) Doesn't currval() lead to a race condition with other users?

"No.  currval() returns the current value assigned by your session, not by
all sessions."

You can do experiments to confirm what the documentation states.
If you see different behavior then please put together a self-contained
test case and report it as a bug.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: getting back autonumber just inserted

From
Michael Fuhr
Date:
On Thu, Jul 07, 2005 at 02:47:23PM -0600, Larry Meadors wrote:
>
> If you have a trigger on your table that inserts a record in a table
> and shares the same sequence, what value do you get back, the
> triggered curval, or the currently inserted one?

That's a different issue than whether currval() is subject to
interference from other transactions.  And just wait until PostgreSQL
8.1 comes out and people start using lastval() -- then it could get
*really* confusing which sequence value you're getting.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: getting back autonumber just inserted

From
PFC
Date:
> That's a different issue than whether currval() is subject to
> interference from other transactions.  And just wait until PostgreSQL
> 8.1 comes out and people start using lastval() -- then it could get
> *really* confusing which sequence value you're getting.
What happens if an INSERT trigger inserts something into another table  
which also has a sequence ?



Re: getting back autonumber just inserted

From
Alvaro Herrera
Date:
On Fri, Jul 08, 2005 at 12:26:30AM +0200, PFC wrote:
> 
> >That's a different issue than whether currval() is subject to
> >interference from other transactions.  And just wait until PostgreSQL
> >8.1 comes out and people start using lastval() -- then it could get
> >*really* confusing which sequence value you're getting.
> 
>     What happens if an INSERT trigger inserts something into another 
>     table  which also has a sequence ?

Using what, lastval()?  The app will get very confused, because it'll
get the value from the sequence used in the trigger.  Using currval
there is no problem, but you already knew that.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)


Re: getting back autonumber just inserted

From
Michael Fuhr
Date:
On Fri, Jul 08, 2005 at 12:26:30AM +0200, PFC wrote:
> 
> >That's a different issue than whether currval() is subject to
> >interference from other transactions.  And just wait until PostgreSQL
> >8.1 comes out and people start using lastval() -- then it could get
> >*really* confusing which sequence value you're getting.
> 
> What happens if an INSERT trigger inserts something into another 
> table  which also has a sequence ?

Do you mean with lastval()?  Here's what happens:

CREATE FUNCTION trigfunc() RETURNS trigger AS $$
BEGIN   INSERT INTO bar (x) VALUES (NEW.x);   RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE TABLE foo (id serial PRIMARY KEY, x integer);
CREATE TABLE bar (id serial PRIMARY KEY, x integer);

CREATE TRIGGER footrig BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE trigfunc();

ALTER SEQUENCE bar_id_seq RESTART WITH 50;

INSERT INTO foo (x) VALUES (12345);

SELECT lastval();lastval 
---------     50
(1 row)

SELECT * FROM foo;id |   x   
----+------- 1 | 12345
(1 row)

SELECT * FROM bar;id |   x   
----+-------50 | 12345
(1 row)

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: getting back autonumber just inserted

From
PFC
Date:
>>     What happens if an INSERT trigger inserts something into another
>>     table  which also has a sequence ?
>
> Using what, lastval()?  The app will get very confused, because it'll
> get the value from the sequence used in the trigger.  Using currval
> there is no problem, but you already knew that.
I knew but I forgot since I use an ORM which handles all the mess ;)




Re: getting back autonumber just inserted

From
PFC
Date:

> Do you mean with lastval()?  Here's what happens:
Hm, interesting, you mean the return value of lastval() also depends if  
you set your constraints  to deferred or immediate ?
I wond


Re: getting back autonumber just inserted

From
Michael Fuhr
Date:
On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote:
> >Do you mean with lastval()?  Here's what happens:
> 
> Hm, interesting, you mean the return value of lastval() also depends 
> if  you set your constraints  to deferred or immediate ?

My mind's ablank trying to contrive a situation where that would
matter.  Can you provide an example?

In any case, I simply meant to point out that 8.1's lastval() will
be seductively convenient because you don't have to pass it a
sequence name, but the value it returns might not be the value you
want.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: getting back autonumber just inserted

From
PFC
Date:
> On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote:
>> >Do you mean with lastval()?  Here's what happens:
>>
>> Hm, interesting, you mean the return value of lastval() also depends
>> if  you set your constraints  to deferred or immediate ?
>
> My mind's ablank trying to contrive a situation where that would
> matter.  Can you provide an example?
It's rather perverse and farfetched, but what would stop one from putting  
some insert statements in a function that happens to be called somewhere  
inside of a check constraint... although one could agree that it's a bit  
shooting oneself in the foot...

> In any case, I simply meant to point out that 8.1's lastval() will
> be seductively convenient because you don't have to pass it a
> sequence name, but the value it returns might not be the value you
> want.
It's the first time I see a MySQLism in postgres !However I like it, cos it might subvert some MySQL users, and provide

 
easy answers to The Weekly Question on the mailing list (ie where is  
AUTO_INCREMENT) ?I've just noticed that I forgot a close() somewhere hence my data import  
files missed a few chunks at the end and MySQL said "3 warnings, 0 errors"  
(if it had failed I would have noticed it a week ago but no, more  
0000-00-00:00:00:00 hand pruning for me now). Uh oh, SHOW WARNINGS isn't  
implemented in that version. There are warnings, but I won't tell you  
where. Somewhere. Hm... grep logs ? yeah, but the error message doesn't  
mention the table name... nor what kind of warning it is that is not an  
error even though half of the row has stayed in an unclosed() file  
buffer...




Re: getting back autonumber just inserted

From
Michael Fuhr
Date:
On Fri, Jul 08, 2005 at 05:03:37AM +0200, PFC wrote:
> >On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote:
> >>Hm, interesting, you mean the return value of lastval() also depends
> >>if  you set your constraints  to deferred or immediate ?
> >
> >My mind's ablank trying to contrive a situation where that would
> >matter.  Can you provide an example?
> 
> It's rather perverse and farfetched, but what would stop one from 
> putting  some insert statements in a function that happens to be called 
> somewhere  inside of a check constraint... although one could agree that 
> it's a bit  shooting oneself in the foot...

PostgreSQL doesn't support deferral of check constraints, only
foreign key constraints.  But yeah, if you could do that then
presumably lastval() might return a different sequence's value
depending on whether the constraints were deferred or immediate.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: getting back autonumber ... Another MsSQL Comparation Question

From
"jimmy.olsen"
Date:
Hi Listers,       I need to know the number of affected (Inserted, deleted, updated)
rows by a Query. In MsSQL I use SELECT @@ROWCOUNT, similar to SELECT
@@IDENTITY. Is there any @@RowCount similar statement in PostGres??

Alessandro - Liga One Sistemas
alessandro@ligaone.com.br


----- Original Message ----- 
From: "Alvaro Herrera" <alvherre@alvh.no-ip.org>
To: "mail TechEvolution" <mail@techevolution.be>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, July 07, 2005 3:03 PM
Subject: Re: [SQL] getting back autonumber just inserted


> On Thu, Jul 07, 2005 at 07:50:16PM +0200, mail TechEvolution wrote:
> > hello
> >
> > i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0
> > (windowsinstaller) on a xp prof platform
> >
> > i would like to get back the autonumber from the last record inserted,
> > for other SQL db (m$ sql db ...) i could use:
> > SELECT @@ IDENTITY
> >
> > can someone help me by informing me what the SQL syntax is to be used
> > with PostGreSQL db and get the same result, the last autonumber
inserted?
>
> You use the currval() function, using the name of the involved sequence
> as parameter.  There is a pg_get_serial_sequence() function, to which
> you give the table name and column name, and it will give you the
> sequence name.
>
> -- 
> Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
> Oh, oh, las chicas galacianas, lo harán por las perlas,
> ¡Y las de Arrakis por el agua! Pero si buscas damas
> Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>



Re: getting back autonumber ... Another MsSQL Comparation Question

From
Michael Fuhr
Date:
[Please start a new thread when asking new questions.]

On Fri, Jul 08, 2005 at 08:56:56AM -0300, jimmy.olsen wrote:
>         I need to know the number of affected (Inserted, deleted, updated)
> rows by a Query. In MsSQL I use SELECT @@ROWCOUNT, similar to SELECT
> @@IDENTITY. Is there any @@RowCount similar statement in PostGres??

Your client interface should have a function to get the row count.
In libpq, for example, you can call PQcmdTuples(); in PL/pgSQL you
can use GET DIAGNOSTICS.  See the documentation for whatever interface
you're using.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: getting back autonumber just inserted

From
Bruno Wolff III
Date:
On Fri, Jul 08, 2005 at 05:03:37 +0200, PFC <lists@boutiquenumerique.com> wrote:
> 
>     It's the first time I see a MySQLism in postgres !

This has meaning in more ways than one.

>     However I like it, cos it might subvert some MySQL users, and 
>     provide  easy answers to The Weekly Question on the mailing list (ie where 
> is  AUTO_INCREMENT) ?

More likely people will shoot themselves in the foot with this feature and come
to the mailing list with questions that take even more time to figure out
than telling them how to use currval(pg_get_serial_sequence().

IMO it is a bad idea to include this feature and that no sane developer will
make use of it in applications.


Re: getting back autonumber just inserted

From
"jimmy.olsen"
Date:
I don't know how to create a Global Variable in Postgres, but the idea is
very simple:
1. Encapsulate the NextVal function in MyNextVal
2. Set to Global variable with NextVal of the desired sequence
3. Inspect to value of the global variable (exactally as it's done in MsSQL)

When Creating a Table:
CREATE TABLE tabTest(
codTest Int Not Null PRIMARY KEY DEFAULT(NextVal("seq_test"),
... # Field List
)

Try this:
CREATE TABLE tabTest(
codTest Int Not Null PRIMARY KEY DEFAULT(MyNextVal("seq_test"),
... # Field List
)

this is not the correct sintax, just an algoritm
CREATE FUNCTION MyNextVal(varchar) RETURNS INT AS
$$ SELECT GlobalVar = NextVal($1)    RETURN GlobalVar
$$



Alessandro - Liga One Sistemas
alessandro@ligaone.com.br
----- Original Message ----- 
From: "Alvaro Herrera" <alvherre@alvh.no-ip.org>
To: "mail TechEvolution" <mail@techevolution.be>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, July 07, 2005 3:03 PM
Subject: Re: [SQL] getting back autonumber just inserted


> On Thu, Jul 07, 2005 at 07:50:16PM +0200, mail TechEvolution wrote:
> > hello
> >
> > i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0
> > (windowsinstaller) on a xp prof platform
> >
> > i would like to get back the autonumber from the last record inserted,
> > for other SQL db (m$ sql db ...) i could use:
> > SELECT @@ IDENTITY
> >
> > can someone help me by informing me what the SQL syntax is to be used
> > with PostGreSQL db and get the same result, the last autonumber
inserted?
>
> You use the currval() function, using the name of the involved sequence
> as parameter.  There is a pg_get_serial_sequence() function, to which
> you give the table name and column name, and it will give you the
> sequence name.
>
> -- 
> Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
> Oh, oh, las chicas galacianas, lo harán por las perlas,
> ¡Y las de Arrakis por el agua! Pero si buscas damas
> Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>



Re: getting back autonumber just inserted

From
Mischa Sandberg
Date:
Quoting "jimmy.olsen" <jimmy.olsen@ig.com.br>:

> I don't know how to create a Global Variable in Postgres, but the
> idea is
> very simple:
> 1. Encapsulate the NextVal function in MyNextVal
> 2. Set to Global variable with NextVal of the desired sequence
> 3. Inspect to value of the global variable (exactally as it's done in
> MsSQL)

Not sure if this thread is about global variables or just how to use
sequences, but ... if you want variables that are global, you can use a
table (global to all processes), a temp table (global to everything
inside one connection), or a row in a global table (can be either). I
tend to keep one global table of (key,value) pairs, with a primary key
constraint so that the query optimizer knows that "SELECT val FROM Env
WHERE key='xxx'" will return at most 1 row. The "keys" are actually
structured paths ("mail.process.thread_limit").




Re: getting back autonumber just inserted

From
Vivek Khera
Date:
On Jul 7, 2005, at 4:14 PM, Theodore Petrosky wrote:

>
> you have to use currval inside a transaction...
>
> begin;
> insert something that increments the counter;
> select currval('sequence_name');
> end;
>
> using currval inside a transaction guarantees that the
> value is correct for your insert statement and has not
> changed by another insert statement.
>

your understanding of currval() is completely incorrect.  no  
transaction is required.


Re: getting back autonumber just inserted

From
Larry Meadors
Date:
Sounds like M$ SuckQL's @@identity value. ;-)

Larry

On 7/7/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote:
> > >Do you mean with lastval()?  Here's what happens:
> >
> > Hm, interesting, you mean the return value of lastval() also depends
> > if  you set your constraints  to deferred or immediate ?
>
> My mind's ablank trying to contrive a situation where that would
> matter.  Can you provide an example?
>
> In any case, I simply meant to point out that 8.1's lastval() will
> be seductively convenient because you don't have to pass it a
> sequence name, but the value it returns might not be the value you
> want.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>


Re: getting back autonumber just inserted

From
Larry Meadors
Date:
If you have a trigger on your table that inserts a record in a table
and shares the same sequence, what value do you get back, the
triggered curval, or the currently inserted one?

Being a lazy bum, this is why I still prefer the "get key - insert
record" approach. Less brain power required. ;-)

Larry

On 7/7/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Thu, Jul 07, 2005 at 01:14:33PM -0700, Theodore Petrosky wrote:
> >
> > you have to use currval inside a transaction...
>
> Not true.  Have you observed otherwise?
>
> > begin;
> > insert something that increments the counter;
> > select currval('sequence_name');
> > end;
> >
> > using currval inside a transaction guarantees that the
> > value is correct for your insert statement and has not
> > changed by another insert statement.
>
> currval() returns the most recently obtained value from the sequence
> in the current session, regardless of what other sessions are doing
> or whether the current session is in a transaction.  See the
> documentation and the FAQ:
>
> http://www.postgresql.org/docs/8.0/static/functions-sequence.html
>
> "Notice that because this is returning a session-local value, it
> gives a predictable answer whether or not other sessions have
> executed nextval since the current session did."
>
> http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3
>
> "4.11.3) Doesn't currval() lead to a race condition with other users?
>
> "No.  currval() returns the current value assigned by your session, not by
> all sessions."
>
> You can do experiments to confirm what the documentation states.
> If you see different behavior then please put together a self-contained
> test case and report it as a bug.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: getting back autonumber just inserted

From
Bruno Wolff III
Date:
On Thu, Jul 07, 2005 at 14:47:23 -0600, Larry Meadors <larry.meadors@gmail.com> wrote:
> If you have a trigger on your table that inserts a record in a table
> and shares the same sequence, what value do you get back, the
> triggered curval, or the currently inserted one?

Whichever one was done second. I think it has to be the trigger, because
I think the inserted values will be evaluated first (even before aa
before trigger) since the triggers need to have access to them.


int to date

From
Daniel Drotos
Date:
Hi,

I have a string containing hexa dump of an 4 bytes integer, like 
'6AF4805C'. Is it possible to convert it somehow to date type in 
plpgsql (v8.0.0-rc1)? Doc says that date is represented as 4 bytes 
internaly.

How can I get internal representation of a date type field? So I would 
be able to produce hexa dump of it like above...

Daniel


Re: int to date

From
Bruno Wolff III
Date:
On Mon, Jul 25, 2005 at 10:54:42 +0200, Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu> wrote:
> Hi,
> 
> I have a string containing hexa dump of an 4 bytes integer, like 
> '6AF4805C'. Is it possible to convert it somehow to date type in 
> plpgsql (v8.0.0-rc1)? Doc says that date is represented as 4 bytes 
> internaly.
> 
> How can I get internal representation of a date type field? So I would 
> be able to produce hexa dump of it like above...

area=> select x'6AF4805C'::integer::abstime;       abstime
------------------------2026-11-11 08:49:00-06
(1 row)

It looks like the your number probably isn't an offset in seconds from
January 1 1970. But with more knowledge about what it is, you should
be able to turn it into a date or timestamp.