insert into postgresql code example

Example 1: return insert results in POSTGRESQL

# to select specific columns
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id, firstname;
# to return every column
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING *;

Example 2: insert postgres

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

Example 3: posgres insert

INSERT INTO films 
  (code, title, did, date_prod, kind)
    VALUES 
  ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

Example 4: insert into postgres python

#!/usr/bin/python

import psycopg2
from config import config


def insert_vendor(vendor_name):
    """ insert a new vendor into the vendors table """
    sql = """INSERT INTO vendors(vendor_name)
             VALUES(%s) RETURNING vendor_id;"""
    conn = None
    vendor_id = None
    try:
        # read database configuration
        params = config()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.execute(sql, (vendor_name,))
        # get the generated id back
        vendor_id = cur.fetchone()[0]
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

    return vendor_id

Example 5: 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()

Example 6: postgresql, Rows, INSERT INTO

INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …)
RETURNING *;Code language: SQL (Structured Query Language) (sql)