0

I am migrating the data from one DB to another (both postgresql). I want to concatenate the value of firstname and lastname columns from the source DB to name column of the destination DB. When I try to run the query, I am getting an error: psycopg2.ProgrammingError: more than one row returned by a subquery used as an expression Checking other threads here in SO, I found this concatenate. I added LIMIT 1 to the code but it gives me the first and last name of the existing name in the table.

My code:

cur_t.execute("""
                SELECT firstname, lastname
                FROM authors;
                """)

for row in cur_t:
    cur_p.execute("""
                     INSERT INTO lib_author (
                                                 created, modified, last_name,
                                                 first_name, country,
                                                 school_id, name)
                    VALUES (current_timestamp, current_timestamp, %s, %s, %s,
                            (SELECT id FROM ed_school WHERE name='My Test School'),
                            (SELECT CONCAT(first_name, ',', last_name) AS name FROM lib_author LIMIT 1)
                            )
                    """, (row['lastname'], row['firstname'], ''))

How can I call the firstname and lastname column from the source DB?

tango ward
  • 307
  • 1
  • 4
  • 18
  • something like `SELECT firstname || ',' || lastname FROM authors;`? – Jim Jones May 08 '18 at 10:04
  • If you concatenate `first_name` and `last_name` like that, my name would appear as 'Mike,Sherrill'. I don't think you want that. I think you'd be better off building a *correct* `SELECT` statement, then just running `INSERT INTO lib_author SELECT . . . `. Good rule of thumb: if you're processing row by row in a SQL database, you're *probably* doing the Wrong Thing. – Mike Sherrill 'Cat Recall' May 08 '18 at 10:18
  • @MikeSherrill'CatRecall' what should be the best approach for migrating data. The data that I am migrating have inconsistencies that's while the destination DB have strict not null fields. Processing row by row gives me a bit of control on the data. – tango ward May 08 '18 at 23:56
  • @JimJones Yes Jim, something like that. It's just that I am using 2 connections, cur_t and cur_p are different connections. – tango ward May 09 '18 at 00:04
  • So I modified the code and removed `(SELECT CONCAT(first_name, ',', last_name) AS name FROM lib_author LIMIT 1)` and replaced it with `%s`. In the parenthesis with row values, I concatenated `(row['firstname'], row['lastname'])`. It works in saving the first and last name, problem is it includes the parenthesis. Any suggestions on how to remove the parenthesis? – tango ward May 09 '18 at 02:29
  • Don't concatenate row constructors here. (Here `row()` is a row constructor.) Concatenate `firstname` and `lastname`. Start with `firstname || ' ' || lastname`. Nulls complicate things. – Mike Sherrill 'Cat Recall' Sep 02 '18 at 16:47

0 Answers0