Thread

1. No primary key table

   Ertan Küçükoglu <ertan.kucukoglu@gmail.com> — 2019-09-13T15:34:06Z

   Hello,
   
   We are using PostgreSQL 10.0 on a Windows  VM.
   
   There is one database in that server.
   There are several tables that will be used for data warehouse purposes.
   
   There are daily inserts and relatively heavy bulk (whole month data at once) reads at end of months. Reads will be from several hundred clients and will be over internet and no local network connection.
   
   Daily data saving application check for duplicate entries of a single record using an sql before each insert. That is only select statement during in month days.
   
   End of the month queries will be selecting bulk data from previous month records and will filter on GUID field and a varchar(25) field.
   
   There is one primary key of a bigint identity column on each table. Primary key won't be used for any purpose for any queries.
   
   We wonder if it maybe a suggested to remove primary key index and column. There is no unique key index on these tables otherwise.
   
   Thanks & regards,
   Ertan Küçükoğlu
   
   Sent from my iPhone
   
   
   

2. Re: No primary key table

   Ron <ronljohnsonjr@gmail.com> — 2019-09-13T15:45:39Z

   On 9/13/19 10:34 AM, Ertan Küçükoglu wrote:
   > Hello,
   >
   > We are using PostgreSQL 10.0 on a Windows  VM.
   >
   > There is one database in that server.
   > There are several tables that will be used for data warehouse purposes.
   >
   > There are daily inserts and relatively heavy bulk (whole month data at once) reads at end of months. Reads will be from several hundred clients and will be over internet and no local network connection.
   >
   > Daily data saving application check for duplicate entries of a single record using an sql before each insert. That is only select statement during in month days.
   >
   > End of the month queries will be selecting bulk data from previous month records and will filter on GUID field and a varchar(25) field.
   >
   > There is one primary key of a bigint identity column on each table. Primary key won't be used for any purpose for any queries.
   >
   > We wonder if it maybe a suggested to remove primary key index and column. There is no unique key index on these tables otherwise.
   
   Don't you need an index to "check for duplicate entries of a single record 
   using an sql before each insert"?
   
   -- 
   Angular momentum makes the world go 'round.
   
   
   
   

3. Re: No primary key table

   SERHAD ERDEM <serhade@hotmail.com> — 2019-09-13T17:38:39Z

   Hi,
   if you have not seen any benefit , of course you can remove identity column from a DWH table , there is a sequence and a trigger  for identity serial column.
   ID columns are being generaly used for base tables which are under end-user operations.
   
   
   ________________________________
   From: Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
   Sent: Friday, September 13, 2019 3:34 PM
   To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
   Subject: No primary key table
   
   Hello,
   
   We are using PostgreSQL 10.0 on a Windows  VM.
   
   There is one database in that server.
   There are several tables that will be used for data warehouse purposes.
   
   There are daily inserts and relatively heavy bulk (whole month data at once) reads at end of months. Reads will be from several hundred clients and will be over internet and no local network connection.
   
   Daily data saving application check for duplicate entries of a single record using an sql before each insert. That is only select statement during in month days.
   
   End of the month queries will be selecting bulk data from previous month records and will filter on GUID field and a varchar(25) field.
   
   There is one primary key of a bigint identity column on each table. Primary key won't be used for any purpose for any queries.
   
   We wonder if it maybe a suggested to remove primary key index and column. There is no unique key index on these tables otherwise.
   
   Thanks & regards,
   Ertan Küçükoğlu
   
   Sent from my iPhone