pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
RE: 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