Import data into table in postgres for `mailing_list`

zipperer / migrate_database_with_michael

0 stars 0 forks source link

Import data into table in postgres for `mailing_list` #5

Closed zipperer closed 5 months ago

zipperer commented 7 months ago

For the table in Lotus Approach that holds the mailing list, we will:

[x] export the data as a csv
[x] determine the type for each column
[x] in postgres create a table to hold the data
[x] copy the data from the csv into the table in postgres

Further, we will

[x] create a new column that will serve as the id / PRIMARY KEY. See #6 .
[x] use table mailing_list and its new column that will serve as the PRIMARY KEY to replace values for listenerid in other tables. See #6

zipperer commented 7 months ago

As I note in this comment, we will choose for the columns types more specific than varchar.

During this meeting, we noticed that some of the columns in the mailing_list table are not varchar. So, we will: determine the type for each column edit the CREATE TABLE statement from
CREATE TABLE mailing_list (
column1 VARCHAR,
...
columnN VARCHAR
);
to a statement with improved types.

To go from all varchar to improved types may take a few steps and involve encountering errors when values do not match the type we propose for a column.

zipperer commented 7 months ago

See also #14 .

zipperer commented 7 months ago

During #3 , we edited the structure of table mailing_list.

psql> DELETE FROM mailing_list;
psql> DROP TABLE mailing_list;
psql> CREATE TABLE mailing_list (
           add_date DATE,
           listenerid VARCHAR,
           ...
           modification_date DATE,
           ...
);
psql> \copy mailing_list ...

Before deleting from the table and dropping the table, we tried to edit-in-place the datatype for column add_date. It did not work.

zipperer commented 5 months ago

We have imported sample data. We will import full data after we pick a machine to host the production postgres cluster.