Re: [GENERAL] Primary Key

>From the way the table was filled, I knew there were no nulls. It succeeded. On Thu, Nov 21, 2013 at 8:15 PM, Joshua D. Drake wrote: > > On 11/21/2013 12:40 PM, Joey Quinn wrote: > >> I have a table (5 columns) with approximately 670 million rows. It has >> had an index (unique) on an inet col

Re: [GENERAL] Primary Key

On 11/21/2013 12:40 PM, Joey Quinn wrote: I have a table (5 columns) with approximately 670 million rows. It has had an index (unique) on an inet column from the beginning. Today I added a primary key constraint based on the same column thinking that since it already had an index, this would be

Re: [GENERAL] Primary Key

yep, that worked... thanks again. On Thu, Nov 21, 2013 at 4:38 PM, Joey Quinn wrote: > Ahhh, that's what I was missing... thank-you. (just launched, we'll see > how that one goes). > > > > On Thu, Nov 21, 2013 at 3:48 PM, Elliot wrote: > >> On 2013-11-21 15:40, Joey Quinn wrote: >> >>> I have a

Re: [GENERAL] Primary Key

Ahhh, that's what I was missing... thank-you. (just launched, we'll see how that one goes). On Thu, Nov 21, 2013 at 3:48 PM, Elliot wrote: > On 2013-11-21 15:40, Joey Quinn wrote: > >> I have a table (5 columns) with approximately 670 million rows. It has >> had an index (unique) on an inet co

Re: [GENERAL] Primary Key

On 2013-11-21 15:40, Joey Quinn wrote: I have a table (5 columns) with approximately 670 million rows. It has had an index (unique) on an inet column from the beginning. Today I added a primary key constraint based on the same column thinking that since it already had an index, this would be a

[GENERAL] Primary Key

I have a table (5 columns) with approximately 670 million rows. It has had an index (unique) on an inet column from the beginning. Today I added a primary key constraint based on the same column thinking that since it already had an index, this would be a relatively quick operation. That does not a

Re: [GENERAL] Primary Key Index Bloat?

On Sun, Nov 17, 2013 at 10:48 PM, David Johnston wrote: > I am preparing to REINDEX the unique index and DROP the non-unique one over > the same field - probably Tuesday evening. Does everything I am saying here > sound kosher or would someone like me to provide additional information? [...] >

Re: [GENERAL] Primary Key Index Bloat?

On 11/17/2013 11:48 PM, David Johnston wrote: > I am guessing that it is the need for the index to point to new versions of > the physical record that the index is churning so much and causing this kind > of bloat? Bingo. > I am preparing to REINDEX the unique index and DROP the non-unique one ov

[GENERAL] Primary Key Index Bloat?

Hi, I have a table with the following usage characteristic: INSERT bulk data using INSERT INTO ... SELECT ... FROM -- this table uses a varchar(50) for the PK -- the PK is rarely (effectively never unless a mistake was made) altered -- always appending to the existing table; some bulk deletions i

Re: [GENERAL] Primary key vs unique index

On Fri, Jul 6, 2012 at 2:07 PM, rverghese wrote: > Yes I am using that option for one of my POstgres 9.1 database and it works > well. But its still an issue with Foreign keys, which you need to drop and > recreate . Having to drop and create foriegn keys is a legitimate concern. I am looking i

Re: [GENERAL] Primary key vs unique index

Yes I am using that option for one of my POstgres 9.1 database and it works well. But its still an issue with Foreign keys, which you need to drop and recreate . Also I use Slony for replication and it uses the primary key to check repl. So I don't want that to be interrupted by dropping PK and rec

Re: [GENERAL] Primary key vs unique index

Bloat in primary key indexes has been a long standing issue (although not faced by many), and especially since online rebuild of primary keys was never possible in production environments. Since version 9.1 we have a nice little feature of being able to change a primary key's underlying index. Loo

Re: [GENERAL] Primary key vs unique index

On Fri, Jul 06, 2012 at 09:07:53AM -0700, rverghese wrote: > We are experiencing a similar problem, even though we are on 8.4 and have > been for a while, and have autovacuum turned on. I have regular concurrent > reindexes on the indexes but the primary key is seriously bloated. I was > considerin

Re: [GENERAL] Primary key vs unique index

We are experiencing a similar problem, even though we are on 8.4 and have been for a while, and have autovacuum turned on. I have regular concurrent reindexes on the indexes but the primary key is seriously bloated. I was considering doing the same thing, that is, create another primary key that is

Re: [GENERAL] Primary key Index Error

On Tue, Oct 25, 2011 at 2:41 AM, Raghavendra wrote: > On Tue, Oct 25, 2011 at 11:40 AM, Manoj K P wrote: >> >> Server log Oct 1 00:06:59 server_host_name postgres[1453]: [5-1] >> 2011-10-01 00:06:59.831 EDT 1453 4e869041.5ad postgres [local] postgres LOG: >> duration: 418583.238 ms statement: sel

Re: [GENERAL] Primary key Index Error

On Tue, Oct 25, 2011 at 11:40 AM, Manoj K P wrote: > *Server log* Oct 1 00:06:59 server_host_name postgres[1453]: [5-1] > 2011-10-01 00:06:59.831 EDT 1453 4e869041.5ad postgres [local] postgres LOG: > duration: 418583.238 ms statement: select pg_start_backup('fortnightly'); > Oct 2 03:03:18 serve

Re: [GENERAL] Primary key Index Error

*Server log* Oct 1 00:06:59 server_host_name postgres[1453]: [5-1] 2011-10-01 00:06:59.831 EDT 1453 4e869041.5ad postgres [local] postgres LOG: duration: 418583.238 ms statement: select pg_start_backup('fortnightly'); Oct 2 03:03:18 server_host_name postgres[1453]: [6-1] 2011-10-02 03:03:18.2

Re: [GENERAL] Primary key Index Error

On Mon, Oct 24, 2011 at 3:22 AM, Manoj K P wrote: > Postgres recovered from data folder , after that some queries shows error > > > select * from table2 order by app_id  ; - its work ( 5000 data) > select * from table2 order by app_id   desc ; - its work > > Here app_id contains  binary index

Re: [GENERAL] Primary key Index Error

On Mon, Oct 24, 2011 at 8:32 AM, manoj wrote: > On 10/24/2011 06:38 PM, Merlin Moncure wrote: > > On Mon, Oct 24, 2011 at 3:22 AM, Manoj K P wrote: > > Postgres recovered from data folder , after that some queries shows error > > > select * from table2 order by app_id  ; - its work ( 5000 dat

Re: [GENERAL] Primary key Index Error

On 10/24/2011 06:38 PM, Merlin Moncure wrote: On Mon, Oct 24, 2011 at 3:22 AM, Manoj K P wrote: Postgres recovered from data folder , after that some queries shows error select * from table2 order by app_id ; - its work ( 5000 data) select * from table2 order by app_id desc ; - its wor

[GENERAL] Primary key Index Error

Postgres recovered from data folder , after that some queries shows error select * from table2 order by app_id ; - its work ( 5000 data) select * from table2 order by app_id desc ; - its work Here app_id contains binary index select * from table2 order by id ; - its work ( 5000

Re: [GENERAL] Primary key vs unique index

Voils, Steven M Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Primary key vs unique index On Fri, Mar 18, 2011 at 6:38 AM, Voils, Steven M wrote: > What are the general guidelines under which autovacuum will trigger?  I was > unaware it was turned on by default for the newer v

Re: [GENERAL] Primary key vs unique index

On Fri, Mar 18, 2011 at 6:38 AM, Voils, Steven M wrote: > What are the general guidelines under which autovacuum will trigger?  I was > unaware it was turned on by default for the newer versions.  Would it be > worthwhile to leave the manual vacuuming on?  Currently it runs immediately > after

Re: [GENERAL] Primary key vs unique index

On Fri, Mar 18, 2011 at 8:38 AM, Voils, Steven M wrote: > What are the general guidelines under which autovacuum will trigger? I was > unaware it was turned on by default for the newer versions. Would it be > worthwhile to leave the manual vacuuming on? Currently it runs immediately > after lar

Re: [GENERAL] Primary key vs unique index

expected that autovac would pick these changes up and run anyway? -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Friday, March 18, 2011 8:36 AM To: Voils, Steven M Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Primary key vs unique index Autovacuum is

Re: [GENERAL] Primary key vs unique index

, 2011 6:31 PM To: Voils, Steven M Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Primary key vs unique index On Thu, Mar 17, 2011 at 12:59 PM, Voils, Steven M wrote: > Thanks for the reply.  I should have mentioned in the first post that we do > delete significant amounts of the

Re: [GENERAL] Primary key vs unique index

e- > From: Scott Marlowe [mailto:scott.marl...@gmail.com] > Sent: Thursday, March 17, 2011 6:31 PM > To: Voils, Steven M > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Primary key vs unique index > > On Thu, Mar 17, 2011 at 12:59 PM, Voils, Steven M > wr

Re: [GENERAL] Primary key vs unique index

On Thu, Mar 17, 2011 at 12:59 PM, Voils, Steven M wrote: > Thanks for the reply.  I should have mentioned in the first post that we do > delete significant amounts of the table which I thought was the cause of the > bloat.  We are already performing automatic vacuums nightly. Automatic regular

Re: [GENERAL] Primary key vs unique index

To: Voils, Steven M Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Primary key vs unique index On Mar 17, 2011, at 6:19 AM, Voils, Steven M wrote: > Is there a fundamental difference between a primary key and a unique index? > Currently we have primary keys on tables that have sig

Re: [GENERAL] Primary key vs unique index

] Sent: Thursday, March 17, 2011 2:52 PM To: Voils, Steven M Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Primary key vs unique index On Thu, Mar 17, 2011 at 12:51 PM, Scott Marlowe wrote: > On Thu, Mar 17, 2011 at 6:19 AM, Voils, Steven M > wrote: >> Is there a fundamenta

Re: [GENERAL] Primary key vs unique index

On Thu, Mar 17, 2011 at 12:51 PM, Scott Marlowe wrote: > On Thu, Mar 17, 2011 at 6:19 AM, Voils, Steven M > wrote: >> Is there a fundamental difference between a primary key and a unique index? >> Currently we have primary keys on tables that have significant amounts of >> updates performed on t

Re: [GENERAL] Primary key vs unique index

On Thu, Mar 17, 2011 at 6:19 AM, Voils, Steven M wrote: > Is there a fundamental difference between a primary key and a unique index? > Currently we have primary keys on tables that have significant amounts of > updates performed on them, as a result the primary key indexes are becoming > signific

Re: [GENERAL] Primary key vs unique index

On Mar 17, 2011, at 6:19 AM, Voils, Steven M wrote: > Is there a fundamental difference between a primary key and a unique index? > Currently we have primary keys on tables that have significant amounts of > updates performed on them, as a result the primary key indexes are becoming > signific

[GENERAL] Primary key vs unique index

Is there a fundamental difference between a primary key and a unique index? Currently we have primary keys on tables that have significant amounts of updates performed on them, as a result the primary key indexes are becoming significantly bloated. There are other indexes on the tables that al

Re: [GENERAL] Primary key

On Mon, 2011-03-14 at 16:03 +, Raymond O'Donnell wrote: > On 14/03/2011 15:56, Raymond O'Donnell wrote: > > On 14/03/2011 15:35, Allan Kamau wrote: > > > >> CREATE SEQUENCE hy3_pack_seq MINVALUE 1000; > >> CREATE TABLE hy3_pack > >> ( > >> hy3_id INTEGER NOT NULL DEFAULT nextval('hy3_pack_seq')

Re: [GENERAL] Primary key

On 14/03/2011 15:56, Raymond O'Donnell wrote: On 14/03/2011 15:35, Allan Kamau wrote: CREATE SEQUENCE hy3_pack_seq MINVALUE 1000; CREATE TABLE hy3_pack ( hy3_id INTEGER NOT NULL DEFAULT nextval('hy3_pack_seq') -- or hy3_id BIGINT NOT NULL DEFAULT nextval('hy3_pack_seq') That's what SERIAL doe

Re: [GENERAL] Primary key

On 14/03/2011 15:35, Allan Kamau wrote: CREATE SEQUENCE hy3_pack_seq MINVALUE 1000; CREATE TABLE hy3_pack ( hy3_id INTEGER NOT NULL DEFAULT nextval('hy3_pack_seq') -- or hy3_id BIGINT NOT NULL DEFAULT nextval('hy3_pack_seq') That's what SERIAL does for you, in one go - it's just syntactic suga

Re: [GENERAL] Primary key

On Mon, Mar 14, 2011 at 10:58 AM, Peter Evens wrote: > hello, > > i have a question about the PRIMARY KEY, > > how can we let it start from for example 1000 instead of 1? > > This is our program: > > CREATE TABLE hy3_pack > > ( > > hy3_id serial NOT NULL, > > hy3_serie_nummer text NOT NULL, > > hy

Re: [GENERAL] primary key

On Monday, March 14, 2011 12:57:07 am Peter Evens wrote: > hello, > > i have a question about the PRIMARY KEY, > how can we let it start from for example 1000 instead of 1? > > This is our program: > CREATE TABLE hy3_pack > ( > hy3_id serial NOT NULL, > hy3_serie_nummer text NOT NULL, > hy3_ba

Re: [GENERAL] primary key

On 14/03/2011 07:57, Peter Evens wrote: hello, i have a question about the PRIMARY KEY, how can we let it start from for example 1000 instead of 1? This is our program: CREATE TABLE hy3_pack ( hy3_id serial NOT NULL, hy3_serie_nummer text NOT NULL, hy3_barcode text NOT NULL, hy3_type_v

[GENERAL] Primary key

hello, i have a question about the PRIMARY KEY, how can we let it start from for example 1000 instead of 1? This is our program: CREATE TABLE hy3_pack ( hy3_id serial NOT NULL, hy3_serie_nummer text NOT NULL, hy3_barcode text NOT NULL, hy3_type_vulling text NOT NULL, hy3_tarra_gewicht te

[GENERAL] primary key

hello, i have a question about the PRIMARY KEY, how can we let it start from for example 1000 instead of 1? This is our program: CREATE TABLE hy3_pack ( hy3_id serial NOT NULL, hy3_serie_nummer text NOT NULL, hy3_barcode text NOT NULL, hy3_type_vulling text NOT NULL, hy3_tarra_gewicht text,

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

On 29 Jan 2010, at 1:56, Yan Cheng Cheok wrote: > Isn't the primary constraint will implicitly create an index for day already? > > PRIMARY KEY (advertiser_id, day), Yes, but it's not a very efficient index to look for values of day if you don't provide a value for advertiser_id as well. See

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

Sorry. I didn't get all your points. "defining a primary key constraint implicitly creates an index" - Yup. I agree on this. But what is the purpose, for author to explicitly define index for day? CREATE INDEX impressions_by_day_y2012m3ms2_day ON impressions_by_day_y2012m3ms2 (day); Isn't the

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

On 28 Jan 2010, at 2:10, Yan Cheng Cheok wrote: >>> EXECUTE 'CREATE TABLE ' || >> quote_ident(measurement_table_name) || ' >>> ( >>> CONSTRAINT >> pk_measurement_id_' || measurement_table_index || ' PRIMARY >> KEY (measurement_id), >>> CONSTRAINT >> fk_unit_id_'

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

Thanks and Regards Yan Cheng CHEOK --- On Thu, 1/28/10, Alban Hertroys wrote: > From: Alban Hertroys > Subject: Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table > Partition > To: "Yan Cheng Cheok" > Cc: pgsql-general@postgresql.org > Da

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

On 27 Jan 2010, at 4:22, Yan Cheng Cheok wrote: > Hello all, > > I solve my problem using the following. It seems that when inherit from > parent table, the parent table's constraint is not being carried over to > child table. > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > RETUR

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

On 27 Jan 2010, at 2:00, Yan Cheng Cheok wrote: >>> However, whenever I insert row into measurement table, >> I realize its primary key value is going from 2, 4, 6, 8, >> 10... >>> >>> May I know how can I prevent this? >> >> Apparently nextval on that sequence gets called multiple >> times in yo

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

Hello all, I solve my problem using the following. It seems that when inherit from parent table, the parent table's constraint is not being carried over to child table. CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $BODY$DECLARE measurement_table_index bigint;

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

Thanks and Regards Yan Cheng CHEOK --- On Tue, 1/26/10, Alban Hertroys wrote: > From: Alban Hertroys > Subject: Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table > Partition > To: "Yan Cheng Cheok" > Cc: pgsql-general@postgresql.org > D

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

On 26 Jan 2010, at 11:00, Yan Cheng Cheok wrote: > However, whenever I insert row into measurement table, I realize its primary > key value is going from 2, 4, 6, 8, 10... > > May I know how can I prevent this? Apparently nextval on that sequence gets called multiple times in your queries. > A

[GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

Currently, I have a table which I implement table (measurement) partition policy. http://www.postgresql.org/docs/current/static/ddl-partitioning.html Whenever an item being inserted into measurement table, modulo will be perform on measurement table primary key. Then, by using the result of mod

[GENERAL] primary key and foreign keys

Hi folks, I am trying to do a little bit of changes in one of my DBs, basically it involves changing primary keys to just unique index (the columns are already not null); I was thinking about just dropping Pk in transaction, creating unique index, and creating PK on other column), but I can't do t

Re: [GENERAL] Primary Key with auto increment field

x asasaxax wrote: > I would like to increment one field, depending of the value of the > other. here´s an example: Didn't you just ask this question with practically the same wording a few days ago? What was wrong with the answers then? What has changed? What's the *difference* this time? Repeat

Re: [GENERAL] Primary Key with serial the solution?

x asasaxax wrote: > How about if i do this inside a procedure: > > SELECT setval('sequence',(SELECT max(id) FROM table)) INTO variable; > insert into table values(variable, ..., ...); ? > > Will this be transactional? Cause, they say that setval is a command > that its transactional. Using this

[GENERAL] Primary Key with auto increment field

I would like to increment one field, depending of the value of the other. here´s an example: id variable 1 1 2 1 1 2 2 2 can i do that with the following commands? SELECT setval('sequence',(SELECT max(id) FROM table)) INTO variable; insert into table values(variable, ...,

Re: [GENERAL] Primary Key with serial the solution?

How about if i do this inside a procedure: SELECT setval('sequence',(SELECT max(id) FROM table)) INTO variable; insert into table values(variable, ..., ...); ? Will this be transactional? Cause, they say that setval is a command that its transactional. Using this way i don´t will need to use a s

Re: [GENERAL] Primary Key with serial

can anyone do a example for me.. an explain how it works? Thanks a lot 2008/3/29, Berend Tober <[EMAIL PROTECTED]>: > > x asasaxax wrote: > >I have the following tablecreate table product(cod serial, > user_cod > > bigint, constraint product_fk Foreign Key(user_cod) references > user(cod

Re: [GENERAL] Primary Key with serial

x asasaxax wrote: I have the following tablecreate table product(cod serial, user_cod bigint, constraint product_fk Foreign Key(user_cod) references user(cod), constraint product_pk Primary Key(cod, user_cod)); What i want to happend is that: user_codcod 1

Re: [GENERAL] Primary Key with serial

x asasaxax wrote: Hi, I have the following tablecreate table product(cod serial, user_cod bigint, constraint product_fk Foreign Key(user_cod) references user(cod), constraint product_pk Primary Key(cod, user_cod)); What i want to happend is that: user_codcod 1

[GENERAL] Primary Key with serial

Hi, I have the following tablecreate table product(cod serial, user_cod bigint, constraint product_fk Foreign Key(user_cod) references user(cod), constraint product_pk Primary Key(cod, user_cod)); What i want to happend is that: user_codcod 1 1 1

Re: [GENERAL] Primary Key

On Nov 26, 2007 1:11 PM, Steve Crawford <[EMAIL PROTECTED]> wrote: > It's worse than that. > > If we presume that the plate is a key to a vehicle, then we immediately > run into problems as a vehicle can, over time, have several plates > (lost, stolen, changed to vanity...) and a plate can belong,

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/26/07 12:11, Steve Crawford wrote: [snip] > > If we presume that the plate is a key to a vehicle, then we immediately > run into problems as a vehicle can, over time, have several plates > (lost, stolen, changed to vanity...) and a plate can bel

Re: [GENERAL] Primary Key

Joshua D. Drake wrote: > On Mon, 26 Nov 2007 10:28:03 -0800 (PST) > Richard Broersma Jr <[EMAIL PROTECTED]> wrote: >> --- On Mon, 11/26/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: >>> In "theory" the item that would be a natural key >>> in this instance is the VIN. And you then need to deal wi

Re: [GENERAL] Primary Key

On Nov 26, 2007 1:30 PM, Steve Crawford <[EMAIL PROTECTED]> wrote: > I'm sure someone has defined a "vehicle", but I don't know what number > applies when you've pieced together a rebuilt engine, salvaged > transmission, junkyard hood and so-on to get a working car. I think > custom builders end u

Re: [GENERAL] Primary Key

Joshua D. Drake wrote: In "theory" the item that would be a natural key in this instance is the VIN. You would of course have to make some kind of allowance for cars that don't have a VIN (nothing in the last what... 50 years?). And some kind of allowance for Title 49, Sec. 565.4, subsection (d)

Re: [GENERAL] Primary Key

> It's worse than that. It's even worse than that. Decades ago, Florida used to issue multiple plates with the same number, differentiated by color. There are other cases of states having multiple types of license plates, with overlapping numbers. -- Scott Ribe [EMAIL PROTECTED] http://www.kil

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 26 Nov 2007 10:28:03 -0800 (PST) Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > --- On Mon, 11/26/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > > In "theory" the item that would be a natural key > > in this instance is the VIN. You wou

Re: [GENERAL] Primary Key

> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Richard Broersma Jr > Sent: Monday, November 26, 2007 10:28 AM > To: Joshua D. Drake > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Primary Key > > --- On

Re: [GENERAL] Primary Key

--- On Mon, 11/26/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > In "theory" the item that would be a natural key > in this instance is the VIN. You would of course have > to make some kind of allowance for cars that don't > have a VIN (nothing in the last what... > 50 years?). So this is why t

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 26 Nov 2007 10:11:37 -0800 Steve Crawford <[EMAIL PROTECTED]> wrote: > Although I haven't seen it much, recently, semi-trucks used to > regularly have with numerous plates - one for each state in which > they operated. And some states such as

Re: [GENERAL] Primary Key

Martijn van Oosterhout wrote: On Fri, Nov 23, 2007 at 09:33:13AM +, Peter Childs wrote: I tend to agree that primary keys should be single fields if they need to be referenced but should also be natural if at all possible. ie use car number plates rather than some serial int. Car n

Re: [GENERAL] Primary Key

On Nov 23, 2007, at 17:35 , James B. Byrne wrote: Why is this desire not better satisfied by an index rather than a key? What's your distinction between an index and a key? For what it's worth, both UNIQUE and PRIMARY KEY constrain a column (or set of columns) to be unique, or a key in th

Re: [GENERAL] Primary Key

On: Fri, 23 Nov 2007 09:33:13 +, "Peter Childs" <[EMAIL PROTECTED]> wrote: > The worse thing I meet is people who think primary keys need to be > integer single field unique serial fields > > I tend to agree that primary keys should be single fields if they > need to be referenced but should a

Re: [GENERAL] Primary Key

Ron Johnson <[EMAIL PROTECTED]> writes: > On 11/23/07 03:33, Peter Childs wrote: >> The worse thing I meet is people who think primary keys need to be >> integer single field unique serial fields >> >> I tend to agree that primary keys should be single fields if they need >> to be referenced but s

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/23/07 03:33, Peter Childs wrote: > > > On 21/11/2007, *Sascha Bohnenkamp* <[EMAIL PROTECTED] > > wrote: > > > Fie on you evil synthetic key lovers. Long live the Natural Key! > > think of an foreign key refe

Re: [GENERAL] Primary Key

On Fri, Nov 23, 2007 at 12:00:18PM +0100, Csaba Nagy wrote: > I'm not sure how it is in the US, but here in Germany I just reused a > car plate from the owner it had before me... so now the plate is > uniquely associated at most with the car, not the owner... and I'm > pretty sure that's not unique

Re: [GENERAL] Primary Key

On Fri, 2007-11-23 at 11:37 +0100, Martijn van Oosterhout wrote: > On Fri, Nov 23, 2007 at 09:33:13AM +, Peter Childs wrote: > > I tend to agree that primary keys should be single fields if they need to be > > referenced but should also be natural if at all possible. ie use car number > > plate

Re: [GENERAL] Primary Key

On Fri, Nov 23, 2007 at 09:33:13AM +, Peter Childs wrote: > I tend to agree that primary keys should be single fields if they need to be > referenced but should also be natural if at all possible. ie use car number > plates rather than some serial int. Car number plates are unique over time? I

Re: [GENERAL] Primary Key

On 21/11/2007, Sascha Bohnenkamp <[EMAIL PROTECTED]> wrote: > > > Fie on you evil synthetic key lovers. Long live the Natural Key! > > think of an foreign key referencing a primary key with 6 columns *urgs* > never! The worse thing I meet is people who think primary keys need to be integer sing

Re: [GENERAL] Primary Key

> Fie on you evil synthetic key lovers. Long live the Natural Key! think of an foreign key referencing a primary key with 6 columns *urgs* never! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate s

Re: [GENERAL] Primary Key

On Sun, Nov 18, 2007 at 04:19:01AM +, Ron Johnson wrote: > If the PK was synthetic and generated by the engine, then a (buggy) > app could insert duplicate tolls and the system wouldn't utter a > peep. But the customer sure would when he saw the duplicate entries. You'd just need to put a UNI

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/17/07 01:21, Gregory Stark wrote: > "Ron Johnson" <[EMAIL PROTECTED]> writes: > >> On 11/16/07 12:50, João Paulo Zavanela wrote: >>> Hello, >>> >>> How many fields is recomended to create a primary key? >>> I'm thinking to create one with 6 fiel

Re: [GENERAL] Primary Key

"Ron Johnson" <[EMAIL PROTECTED]> writes: > On 11/16/07 12:50, João Paulo Zavanela wrote: >> Hello, >> >> How many fields is recomended to create a primary key? >> I'm thinking to create one with 6 fields, is much? > > The number of recommended fields is the *minimum* number required > for unique

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/16/07 20:50, Greg Smith wrote: [snip] > > He doesn't use that example anymore but still misses the point I tried > to make. The ability of the world to invalidate the assumptions that go > into natural key assignment are really impressive. I p

Re: [GENERAL] Primary Key

On Nov 16, 2007 9:50 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Fri, 16 Nov 2007, Merlin Moncure wrote: > > the sad fact is that sequences have made developers lazy > > Nah, developers were lazy long before that. If you ask Larry Wall it's a > virtue. well, 'lazy' in the sense that it encoura

Re: [GENERAL] Primary Key

On Fri, 16 Nov 2007, Merlin Moncure wrote: the sad fact is that sequences have made developers lazy Nah, developers were lazy long before that. If you ask Larry Wall it's a virtue. I gave up on this argument ten years ago after a long battle with well-known natural key zealot Joe Celko wo

Re: [GENERAL] Primary Key

On Fri, Nov 16, 2007 at 04:41:42PM -0500, Merlin Moncure wrote: > On Nov 16, 2007 3:21 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > > On Fri, Nov 16, 2007 at 12:06:46PM -0800, Joshua D. Drake wrote: > > > When that is needed I do this: > > > > > > create table foo(id serial unique, a text, b text, pr

Re: [GENERAL] Primary Key

On Fri, Nov 16, 2007 at 12:06:46PM -0800, Joshua D. Drake wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Fri, 16 Nov 2007 20:00:29 + > Sam Mason <[EMAIL PROTECTED]> wrote: > > > > > > Normally a primary key would just be a single column. When > > > > you start going to that

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/16/07 12:50, João Paulo Zavanela wrote: > Hello, > > How many fields is recomended to create a primary key? > I'm thinking to create one with 6 fields, is much? The number of recommended fields is the *minimum* number required for uniqueness.

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 16 Nov 2007 18:54:22 + Sam Mason <[EMAIL PROTECTED]> wrote: > On Fri, Nov 16, 2007 at 03:50:20PM -0300, Jo??o Paulo Zavanela wrote: > > How many fields is recomended to create a primary key? > > I'm thinking to create one with 6 fields, is

Re: [GENERAL] Primary Key

On Nov 16, 2007 3:21 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > On Fri, Nov 16, 2007 at 12:06:46PM -0800, Joshua D. Drake wrote: > > When that is needed I do this: > > > > create table foo(id serial unique, a text, b text, primary (a,b)); > > Humm, so the other way around from what I've ended up do

Re: [GENERAL] Primary Key

On Fri, Nov 16, 2007 at 12:06:46PM -0800, Joshua D. Drake wrote: > When that is needed I do this: > > create table foo(id serial unique, a text, b text, primary (a,b)); Humm, so the other way around from what I've ended up doing. I'll need to think about the implications of changing things aroun

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 16 Nov 2007 20:00:29 + Sam Mason <[EMAIL PROTECTED]> wrote: > > > Normally a primary key would just be a single column. When you > > > start going to that many I'd probably have a serial column as the > > > primary key, and a UNIQUE inde

Re: [GENERAL] Primary Key

( Fi Fie Foe Fum, I smell the blood of a religious war ) On Fri, Nov 16, 2007 at 01:03:23PM -0600, Ron Johnson wrote: > On 11/16/07 12:54, Sam Mason wrote: > > On Fri, Nov 16, 2007 at 03:50:20PM -0300, Jo??o Paulo Zavanela wrote: > >> How many fields is recomended to create a primary key? > >> I

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 16 Nov 2007 13:03:23 -0600 Ron Johnson <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 11/16/07 12:54, Sam Mason wrote: > > On Fri, Nov 16, 2007 at 03:50:20PM -0300, Jo??o Paulo Zavanela > > wrote: > >> How

Re: [GENERAL] Primary Key

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/16/07 12:54, Sam Mason wrote: > On Fri, Nov 16, 2007 at 03:50:20PM -0300, Jo??o Paulo Zavanela wrote: >> How many fields is recomended to create a primary key? >> I'm thinking to create one with 6 fields, is much? > > Normally a primary key woul

Re: [GENERAL] Primary Key

On Fri, Nov 16, 2007 at 03:50:20PM -0300, Jo??o Paulo Zavanela wrote: > How many fields is recomended to create a primary key? > I'm thinking to create one with 6 fields, is much? Normally a primary key would just be a single column. When you start going to that many I'd probably have a serial co

[GENERAL] Primary Key

Hello, How many fields is recomended to create a primary key? I'm thinking to create one with 6 fields, is much? Thanks! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Primary Key Performance with INTEGER vs. VARCHAR

On 7/21/07, Siah <[EMAIL PROTECTED]> wrote: Hi, Designing my application, I was wondering if having my primary keys (to be indexed) with VARCHAR brings performance down significantly? My own test didn't show much difference. Thinking about it though, I'd guess Integer Indexing should be much qui

  1   2   >