Postgres Pro
Downloads
Home   >   mailing lists

Thread: selecting the last record from a table

selecting the last record from a table

From
MT
Date:
I have a script that inserts a record into a table and increments the
unique field using nextval('my_sequence'). My problem is once I have
inserted the record, I'd like to select this same record and display it
so that the user knows he has actually inserted the record. I'm
wondering if there's a bit of sql to select the last record.

Thanks for your help,

Mark Tessier

Re: selecting the last record from a table

From
Doug McNaught
Date:
MT <mt@open2web.com> writes:

> I have a script that inserts a record into a table and increments the
> unique field using nextval('my_sequence'). My problem is once I have
> inserted the record, I'd like to select this same record and display
> it so that the user knows he has actually inserted the record. I'm
> wondering if there's a bit of sql to select the last record.

SELECT * FROM mytable WHERE my_unique_field = currval('my_sequence');

-Doug

Re: selecting the last record from a table

From
Medi Montaseri
Date:
Actually when you insert into a table, PG gives allows you to fetch the
OID it just created...what
language are you using.....

Here is psql(1)'s session

Test1=> insert into test values ('something');
INSERT 36205 1
Test1=>


Doug McNaught wrote:

>MT <mt@open2web.com> writes:
>
>
>
>>I have a script that inserts a record into a table and increments the
>>unique field using nextval('my_sequence'). My problem is once I have
>>inserted the record, I'd like to select this same record and display
>>it so that the user knows he has actually inserted the record. I'm
>>wondering if there's a bit of sql to select the last record.
>>
>>
>
>SELECT * FROM mytable WHERE my_unique_field = currval('my_sequence');
>
>-Doug
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>

Re: selecting the last record from a table

From
Ericson Smith
Date:
You might want to get the sequence before you even do the insert...

SELECT nextval('my_sequence') as id

Then do the insert with the sequence and all other operations with the
"id". Sure you might have a few holes in the sequence if you abort an
insert, but this way you dont have to mess with OID's etc.

- Ericson

On Tue, 2002-11-19 at 14:07, Doug McNaught wrote:
> MT <mt@open2web.com> writes:
>
> > I have a script that inserts a record into a table and increments the
> > unique field using nextval('my_sequence'). My problem is once I have
> > inserted the record, I'd like to select this same record and display
> > it so that the user knows he has actually inserted the record. I'm
> > wondering if there's a bit of sql to select the last record.
>
> SELECT * FROM mytable WHERE my_unique_field = currval('my_sequence');
>
> -Doug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Re: selecting the last record from a table

From
Andrew Sullivan
Date:
On Tue, Nov 19, 2002 at 11:24:53AM -0800, Medi Montaseri wrote:
> Actually when you insert into a table, PG gives allows you to fetch the
> OID it just created...what

True, but access by OID on a large table can be painful, as they
aren't indexed by default.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110