0

I am using pgAdmin 4 for PostgreSQL. I have two databases A and B each having a table called records. In database A, table records has 3 columns person_id, shop_freq and time, and has about 1000 rows. While in database B, table records has 4 columns person_id, shop_freq, start_time and end_time, and is empty.

I want to transfer the data of columns person_id and shop_freq from database A to their corresponding columns in database B, and then transfer column time of database A to column start_time, and pass random data (for instance about 1 to 2 months after the start time) to the column end_time.

How can I do such a thing in an easy and clean way in pgAdmin itself?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Mike
  • 369
  • 5
  • 21

1 Answers1

1

You can use the COPY statement to export the data to a file on the database server and use COPY with column names to load them into the other database.

To fill the missing column, run an UPDATE statement after the import.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks. I tried this. COPY TO works fine. But when I use COPY FROM I get the error: "Null value in column “name” violates not-null constraint" – Mike Feb 25 '19 at 13:17
  • 1
    Either create a "staging table" without that constraint, import the data there, update the column and then transfer it with `INSERT INTO ... SELECT ...`, or create a `BEFORE INSERT` trigger on the target table that sets the column if it is NULL. – Laurenz Albe Feb 25 '19 at 13:20