Обсуждение: 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