Thread: how do i...

how do i...

From
"Robert J. Sanford, Jr."
Date:
first - i'm new at this postgres thing...

i'm coming from a MSAccess and MSSQLServer environment where
i can set up and "AUTOINCREMENT" primary key so that when i
insert a row into a table i don't have to worry about trying
to figure out what the primary key should be.

i know that i can set up a sequence in postgres but i then
have to make sure that all of my inserts use the sequence to
create the primary key and i would like to avoid that.

is there any way that i can set up a sequence so that the
primary key is automatically generated whenever a row is
inserted?

if not, then my plan is to create a stored procedure that
performs the insert with the sequence call. however, i would
like to make sure that ONLY the stored procedure has the
ability to perform the insert and that my developers cannot
insert directly into the table. is this possible? i'm worried
that when i set up a stored procedure it only has the rights
given to it by the caller so if i turn off insert rights for
my applications then the store procedure won't be able to do
the inserts either.

many thanks!

rjsjr



RE: how do i...

From
"Robert J. Sanford, Jr."
Date:
doh!

i have now discovered that every row in every table is
automatically given a globally unique OID. that's pretty
cool. that means that i don't have to personally create
an OID column for every table. excellent.

i also enjoy that i can use the OID as an index on tables.
nice, very nice. thank you for making my life easier.

rjsjr

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Robert 
> J. Sanford, Jr.
> Sent: Saturday, August 11, 2001 10:49 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] how do i...
> 
> 
> first - i'm new at this postgres thing...
> 
> i'm coming from a MSAccess and MSSQLServer environment where
> i can set up and "AUTOINCREMENT" primary key so that when i
> insert a row into a table i don't have to worry about trying
> to figure out what the primary key should be.
> 
> i know that i can set up a sequence in postgres but i then
> have to make sure that all of my inserts use the sequence to
> create the primary key and i would like to avoid that.
> 
> is there any way that i can set up a sequence so that the
> primary key is automatically generated whenever a row is
> inserted?
> 
> if not, then my plan is to create a stored procedure that
> performs the insert with the sequence call. however, i would
> like to make sure that ONLY the stored procedure has the
> ability to perform the insert and that my developers cannot
> insert directly into the table. is this possible? i'm worried
> that when i set up a stored procedure it only has the rights
> given to it by the caller so if i turn off insert rights for
> my applications then the store procedure won't be able to do
> the inserts either.
> 
> many thanks!
> 
> rjsjr
> 
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


RE: how do i...

From
"omid omoomi"
Date:
hi,
In my opinion, using OIDs would not be the best way for your job. Because 
OID is an automatic system column for each db/table it would make some 
conflicts when migrating between multiple DBs or when you need to port or 
move your DB(such as backup/restores , ...) it would not be the best 
solution.
I think we often don't need an increamental number as a primary key at all? 
I mean (considering the performance) you still have the choice to take one 
or more columns as your unique or primary key (if needed at all).
Also you may have your own sequence number as your unique column.
#> Create table foo (f1 text,f2 text,f3 serial)

#> Insert into foo(f1,f2) values('hi','there');
#> Insert into foo(f1,f2) values('this','that');

#> select * from foo;

f1    f2     f3
----------------
hi    there    1
this  that     2

regards
Omid


>From: "Robert J. Sanford, Jr." <rsanford@nolimitsystems.com>
>To: <pgsql-sql@postgresql.org>
>Subject: RE: [SQL] how do i...
>Date: Sun, 12 Aug 2001 00:11:10 -0500
>
>doh!
>
>i have now discovered that every row in every table is
>automatically given a globally unique OID. that's pretty
>cool. that means that i don't have to personally create
>an OID column for every table. excellent.
>
>i also enjoy that i can use the OID as an index on tables.
>nice, very nice. thank you for making my life easier.
>
>rjsjr
>
> > -----Original Message-----
> > From: pgsql-sql-owner@postgresql.org
> > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Robert
> > J. Sanford, Jr.
> > Sent: Saturday, August 11, 2001 10:49 PM
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] how do i...
> >
> >
> > first - i'm new at this postgres thing...
> >
> > i'm coming from a MSAccess and MSSQLServer environment where
> > i can set up and "AUTOINCREMENT" primary key so that when i
> > insert a row into a table i don't have to worry about trying
> > to figure out what the primary key should be.
> >
> > i know that i can set up a sequence in postgres but i then
> > have to make sure that all of my inserts use the sequence to
> > create the primary key and i would like to avoid that.
> >
> > is there any way that i can set up a sequence so that the
> > primary key is automatically generated whenever a row is
> > inserted?
> >
> > if not, then my plan is to create a stored procedure that
> > performs the insert with the sequence call. however, i would
> > like to make sure that ONLY the stored procedure has the
> > ability to perform the insert and that my developers cannot
> > insert directly into the table. is this possible? i'm worried
> > that when i set up a stored procedure it only has the rights
> > given to it by the caller so if i turn off insert rights for
> > my applications then the store procedure won't be able to do
> > the inserts either.
> >
> > many thanks!
> >
> > rjsjr
> >
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
>
>---------------------------(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


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp



Re: RE: how do i...

From
Roberto Mello
Date:
On Sun, Aug 12, 2001 at 12:11:10AM -0500, Robert J. Sanford, Jr. wrote:
> 
> i have now discovered that every row in every table is
> automatically given a globally unique OID. that's pretty
> cool. that means that i don't have to personally create
> an OID column for every table. excellent.
> 
> i also enjoy that i can use the OID as an index on tables.
> nice, very nice. thank you for making my life easier.
It's not a good idea to use OIDs as your primary keys as pointed out
by someone else. Look in the documentation for the SERIAL datatype. It
will create sequences for you and do the equivalent of what you see in the
AUTOINCREMENT types in Access.
-Roberto
-- 
+------------| Roberto Mello - http://www.brasileiro.net |------------+    Computer Science, Utah State University -
http://www.usu.edu     USU Free Software & GNU/Linux Club - http://fslc.usu.edu        Space Dynamics Lab, Developer
http://www.sdl.usu.edu
NEW!!! A Pentium to XT convertion Kit... name?? ---> WINDOWS!


RE: RE: how do i...

From
"Robert J. Sanford, Jr."
Date:
many thanks! ya'll have been a GREAT help!

rjsjr

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Roberto Mello
> Sent: Sunday, August 12, 2001 10:21 AM
> To: Robert J. Sanford, Jr.
> Cc: pgsql-sql@postgresql.org
> Subject: Re: RE: [SQL] how do i...
> 
> 
> On Sun, Aug 12, 2001 at 12:11:10AM -0500, Robert J. 
> Sanford, Jr. wrote:
> > 
> > i have now discovered that every row in every table is
> > automatically given a globally unique OID. that's pretty
> > cool. that means that i don't have to personally create
> > an OID column for every table. excellent.
> > 
> > i also enjoy that i can use the OID as an index on tables.
> > nice, very nice. thank you for making my life easier.
> 
>     It's not a good idea to use OIDs as your primary keys 
> as pointed out
> by someone else. Look in the documentation for the SERIAL 
> datatype. It
> will create sequences for you and do the equivalent of what 
> you see in the
> AUTOINCREMENT types in Access.
> 
>     -Roberto
> -- 
> +------------| Roberto Mello - http://www.brasileiro.net 
> |------------+
>      Computer Science, Utah State University - http://www.usu.edu
>        USU Free Software & GNU/Linux Club - http://fslc.usu.edu
>          Space Dynamics Lab, Developer  http://www.sdl.usu.edu
> NEW!!! A Pentium to XT convertion Kit... name?? ---> WINDOWS!
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
>