Search Postgresql Archives

Re: Concatenate 2 Column Values For One Column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, May 8, 2018 at 7:17 PM, tango ward <tangoward15@xxxxxxxxx> wrote:
I am trying to concatenate the value of column firstname and lastname from source DB to name column of destination DB.

                            (SELECT CONCAT(first_name, ',', last_name) AS name FROM lib_author LIMIT 1)
                            )
                    """, (row['lastname'], row['firstname'], ''))

The code will take the first and lastname of the FIRST data existing on the destination table.

​That seems so not useful (and "first" is random here since you lack an ORDER BY)...​and is "first,last" with no space following the comma a cultural thing I'm unfamiliar with?
 
I modified the code, instead of running SELECT and CONCAT, I passed string formatter and call the row['firstname'], row['lastname']

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'),
                             %s
                            )
                    """, (row['lastname'], row['firstname'], '', (row['firstname'], row['lastname']) )

The second code works but it includes the parenthesis in the DB.

​I'm somewhat surprised that's the only oddity you observed...
How can I remove the ( ) in the DB? I can't call the row['firstname'] and row['lastname'] as values without using ( ).

1. Store the desired value, complete, in a variable and pass that variable to cur_p.
2. Pass the row[] constructs individually and write "%s || ',' || %s" (or use the CONCAT function you discovered earlier) instead of a single %s for the "name" column

I'd probably write it as:

INSERT INTO lib_author (...)
SELECT 
current_timestamp AS ct, 
current_timestamp AS mt, 
vals.last_name, 
vals.first_name, 
vals.country, 
(SELECT id ....) AS school_id, 
vals.last_name || ', ' || vals.first_name
FROM (VALUES (%s, %s, %s)) vals (first_name, last_name, country)

And I'd probably rely on defaults for the timestamp columns and only do: INSERT INTO lib_author (last_name, first_name, country, school_id, name) SELECT ...

David J.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux