Re: Process for populating tables in new database

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