postgresql insert into table from select code example

Example 1: postgresql insert select

insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;

Example 2: postgre insert select

insert into TABLENAMEA (A,B,C,D) 
select A,B,C,D from TABLENAMEB

Example 3: insert record into table postgresql

try:
        connection = psycopg2.connect(**postgres_credentials())
        cursor = connection.cursor()

        order_types = [(1,'SELL'),(2,'BUY')]

        placeholders = ','.join(['%s']*len(order_types)) # => '%s,%s'
        sql = f"""
            INSERT INTO collection.instruction(id, side)
            VALUES {placeholders}
        """
                       
        # Mogrify transforms command into human readable form so we can print and debug command sent to DB server.
        # It is not necessary. We could just use executemany, but it's slower and also harder to debug the command.
        insert_statement = cursor.mogrify(sql, order_types)
        # prints the exact command that would be sent to server.
        print(insert_statement.decode('utf-8'))

        cursor.execute(insert_statement)
        # cursor.executemany(sql, order_types) # SLOW bc executes and commits each record one at a time!
        # prints the exact command that would be sent to server.
        # print(cursor.mogrify(sql, order_types).decode('utf-8'))

        connection.commit()
        print('DB entries committed.')

    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if connection:
            cursor.close()
            connection.close()

Tags:

Sql Example