Re: [GENERAL] PostgreSQL Developer Best Practices

Thank you to all that contributed to this thread. I now have about a dozen guidelines I would like to share. However, since a few individuals felt it was more of an opportunity to prove or disprove certain theories, rather than just contribute additional good practices, I will not be posting them d

Re: [GENERAL] PostgreSQL Developer Best Practices

On 27/08/15 06:59, Raymond O'Donnell wrote: On 26/08/2015 19:54, Gavin Flower wrote: On 27/08/15 00:03, Vincent de Phily wrote: On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: Actually I would suggest standardising on singular names, not JUST because that this the standard I prefer!

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 12:17, Melvin Davidson wrote: [...] So for the sake of argument, a natural key is something that in itself is unique and the possibility of a duplicate does not exist. Before ANYONE continues to insist that a serial id column is good, consider the case where the number of tuples will

Re: [GENERAL] PostgreSQL Developer Best Practices

- Original Message - From: Gavin Flower On 27/08/15 00:03, Vincent de Phily wrote: On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: Actually I would suggest standardising on singular names, not JUST because that this the standard I prefer! :-) But (also) because: 1. Si

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/2015 19:54, Gavin Flower wrote: > On 27/08/15 00:03, Vincent de Phily wrote: >> On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: >>> Actually I would suggest standardising on singular names, not JUST >>> because that this the standard I prefer! :-) >>> >>> But (also) because: >>>

Re: [GENERAL] PostgreSQL Developer Best Practices

On 27/08/15 00:03, Vincent de Phily wrote: On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: Actually I would suggest standardising on singular names, not JUST because that this the standard I prefer! :-) But (also) because: 1. Singular words tend to be shorter 2. plurals are more

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson wrote: > Before ANYONE continues to insist that a serial id column is good, consider > the case where the number of tuples will exceed a bigint. > Don't say it cannot happen, because it can. In practice, it cannot happen. A tuple with a bigint column weighs at least 32 by

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 05:28 PM, Adrian Klaver wrote: On 08/25/2015 05:17 PM, Melvin Davidson wrote: I think a lot of people here are missing the point. I was trying to give examples of natural keys, but a lot of people are taking great delight in pointing out exceptions to examples, rather than understa

Re: [GENERAL] PostgreSQL Developer Best Practices

Pierce < > pie...@hogranch.com>; pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] PostgreSQL Developer Best Practices > > > > …. > > Before ANYONE continues to insist that a serial id column is good, > consider the case where the number of tuples will exceed a bigint

Re: [GENERAL] PostgreSQL Developer Best Practices

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Melvin Davidson Sent: Tuesday, August 25, 2015 8:18 PM To: Adrian Klaver Cc: Jerry Sievers ; John R Pierce ; pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL Developer Best Practices

Re: [GENERAL] PostgreSQL Developer Best Practices

On Wed, Aug 26, 2015 at 09:04:08AM -0400, John Turner wrote: > >>>In most cases developers don’t care about index, unique, foreign key, > >>>or primary key names (from a coding standpoint) > >> > >>Until the day they’d like to write a reliable database change script. > > > >Not sure I understand.

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, 25 Aug 2015 18:57:28 -0400, Neil Tiffin wrote: On Aug 25, 2015, at 1:38 PM, Karsten Hilbert wrote: In most cases developers don’t care about index, unique, foreign key, or primary key names (from a coding standpoint) Until the day they’d like to write a reliable database cha

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26-08-2015 10:13, Allan Kamau wrote: On Wed, Aug 26, 2015 at 5:23 AM, rob stone wrote: On Tue, 2015-08-25 at 20:17 -0400, Melvin Davidson wrote: I think a lot of people here are missing the point. I was trying to give examples of natural keys, but a lot of people are taking great delig

Re: [GENERAL] PostgreSQL Developer Best Practices

On Wed, Aug 26, 2015 at 7:03 AM, Vincent de Phily < vincent.deph...@mobile-devices.fr> wrote: > On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: > > Actually I would suggest standardising on singular names, not JUST > > because that this the standard I prefer! :-) > > > > But (also) becau

Re: [GENERAL] PostgreSQL Developer Best Practices

On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: > Actually I would suggest standardising on singular names, not JUST > because that this the standard I prefer! :-) > > But (also) because: > > 1. Singular words tend to be shorter > > 2. plurals are more ambiguous wrt spelling > > 3.

Re: [GENERAL] PostgreSQL Developer Best Practices

On Wed, Aug 26, 2015 at 5:23 AM, rob stone wrote: > On Tue, 2015-08-25 at 20:17 -0400, Melvin Davidson wrote: > > I think a lot of people here are missing the point. I was trying to > > give examples of natural keys, but a lot of people are taking great > > delight > > in pointing out exceptions

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, 2015-08-25 at 20:17 -0400, Melvin Davidson wrote: > I think a lot of people here are missing the point. I was trying to > give examples of natural keys, but a lot of people are taking great > delight > in pointing out exceptions to examples, rather than understanding the > point. > So f

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tuesday, August 25, 2015, Melvin Davidson wrote: > > Before ANYONE continues to insist that a serial id column is good, > consider the case where the number of tuples will exceed a bigint. > Don't say it cannot happen, because it can. > However, if you have an alphanumeric field, let's say varc

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 05:21 PM, Gavin Flower wrote: On 26/08/15 11:34, Adrian Klaver wrote: [...] Agreed, but it happens. When Lowes took over a local hardware chain(Eagles) here in Washington state they moved very quickly on changing the account numbers. The company I worked for who supplied Eagles an

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 05:17 PM, Melvin Davidson wrote: I think a lot of people here are missing the point. I was trying to give examples of natural keys, but a lot of people are taking great delight in pointing out exceptions to examples, rather than understanding the point. So for the sake of argument,

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 11:34, Adrian Klaver wrote: [...] Agreed, but it happens. When Lowes took over a local hardware chain(Eagles) here in Washington state they moved very quickly on changing the account numbers. The company I worked for who supplied Eagles and then Lowes sat on a check for $22,000 that

Re: [GENERAL] PostgreSQL Developer Best Practices

I think a lot of people here are missing the point. I was trying to give examples of natural keys, but a lot of people are taking great delight in pointing out exceptions to examples, rather than understanding the point. So for the sake of argument, a natural key is something that in itself is uniq

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 04:23 PM, Jerry Sievers wrote: Adrian Klaver writes: On 08/25/2015 01:56 PM, John R Pierce wrote: On 8/25/2015 1:42 PM, Gavin Flower wrote: Account numbers are externally generated, and may potentially change. Management might suddenly decide that they want to start using the

Re: [GENERAL] PostgreSQL Developer Best Practices

Adrian Klaver writes: > On 08/25/2015 01:56 PM, John R Pierce wrote: > >> On 8/25/2015 1:42 PM, Gavin Flower wrote: >>> Account numbers are externally generated, and may potentially change. >>> Management might suddenly decide that they want to start using the >>> year the account started as the

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, Aug 25, 2015 at 6:27 PM, Gavin Flower wrote: > On 26/08/15 04:33, Marc Munro wrote: > >> On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: >> >> I've been searching for a "PostgreSQL Developer Best Practices" with not >>> much luck, >>> so I've started my own. At the risk of stirr

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 04:27 PM, Gavin Flower wrote: On 26/08/15 04:33, Marc Munro wrote: On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: I've been searching for a "PostgreSQL Developer Best Practices" with not much luck, so I've started my own. At the risk of stirring up a storm of controv

Re: [GENERAL] PostgreSQL Developer Best Practices

On 8/25/2015 1:38 PM, Joshua D. Drake wrote: But a VIN is in fact, UNIQUE so it is useful as a PK. JD But a VIN is *not* guaranteed to exist, nor is it guaranteed never to change, and I regard those as pretty important characteristics in a PK. VINs were not required in the U.S. until 1954, a

Re: [GENERAL] PostgreSQL Developer Best Practices

> On Aug 25, 2015, at 1:38 PM, Karsten Hilbert wrote: > >> In most cases developers don’t care about index, unique, foreign key, or >> primary key names (from a coding standpoint) > > Until the day they’d like to write a reliable database change script. Not sure I understand. Once the object

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 04:33, Marc Munro wrote: On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: I've been searching for a "PostgreSQL Developer Best Practices" with not much luck, so I've started my own. At the risk of stirring up a storm of controversy, I would appreciate additional suggestions

Re: [GENERAL] PostgreSQL Developer Best Practices

NB the attribution colours seems to be mixed up a bit here, but this all dialogue between me & Adrian. On 26/08/15 09:48, Adrian Klaver wrote: On 08/25/2015 02:23 PM, Gavin Flower wrote: On 26/08/15 08:56, Adrian Klaver wrote: [...] have all gone to the same seminar on how to be Walmart and

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 02:44 PM, Gavin Flower wrote: On 26/08/15 02:17, Adrian Klaver wrote: [...] 2) One of the older unique natural keys (genus, species) is not so unique. I am a fisheries biologist by training and in my time the 'unique' identifier for various fishes has changed. Now that ichthyologi

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 02:23 PM, Gavin Flower wrote: On 26/08/15 08:56, Adrian Klaver wrote: On 08/25/2015 08:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. Pretty sur

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 02:17, Adrian Klaver wrote: [...] 2) One of the older unique natural keys (genus, species) is not so unique. I am a fisheries biologist by training and in my time the 'unique' identifier for various fishes has changed. Now that ichthyologists have discovered DNA testing, it can be

[GENERAL] PostgreSQL Developer Best Practices

On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: > I've been searching for a "PostgreSQL Developer Best Practices" with not > much luck, > so I've started my own. At the risk of stirring up a storm of controversy, > I would appreciate additional suggestions and feedback. > You might add:

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 08:56, Adrian Klaver wrote: On 08/25/2015 08:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. Pretty sure this is a technical list:) Don't let inco

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 01:56 PM, John R Pierce wrote: On 8/25/2015 1:42 PM, Gavin Flower wrote: Account numbers are externally generated, and may potentially change. Management might suddenly decide that they want to start using the year the account started as the first 4 digits, or that the branch code

Re: [GENERAL] PostgreSQL Developer Best Practices

On 8/25/2015 1:42 PM, Gavin Flower wrote: Account numbers are externally generated, and may potentially change. Management might suddenly decide that they want to start using the year the account started as the first 4 digits, or that the branch code should be reflected in it, or something els

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 08:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. Pretty sure this is a technical list:) No one ever said a number is not natural. just that the

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 03:40, Melvin Davidson wrote: [...] IOW: If we have an account table, then the account_id or account_no would be the primary key. There is no need to have a separate serial id as the primary key. [...] Account numbers are externally generated, and may potentially change. Man

Re: [GENERAL] PostgreSQL Developer Best Practices

> In most cases developers don’t care about index, unique, foreign key, or > primary key names (from a coding standpoint) Until the day they'd like to write a reliable database change script. (PG's internal conventions for object names _have_ changed over the years) Karsten -- Sent via pgsql

Re: [GENERAL] PostgreSQL Developer Best Practices

Personally I always set the natural key with a not null and unique constraint, but create an artificial key for it as well. As an example, if we had a product table, the product_sku is defined as not null with a unique constraint on it, while product_id is the primary key which all other tables re

Re: [GENERAL] PostgreSQL Developer Best Practices

Consistency in naming convention. Good suggestion! On Tue, Aug 25, 2015 at 12:33 PM, Marc Munro wrote: > On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: > > > I've been searching for a "PostgreSQL Developer Best Practices" with not > > much luck, > > so I've started my own. At the ris

Re: [GENERAL] PostgreSQL Developer Best Practices

> No one ever said a number is not natural. just that there is no need to > duplicate uniqueness > with a separate number. The whole point is that people are telling you that surrogate keys do not _duplicate_ uniqueness but rather _generate_ it, artificially, and therefore reliably. Today's ext

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 09:09 AM, Rob Sargent wrote: On 08/25/2015 09:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. No one ever said a number is not natural. just that th

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, Aug 25, 2015 at 12:09 PM, Rob Sargent wrote: > On 08/25/2015 09:40 AM, Melvin Davidson wrote: > >> Adrian, >> >> Stop being so technical. When we/I speak of natural keys, we are talking >> about the column >> that would NATURALly lend itself as the primary key. >> No one ever said a numbe

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, Aug 25, 2015 at 11:40 AM, Melvin Davidson wrote: > Consider: > SELECT c.registration_no, >c.car_make, >p.part_no >FROM car c >JOIN parts p ON ( p.registration_no = c.registration_no) > WHERE registration_no = ; > > versus: > SELECT c.registration_no, >c.

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 09:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. No one ever said a number is not natural. just that there is no need to duplicate uniqueness w

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson wrote: 9. >1) What happens if someone mis-types the account-id? > To correct that, you also need to correct the FK field in the other dozen tables. >2) What happens when your company starts a new project (or buys a I would not consider the general use of natural primary

Re: [GENERAL] PostgreSQL Developer Best Practices

Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. No one ever said a number is not natural. just that there is no need to duplicate uniqueness with a separate number. IOW: If we have an account ta

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 08:44 PM, Rob Sargent wrote: On Aug 24, 2015, at 6:53 PM, Melvin Davidson wrote: You are right, he was probably talking about FK's. I was just so frustrated about people insisting that using "ID" as the primary key in every table is a "good" idea, I didn't bother to reply previ

Re: [GENERAL] PostgreSQL Developer Best Practices

> On Aug 22, 2015, at 10:15 AM, Melvin Davidson wrote: > 6. Although it is legal to use the form "column TYPE PRIMARY KEY," It is best > to specify as a CONSTRAINT, >that way YOU get to choose the name, otherwise postgres assigns a default > name which may not be to your liking. >EG: ,

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 19:04, Karsten Hilbert wrote: On Tue, Aug 25, 2015 at 02:02:17PM +1200, Gavin Flower wrote: On 25/08/15 01:15, Ray Cote wrote: On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert mailto:karsten.hilb...@gmx.net>> wrote: [...] 9. Do NOT arbitrarily assign an "id" column to a tab

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, Aug 25, 2015 at 02:02:17PM +1200, Gavin Flower wrote: > On 25/08/15 01:15, Ray Cote wrote: > >On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert >> wrote: > > > [...] > > > >9. Do NOT arbitrarily assign an "id" column to a table as a > >primary key w

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 14:45, David G. Johnston wrote: On Mon, Aug 24, 2015 at 10:02 PM, Gavin Flower >wrote: Also the best practice is to make the primary key name 'id' as you do know the table it is in, so prepending the table name is redundant - so you

Re: [GENERAL] PostgreSQL Developer Best Practices

> On Aug 24, 2015, at 6:53 PM, Melvin Davidson wrote: > > You are right, he was probably talking about FK's. I was just so frustrated > about people insisting that using "ID" as the primary key in every table is a > "good" idea, > I didn't bother to reply previously. I stand firm on my belief

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 10:02 PM, Gavin Flower < gavinflo...@archidevsys.co.nz> wrote: > Also the best practice is to make the primary key name 'id' as you do know > the table it is in, so prepending the table name is redundant - so you can > clearly identify foreign keys because the suffix '_id '

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 08:53:43PM -0400, Melvin Davidson wrote: > You are right, he was probably talking about FK's. I was just so frustrated > about people insisting that using "ID" as the primary key in every table is > a "good" idea, > I didn't bother to reply previously. I stand firm on my bel

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 04:26, Joshua D. Drake wrote: On 08/24/2015 08:56 AM, Melvin Davidson wrote: >The "serial" key is the default primary key amongst every single web development environment in existence. Methinks thou doest take too much for granted. Yes, serial has it's purpose, but I sincerely dou

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 02:58, John Turner wrote: [...] Conversely, if synthetic keys are chosen as Primary, they must be accompanied by a legitimate Unique natural key. Agreed, but only where appropriate. Cheers, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 01:15, Ray Cote wrote: On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert mailto:karsten.hilb...@gmx.net>> wrote: [...] 9. Do NOT arbitrarily assign an "id" column to a table as a primary key when other columns are perfectly suited as a unique primary key. ...

Re: [GENERAL] PostgreSQL Developer Best Practices

You are right, he was probably talking about FK's. I was just so frustrated about people insisting that using "ID" as the primary key in every table is a "good" idea, I didn't bother to reply previously. I stand firm on my belief that the primary key should be something meaningful and NOT "id" just

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 08:22:17PM -0400, Melvin Davidson wrote: > On Mon, Aug 24, 2015 at 8:00 PM, Berend Tober wrote: > >> 1) What happens if someone mis-types the account-id? > >> To correct that, you also need to correct the FK field in the > >> other dozen tables. > > > > ..

Re: [GENERAL] PostgreSQL Developer Best Practices

>ON UPDATE CASCADE ? I believe he's talking about triggers. On Mon, Aug 24, 2015 at 8:00 PM, Berend Tober wrote: > Melvin Davidson wrote: > >> 9. >> >1) What happens if someone mis-types the account-id? >> > To correct that, you also need to correct the FK field in the >> other dozen tabl

Re: [GENERAL] PostgreSQL Developer Best Practices

Thank you John R. Pierce. Finally someone who understands the purpose of this thread. Otherwise, next thing you know, we'll have Web apps/developers designing bra's for milk cows so they'll look better in the field. :) On Mon, Aug 24, 2015 at 1:05 PM, John R Pierce wrote: > On 8/24/2015 9:34 AM,

Re: [GENERAL] PostgreSQL Developer Best Practices

On 8/24/2015 9:34 AM, Melvin Davidson wrote: And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. It is far better to let DBA's and "database develeopers" design a good database, then to l

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, 24 Aug 2015 12:40:37 -0400, Joshua D. Drake wrote: On 08/24/2015 09:34 AM, Melvin Davidson wrote: And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. And I don't argue that b

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 09:34 AM, Melvin Davidson wrote: And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. Again this is not restricted to Web apps. Anything that touches a database via an ORM i

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 09:34 AM, Melvin Davidson wrote: And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. And I don't argue that but we also live in a world based on reality. DBAs are rare, web

Re: [GENERAL] PostgreSQL Developer Best Practices

And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. It is far better to let DBA's and "database develeopers" design a good database, then to let those apps mold a db into a non-optimum design

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 08:56 AM, Melvin Davidson wrote: >The "serial" key is the default primary key amongst every single web development environment in existence. Methinks thou doest take too much for granted. Yes, serial has it's purpose, but I sincerely doubt it is "the default primary key amongst e

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 08:56 AM, Melvin Davidson wrote: >The "serial" key is the default primary key amongst every single web development environment in existence. Methinks thou doest take too much for granted. Yes, serial has it's purpose, but I sincerely doubt it is "the default primary key amongst e

Re: [GENERAL] PostgreSQL Developer Best Practices

>The "serial" key is the default primary key amongst every single web development environment in existence. Methinks thou doest take too much for granted. Yes, serial has it's purpose, but I sincerely doubt it is "the default primary key amongst every single web development environment in existen

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 07:58 AM, John Turner wrote: On Mon, 24 Aug 2015 09:15:27 -0400, Ray Cote Point 9 is well-intentioned, but perhaps needs to be clarified/rephrased: Developers should not be creating production-grade tables devoid of well-defined business keys, period. That would be regardless o

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, 24 Aug 2015 09:15:27 -0400, Ray Cote wrote: 9. Do NOT arbitrarily assign an "id" column to a table as a primary key when other columns are perfectly suited as a unique primary key. ... Good example: CREATE TABLE accounts ( accout_id bigint NOT NULL , I would

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 10:32 AM, Melvin Davidson wrote: > >What then if it is discovered that the keyed in value was mis-typed? > > That is why SQL has UPDATE and DELETE statements. If a primary key is > incorrect, > it can be fixed, be it one method of another. > ​Yes, a DBA can use ON DELETE

Re: [GENERAL] PostgreSQL Developer Best Practices

>What then if it is discovered that the keyed in value was mis-typed? That is why SQL has UPDATE and DELETE statements. If a primary key is incorrect, it can be fixed, be it one method of another. On Mon, Aug 24, 2015 at 10:04 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon,

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 9:15 AM, Ray Cote wrote: > On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert > wrote: > >> > 1. Prefix ALL literals with an Escape >> >EG: SELECT E'This is a \'quoted literal \''; >> > SELECT E'This is an unquoted literal'; >> > >> >Doing so will prevent

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 9:27 AM, Melvin Davidson wrote: > 9. > >1) What happens if someone mis-types the account-id? > > To correct that, you also need to correct the FK field in the other > dozen tables. > >2) What happens when your company starts a new project (or buys a > competitor) >and

Re: [GENERAL] PostgreSQL Developer Best Practices

9. >1) What happens if someone mis-types the account-id? > To correct that, you also need to correct the FK field in the other dozen tables. >2) What happens when your company starts a new project (or buys a competitor) >and all the new account numbers are alpha-numeric? I would reply that in

Re: [GENERAL] PostgreSQL Developer Best Practices

On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert wrote: > > 1. Prefix ALL literals with an Escape > >EG: SELECT E'This is a \'quoted literal \''; > > SELECT E'This is an unquoted literal'; > > > >Doing so will prevent the annoying "WARNING: nonstandard use of > escape in a stri

Re: [GENERAL] PostgreSQL Developer Best Practices

David, Thank you. This is exactly the type of feedback I was looking for. To answer your question, for now it is a guide for one particular client, however, if I get enough feedback and contributions, I will revise it and submit to the PostgreSQL community. On Mon, Aug 24, 2015 at 2:34 AM, Thomas

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson schrieb am 22.08.2015 um 17:15: > I've attached a file with a few starters that although are numbered, > are in no special order. > 2. End ALL queries with a semi-colon (;) >EG: SELECT some_column FROM a_table; > >Although autocommit is on by default, it is always a go

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson schrieb am 22.08.2015 um 21:40: > Thank you for pointing out "run with standard_conforming_strings = ON".. > However, that is NOT the problem. > What is occurring is that the developers are sending strings like 'Mr. > M\'vey', > which, if we set standard_conforming_strings = ON, wo

Re: [GENERAL] PostgreSQL Developer Best Practices

On Sat, Aug 22, 2015 at 7:33 PM, Melvin Davidson wrote: > John, > > I believe you and I think alike. The truth is, I was brought on as a > consultant to help this client, so I do not have the authority to fire the > developers. Rather, I am trying to help them fix the absolute worst > designed DB

Re: [GENERAL] PostgreSQL Developer Best Practices

John, I believe you and I think alike. The truth is, I was brought on as a consultant to help this client, so I do not have the authority to fire the developers. Rather, I am trying to help them fix the absolute worst designed DB and coding I have seen in 15 years of working with PostgreSQL. So I'

Re: [GENERAL] PostgreSQL Developer Best Practices

On 8/22/2015 12:40 PM, Melvin Davidson wrote: What is occurring is that the developers are sending strings like 'Mr. M\'vey', which, if we set standard_conforming_strings = ON, would, and does, result in errors and the statement failing, which is a lot less desirable that a simple warning. if

Re: [GENERAL] PostgreSQL Developer Best Practices

On Saturday, August 22, 2015, Melvin Davidson wrote: > >The correct way to escape a quote is to double quote it: 'Mr. M''vey' > > That is a matter of opinion. However, the real problem is the enclosed > backslashes, which is > beyond our control at this point. Therefore, the best solution is to

Re: [GENERAL] PostgreSQL Developer Best Practices

>The correct way to escape a quote is to double quote it: 'Mr. M''vey' That is a matter of opinion. However, the real problem is the enclosed backslashes, which is beyond our control at this point. Therefore, the best solution is to use ESCAPE E. On Sat, Aug 22, 2015 at 3:49 PM, Andy Colson wro

Re: [GENERAL] PostgreSQL Developer Best Practices

On Sat, Aug 22, 2015 at 1:16 PM, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: Melvin Davidson mailto:melvin6...@gmail.com>> writes: >> Best Practice would rather be something along the lines: Avoid coding in a way that triggers "WARNING: nonstandard use of escape in a str

Re: [GENERAL] PostgreSQL Developer Best Practices

Tom, Thank you for pointing out "run with standard_conforming_strings = ON".. However, that is NOT the problem. What is occurring is that the developers are sending strings like 'Mr. M\'vey', which, if we set standard_conforming_strings = ON, would, and does, result in errors and the statement fai

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson writes: >> Best Practice would rather be something along the lines: Avoid coding in a way that triggers "WARNING: nonstandard use of escape in a string literal". If you cannot comply with this rule document your reasons. > Thanks for the suggestion. For the past few

Re: [GENERAL] PostgreSQL Developer Best Practices

>This is certainly not Best Practice as the warning is >annoying for a reason. >Best Practice would rather be something along the lines: > Avoid coding in a way that triggers "WARNING: > nonstandard use of escape in a string literal". If you > cannot comply with this rule do

Re: [GENERAL] PostgreSQL Developer Best Practices

On Sat, Aug 22, 2015 at 11:15:07AM -0400, Melvin Davidson wrote: > PostgreSQL Developer Best Practices > > 1. Prefix ALL literals with an Escape >EG: SELECT E'This is a \'quoted literal \''; > SELECT E'This is an unquoted literal'; > >Doing so will prevent the annoying "WARNING:

[GENERAL] PostgreSQL Developer Best Practices

I've been searching for a "PostgreSQL Developer Best Practices" with not much luck, so I've started my own. At the risk of stirring up a storm of controversy, I would appreciate additional suggestions and feedback. As a way of clarifying, generally, a DBA is someone that is responsible for maintai