Process for populating tables in new database

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Process for populating tables in new database

Rich Shepard
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





Reply | Threaded
Open this post in threaded view
|

Re: Process for populating tables in new database

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Process for populating tables in new database

Adrian Klaver-4
In reply to this post by Rich Shepard
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Process for populating tables in new database [RESOLVED]

Rich Shepard
In reply to this post by Adrian Klaver-4
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

Reply | Threaded
Open this post in threaded view
|

Re: Process for populating tables in new database

Rich Shepard
In reply to this post by Adrian Klaver-4
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