Thread: Process for populating tables in new database
I have a new database with five tables, and text files with data to populate the tables using the insert command. 1. Each table has a sequential primary key. Should I manually add keys to each insert row or will postgres generate it automatically? Example, for the companies table: org_id | integer | | not null | nextval('companies_org_id_seq'::regclass) org_name | character varying(64) | | not null | 2. Should I use sepatate files as input to each table or can I combine them in a single file? For example, adding a company name and contact name for someone in that company using input commands for both tables in a single file? I will need to provide the primary key to the company table as the foreign key in contact table and have not before pupulated multiple related tables in a new database from .sql files. TIA, Rich
On 12/1/18 6:09 AM, Rich Shepard wrote: > I have a new database with five tables, and text files with data to > populate > the tables using the insert command. > > 1. Each table has a sequential primary key. Should I manually add keys to > each insert row or will postgres generate it automatically? Example, for > the > companies table: If you don't supply the key it will be generated as the default for the PK column is a sequence. > > org_id | integer | | not null | > nextval('companies_org_id_seq'::regclass) > org_name | character varying(64) | | not null | > > 2. Should I use sepatate files as input to each table or can I combine them > in a single file? For example, adding a company name and contact name for > someone in that company using input commands for both tables in a single > file? I will need to provide the primary key to the company table as the > foreign key in contact table and have not before pupulated multiple related > tables in a new database from .sql files. So are the tables you are INSERTing into currently unpopulated? In any case you will need to do this in sequence, where you populate the company table and then the contact table. The question is whether you want to pre-assign the company id's in the company data and the company_id_fk in the contacts data or not. If not then you will need to grab the company id's after populating the company table and match those to the contacts data before inserting it. > > TIA, > > Rich > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 12/1/18 6:09 AM, Rich Shepard wrote: > I have a new database with five tables, and text files with data to > populate > the tables using the insert command. > > 1. Each table has a sequential primary key. Should I manually add keys to > each insert row or will postgres generate it automatically? Example, for > the > companies table: > > org_id | integer | | not null | > nextval('companies_org_id_seq'::regclass) > org_name | character varying(64) | | not null | > > 2. Should I use sepatate files as input to each table or can I combine them > in a single file? For example, adding a company name and contact name for > someone in that company using input commands for both tables in a single > file? I will need to provide the primary key to the company table as the > foreign key in contact table and have not before pupulated multiple related > tables in a new database from .sql files. Forgot to add that if you pre-assign the keys to the serial field you will need to advance the sequence to a value past the last key value to avoid a duplicate key error when you let the sequence assign numbers. > > TIA, > > Rich > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 1 Dec 2018, Adrian Klaver wrote: > If you don't supply the key it will be generated as the default for the PK > column is a sequence. Thanks, Adrian. I thought this to be the case and did not find confirmation in the manual (perhaps I just missed seeing it.) > So are the tables you are INSERTing into currently unpopulated? Yes. > In any case you will need to do this in sequence, where you populate the > company table and then the contact table. The question is whether you want > to pre-assign the company id's in the company data and the company_id_fk > in the contacts data or not. If not then you will need to grab the company > id's after populating the company table and match those to the contacts > data before inserting it. This clears up everything. I'll let pg assign company id's then use them as you write to relate the other tables to the proper company/contact. Much appreciated, Rich
On Sat, 1 Dec 2018, Adrian Klaver wrote: > Forgot to add that if you pre-assign the keys to the serial field you will > need to advance the sequence to a value past the last key value to avoid a > duplicate key error when you let the sequence assign numbers. Adrian, Got it, thanks. Carpe weekend, Rich