pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRE: how do i...
9+ messages / 7 participants
[nested] [flat]
* RE: how do i...
@ 1970-01-01 00:00 omid omoomi <oomoomi@hotmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: omid omoomi @ 1970-01-01 00:00 UTC (permalink / raw)
To: rsanford@nolimitsystems.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* How do I ???
@ 1998-08-26 20:15 Ian Johnston <ian@iprogc.demon.co.uk>
0 siblings, 0 replies; 9+ messages in thread
From: Ian Johnston @ 1998-08-26 20:15 UTC (permalink / raw)
To: pgsql-sql
problem... I want to extract all entries from a database where a date
field is not yet defined. What sort of SQL query can I make that says
'where data_field = NULL' etc.(or something like that) ?
Ian Johnston
Email back to: ian@iprogc.demon.co.uk
^ permalink raw reply [nested|flat] 9+ messages in thread
* HOW DO I...
@ 1999-10-15 12:54 John C Cusick <jcc2@juno.com>
0 siblings, 2 replies; 9+ messages in thread
From: John C Cusick @ 1999-10-15 12:54 UTC (permalink / raw)
To: pgsql-sql
To All,
How do I select from one table into another table
that already exists?
In other words, if I do a:
select * into tblB from tblA where attrN like 'xxx';
and both table structures are identical I get a
ERROR: Relation 'tblB' already exists
If I : select * into table tblB from tblA where attrN like 'xxx';
I get the same answer.
I also tried using 'update'
So, How do I update a smaller table from a larger table?
For those who wish to know, This is a large parts database that
is updated monthly and I have divided it into smaller subsections for
quick access through a web-based interface. Searching the original
table is much too time consuming ( over 130,000 part #'s with Specs and
Descriptions - all running on an ancient 80386). And I would like
to avoid updating the smaller tables by re-building them from scratch.
Thanks for your time,
John C.
___________________________________________________________________
Get the Internet just the way you want it.
Free software, free e-mail, and free Internet access for a month!
Try Juno Web: http://dl.www.juno.com/dynoget/tagj.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] HOW DO I...
@ 1999-10-15 13:14 Mark Jewiss <Mark.Jewiss@knowledge.com>
parent: John C Cusick <jcc2@juno.com>
1 sibling, 0 replies; 9+ messages in thread
From: Mark Jewiss @ 1999-10-15 13:14 UTC (permalink / raw)
To: John C Cusick <jcc2@juno.com>; +Cc: pgsql-sql
Hello,
On Fri, 15 Oct 1999, John C Cusick wrote:
> In other words, if I do a:
> select * into tblB from tblA where attrN like 'xxx';
>
> and both table structures are identical I get a
> ERROR: Relation 'tblB' already exists
Correct, the 'select into...' statement attempts to create the table, not
just insert rows.
I had this problem on a different system (which I haven't tried to do in
pgsql), where the solution was:
insert into tbl1
select * from tbl2 where this = that
I *think* you could declare a value list as in a normal insert. I also
think that this is SQL, not something specific to that db system, but I
may be wrong.
Regards,
Mark.
--
Mark Jewiss
Knowledge Matters Limited
http://www.knowledge.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] HOW DO I...
@ 1999-10-15 13:23 Mathijs Brands <mathijs@ilse.nl>
parent: John C Cusick <jcc2@juno.com>
1 sibling, 0 replies; 9+ messages in thread
From: Mathijs Brands @ 1999-10-15 13:23 UTC (permalink / raw)
To: John C Cusick <jcc2@juno.com>; +Cc: pgsql-sql
On Fri, Oct 15, 1999 at 08:54:01AM -0400, John C Cusick allegedly wrote:
> To All,
>
> How do I select from one table into another table
> that already exists?
>
> In other words, if I do a:
> select * into tblB from tblA where attrN like 'xxx';
>
> and both table structures are identical I get a
> ERROR: Relation 'tblB' already exists
>
> If I : select * into table tblB from tblA where attrN like 'xxx';
>
> I get the same answer.
>
> I also tried using 'update'
>
> So, How do I update a smaller table from a larger table?
>
> For those who wish to know, This is a large parts database that
> is updated monthly and I have divided it into smaller subsections for
> quick access through a web-based interface. Searching the original
> table is much too time consuming ( over 130,000 part #'s with Specs and
> Descriptions - all running on an ancient 80386). And I would like
> to avoid updating the smaller tables by re-building them from scratch.
>
> Thanks for your time,
>
> John C.
Shouldn't you be doing the following?
insert into tblB select * from tblA where attrN like 'xxx';
Mathijs
^ permalink raw reply [nested|flat] 9+ messages in thread
* how do i...
@ 2001-08-12 03:49 Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
0 siblings, 1 reply; 9+ messages in thread
From: Robert J. Sanford, Jr. @ 2001-08-12 03:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: how do i...
@ 2001-08-12 05:11 Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
parent: Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
0 siblings, 1 reply; 9+ messages in thread
From: Robert J. Sanford, Jr. @ 2001-08-12 05:11 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: RE: how do i...
@ 2001-08-12 15:20 Roberto Mello <rmello@cc.usu.edu>
parent: Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
0 siblings, 1 reply; 9+ messages in thread
From: Roberto Mello @ 2001-08-12 15:20 UTC (permalink / raw)
To: Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>; +Cc: pgsql-sql
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!
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: RE: how do i...
@ 2001-08-12 17:17 Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
parent: Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 9+ messages in thread
From: Robert J. Sanford, Jr. @ 2001-08-12 17:17 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2001-08-12 17:17 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 RE: how do i... omid omoomi <oomoomi@hotmail.com>
1998-08-26 20:15 How do I ??? Ian Johnston <ian@iprogc.demon.co.uk>
1999-10-15 12:54 HOW DO I... John C Cusick <jcc2@juno.com>
1999-10-15 13:14 ` Re: [SQL] HOW DO I... Mark Jewiss <Mark.Jewiss@knowledge.com>
1999-10-15 13:23 ` Re: [SQL] HOW DO I... Mathijs Brands <mathijs@ilse.nl>
2001-08-12 03:49 how do i... Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
2001-08-12 05:11 ` Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
2001-08-12 15:20 ` Roberto Mello <rmello@cc.usu.edu>
2001-08-12 17:17 ` Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox