Thread: 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
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 >
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
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!
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 >