Thread: Documentation about Postgres architecture

Hi,

Is there any documentation where I could find information about Postgres
architecture.
We are looking for a new database to take place of the one we use today and
think Postgres will be the new one.
We would like to know about internal architecture to know if it can match
our needs or if we could make changes to match them.

Thanks

Patrick Fiche

HI!

i have a little question:

how may i easyly obtain row numbers in a query:

for example how may i obtain:

select <<row_number>>, * from a_table_or_view;

i did some experiences with sequences but without satisfying results;

thx for any hint.


Marcin Inkielman

Marcin Inkielman writes:

> how may i easyly obtain row numbers in a query:

In the SQL data model, rows don't have numbers, because rows aren't
ordered -- a query may return the rows in any order. So if you are
building on that assumption you might have to rethink slightly.

If you need to number your records for some reason, say invoice numbers,
you can use a sequence:

create table my_tbl (
    nr serial, -- creates implicit sequence
    ... more fields ...
);

Then you can select them like any other field. You say you had
unsatisfying results, would you care to explain why?

Lastly, there is the OID which every row gets automatically assigned by
the system. This is a unique number across the whole installation. You can
access it as
    select oid, your, fields, here from table ...
like a regular column. The OID may be good enough to get some sort of
number on a row but be aware that it is wildly non-portable.


--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Hi!

thanks for your respose!

sorry if i was not very clear with my question...

On Wed, 10 May 2000, Peter Eisentraut wrote:

> Marcin Inkielman writes:
>
> > how may i easyly obtain row numbers in a query:
>
> In the SQL data model, rows don't have numbers, because rows aren't
> ordered -- a query may return the rows in any order. So if you are

sure, i know that

> building on that assumption you might have to rethink slightly.

>
> If you need to number your records for some reason, say invoice numbers,
> you can use a sequence:

that is exactly i want to obtain. i would like to have:

1 | first item
2 | second,
3 | ... and so on.

each time i do a select on a table and aways starting with a "1".

>
> create table my_tbl (
>     nr serial, -- creates implicit sequence
>     ... more fields ...
> );
>
> Then you can select them like any other field. You say you had
> unsatisfying results, would you care to explain why?

the main problem i have with sequences (or temporary tables - as i think
you suggest) is that i have multiples concurrent transactions at he same
time. as they are generated by a single cgi script, if i use a sequence it
will have the same name for each transaction, and therefore only the first
trasaction will work. of course i may lock it just like a simple table but
i am not sure this is a simpliest way. i am just searching something
simplier

>
> Lastly, there is the OID which every row gets automatically assigned by
> the system. This is a unique number across the whole installation. You can
> access it as
>     select oid, your, fields, here from table ...
> like a regular column. The OID may be good enough to get some sort of
> number on a row but be aware that it is wildly non-portable.

i thought to use oids but i am no sure if it is possible to obtain the
result i require

>
>
>

Marcin Inkielman