I am testing Postgres insertion performance. I have a table with one column with number as its data type. There is an index on it as well. I filled the database up using this query:

    insert into aNumber (id) values (564),(43536),(34560) ...

I inserted 4 million rows very quickly 10,000 at a time with the query above. After the database reached 6 million rows performance drastically declined to 1 Million rows every 15 min. Is there any trick to increase insertion performance? I need optimal insertion performance on this project.

Using Windows 7 Pro on a machine with 5 GB RAM.

How to speed up insertion performance in PostgreSQL

I started by googling and found the article *[How to write INSERT if NOT EXISTS queries in standard SQL][1]* which talks about mutex tables.

I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (i.e., one query to check and one to insert is the result set is empty)?

Does a `unique` constraint on a field guarantee the `insert` will fail if it&#39;s already there?

It seems that with *merely* a constraint, when I issue the insert via PHP, the script croaks.

  [1]: http://www.xaprb.com/blog/2005/09/25/insert-if-not-exists-queries-in-mysql/


How can I do &#39;insert if not exists&#39; in MySQL?

I have an SQLite database. I am trying to insert values (`users_id`, `lessoninfo_id`) in table `bookmarks`, only if both do not exist before in a row.

    INSERT INTO bookmarks(users_id,lessoninfo_id) 
    VALUES(
        (SELECT _id FROM Users WHERE User=&#39;&quot;+$(&#39;#user_lesson&#39;).html()+&quot;&#39;),
            (SELECT _id FROM lessoninfo 
            WHERE Lesson=&quot;+lesson_no+&quot; AND cast(starttime AS int)=&quot;+Math.floor(result_set.rows.item(markerCount-1).starttime)+&quot;) 
            WHERE NOT EXISTS (
                SELECT users_id,lessoninfo_id from bookmarks 
                WHERE users_id=(SELECT _id FROM Users 
                WHERE User=&#39;&quot;+$(&#39;#user_lesson&#39;).html()+&quot;&#39;) AND lessoninfo_id=(
                    SELECT _id FROM lessoninfo
                    WHERE Lesson=&quot;+lesson_no+&quot;)))

 

This gives an error saying:

&gt;db error near where syntax.

&quot;Insert if not exists&quot; statement in SQLite

I have the following two tables:

    Table1
    ----------
    ID   Name
    1    A
    2    B
    3    C

    Table2
    ----------
    ID   Name
    1    Z

I need to insert data from `Table1` to `Table2`. I can use the following syntax:

    INSERT INTO Table2(Id, Name) SELECT Id, Name FROM Table1

However, in my case, duplicate IDs might exist in `Table2` (in my case, it&#39;s just &quot;`1`&quot;) and I don&#39;t want to copy that again as that would throw an error. 

I can write something like this:

    IF NOT EXISTS(SELECT 1 FROM Table2 WHERE Id=1)
    INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 
    ELSE
    INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 WHERE Table1.Id&lt;&gt;1

Is there a better way to do this without using `IF - ELSE`? I want to avoid two `INSERT INTO-SELECT` statements based on some condition.

Avoid duplicates in INSERT INTO SELECT query in SQL Server

I&#39;m using Python to write to a postgres database:

    sql_string = &quot;INSERT INTO hundred (name,name_slug,status) VALUES (&quot;
    sql_string += hundred + &quot;, &#39;&quot; + hundred_slug + &quot;&#39;, &quot; + status + &quot;);&quot;
    cursor.execute(sql_string)

But because some of my rows are identical, I get the following error:

    psycopg2.IntegrityError: duplicate key value  
      violates unique constraint &quot;hundred_pkey&quot;

How can I write an &#39;INSERT unless this row already exists&#39; SQL statement? 

I&#39;ve seen complex statements like this recommended:

    IF EXISTS (SELECT * FROM invoices WHERE invoiceid = &#39;12345&#39;)
    UPDATE invoices SET billed = &#39;TRUE&#39; WHERE invoiceid = &#39;12345&#39;
    ELSE
    INSERT INTO invoices (invoiceid, billed) VALUES (&#39;12345&#39;, &#39;TRUE&#39;)
    END IF

But firstly, is this overkill for what I need, and secondly, how can I execute one of those as a simple string?

Postgres: INSERT if does not exist already

I have a table that I am trying to update multiple values at once.  Here is the table schema:

        Column     |  Type   | Modifiers 
    ---------------+---------+-----------
     user_id       | integer | 
     subservice_id | integer |

I have the `user_id` and want to insert multiple `subservice_id`&#39;s at once.  Is there a syntax in `Postgres` that will let me do something like this

    insert into user_subservices(user_id, subservice_id) values(1, [1, 2, 3]);

How would I do this?

How do I insert multiple values into a postgres table at once?

I&#39;m using this code and I&#39;m beyond frustration:

    try {
        $dbh = new PDO(&#39;mysql:dbname=&#39; . DB . &#39;;host=&#39; . HOST, USER, PASS);
        $dbh-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $dbh-&gt;setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, &quot;SET NAMES &#39;utf8&#39;&quot;);
    }
    catch(PDOException $e)
    {
        ...
    }
    $stmt = $dbh-&gt;prepare(&#39;INSERT INTO table(v1, v2, ...) VALUES(:v1, :v2, ...)&#39;);
    $stmt-&gt;bindParam(&#39;:v1&#39;, PDO::PARAM_NULL); // --&gt; Here&#39;s the problem

`PDO::PARAM_NULL, null, &#39;&#39;,` all of them fail and throw this error:

&gt; **Fatal error**: Cannot pass parameter 2 by reference in /opt/...

Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?

I have a field in a MySQL table which has a `timestamp` data type. I am saving data into that table. But when I pass the timestamp (`1299762201428`) to the record, it automatically saves the value `0000-00-00 00:00:00` into that table.

How can I store the timestamp in a MySQL table?

Here is my `INSERT` statement:

    INSERT INTO table_name (id,d_id,l_id,connection,s_time,upload_items_count,download_items_count,t_time,status)
    VALUES (1,5,9,&#39;2&#39;,1299762201428,5,10,20,&#39;1&#39;),
           (2,5,9,&#39;2&#39;,1299762201428,5,10,20,&#39;1&#39;)


Saving timestamp in mysql table using php

I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng).  Right now, if these values are not entered I pass along an empty string as a value.  How do I pass an explicit NULL value to MySQL (if empty)?

    $query = &quot;INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
              VALUES (&#39;$notes&#39;, &#39;$id&#39;, TRIM(&#39;$imageUploaded&#39;), &#39;$lat&#39;, &#39;$long&#39;, 
                      &#39;$intLat&#39;, &#39;$intLng&#39;)&quot;;
    mysql_query($query);



MySQL and PHP - insert NULL rather than empty string

I want to insert data into 3 tables with a single query.  
My tables looks like below:

    CREATE TABLE sample (
       id        bigserial PRIMARY KEY,
       lastname  varchar(20),
       firstname varchar(20)
    );

    CREATE TABLE sample1(
       user_id    bigserial PRIMARY KEY,
       sample_id  bigint REFERENCES sample,
       adddetails varchar(20)
    );
    
    CREATE TABLE sample2(
       id      bigserial PRIMARY KEY,
       user_id bigint REFERENCES sample1,
       value   varchar(10)
    );

I will get a key in return for every insertion and I need to insert that key in the next table.  
My query is:

    insert into sample(firstname,lastname) values(&#39;fai55&#39;,&#39;shaggk&#39;) RETURNING id;
    insert into sample1(sample_id, adddetails) values($id,&#39;ss&#39;) RETURNING user_id;
    insert into sample2(user_id, value) values($id,&#39;ss&#39;) RETURNING id;

But if I run single queries they just return values to me and I cannot reuse them in the next query immediately.

How to achieve this?

Insert data in 3 tables at a time using Postgres

So I read the other posts but this question is unique. So this SQL dump file has this as the last entry.

    INSERT INTO `wp_posts` VALUES(2781, 3, &#39;2013-01-04 17:24:19&#39;, &#39;2013-01-05 00:24:19&#39;.

I&#39;m trying to insert this value to the table...

    INSERT INTO `wp_posts` VALUES(5, 5, &#39;2005-04-11 09:54:35&#39;, &#39;2005-04-11 17:54:35&#39;

it gives me the error, &quot;Column count doesn&#39;t match value count at row 1.&quot; So I&#39;m lost on the concept of how the column and row apply here. 

Doesn&#39;t `2781,3` mean row 2781 and column 3? And doesn&#39;t `5,5` mean row 5 and column 5?


Column count doesn&#39;t match value count at row 1

I am trying to insert data into an existing table and keep receiving an error.

    INSERT INTO Patient  
    (
      PatientNo,
      PatientFirstName,
      PatientLastName,
      PatientStreetAddress,
      PatientTown,
      PatientCounty,
      PatientPostcode,
      DOB,
      Gender,
      PatientHomeTelephoneNumber,
      PatientMobileTelephoneNumber
    )
    VALUES 
    (
      121, 
      &#39;Miles&#39;, 
      &#39;Malone&#39;, 
      &#39;64 Zoo Lane&#39;, 
      &#39;Clapham&#39;, 
      &#39;United Kingdom&#39;,
      &#39;SW4 9LP&#39;,
      &#39;1989-12-09&#39;,
      &#39;M&#39;,
      02086950291,
      07498635200
    );


Error:
   
    Error starting at line : 1 in command -
    INSERT INTO Patient (PatientNo,PatientFirstName,PatientLastName,PatientStreetAddress,PatientTown,PatientCounty,PatientPostcode,DOB,Gender,PatientHomeTelephoneNumber,PatientMobileTelephoneNumber)
    VALUES (121, &#39;Miles&#39;, &#39;Malone&#39;, &#39;64 Zoo Lane&#39;, &#39;Clapham&#39;, &#39;United Kingdom&#39;,&#39;SW4 9LP&#39;,&#39;1989-12-09&#39;,&#39;M&#39;,02086950291,07498635200)
    Error report -
    SQL Error: ORA-01861: literal does not match format string
    01861. 00000 -  &quot;literal does not match format string&quot;
    *Cause:    Literals in the input must be the same length as literals in
               the format string (with the exception of leading whitespace).  If the
               &quot;FX&quot; modifier has been toggled on, the literal must match exactly,
               with no extra whitespace.
    *Action:   Correct the format string to match the literal.


Just not sure why this keeps happening I am learning SQL at the moment, any help will be greatly appreciated!

SQL Error: ORA-01861: literal does not match format string 01861

Say I have a simple table that has the following fields:

 1. ID: int, autoincremental (identity), primary key
 2. Name: varchar(50), unique, has unique index
 3. Tag: int

I never use the ID field for lookup, because my application is always based on working with the Name field. 

I need to change the Tag value from time to time. I&#39;m using the following trivial SQL code: 

    UPDATE Table SET Tag = XX WHERE Name = YY;

I wondered if anyone knows whether the above is always faster than:

    DELETE FROM Table WHERE Name = YY;
    INSERT INTO Table (Name, Tag) VALUES (YY, XX);

Again - I know that in the second example the ID is changed, but it does not matter for my application.



In SQL, is UPDATE always faster than DELETE+INSERT?

What is the correct SQL syntax to insert a value with an apostrophe in it?

    Insert into Person
      (First, Last)
    Values
      &#39;Joe&#39;,
      &#39;O&#39;Brien&#39;

I keep getting an error as I think the apostrophe after the O is the ending tag for the value.


How to insert a value that contains an apostrophe (single quote)?

I have no idea if this is even remotely correct. I have a class where I would like to update the database if the fields currently exist or insert if they do not. The complication is that I am doing a joining 3 tables (set_colors, school_art, baseimage)  

Any help would be really great.

Here is what I have: 

    public function set_layer_colors($value) {
    	global $db;
    	
    	$result_array = mysql_query(&quot;
        IF EXISTS(SELECT * FROM set_colors WHERE school_art_id = &#39;{$value}&#39;)
    	  
    	  UPDATE set_colors (school_art_id, baseimage_id, sub_folder, layer)
    		SELECT school_art.id, baseimage.id, baseimage.sub_folder, baseimage.layer
    		FROM school_art 
    		JOIN baseimage ON baseimage.base_folder = school_art.series_code 
    		WHERE baseimage.image_type = &#39;B&#39; ORDER BY school_art.id 
    		
    	ELSE
    	
    	 INSERT INTO set_colors (school_art_id, baseimage_id, sub_folder, layer)
    		SELECT school_art.id, baseimage.id, baseimage.sub_folder, baseimage.layer
    		FROM school_art 
    		JOIN baseimage ON baseimage.base_folder = school_art.series_code 
    		WHERE baseimage.image_type = &#39;B&#39; ORDER BY school_art.id 
    		&quot;);
    	
    	return $result_array;
    }

PHP MYSQL UPDATE if Exist or INSERT if not?

I want to run a set of queries to insert some data into an SQL table but only if the record satisfying certain criteria are met. The table has 4 fields: `id` (primary), `fund_id`, `date` and `price`

I have 3 fields in the query: `fund_id`, `date` and `price`.

So my query would go something like this:

    INSERT INTO funds (fund_id, date, price)
        VALUES (23, &#39;2013-02-12&#39;, 22.43)
        WHERE NOT EXISTS (
           SELECT * 
           FROM funds 
           WHERE fund_id = 23
             AND date = &#39;2013-02-12&#39;
        );

So I only want to insert the data if a record matching the `fund_id` and `date` does not already exist. If the above is correct it strikes me as quite an inefficient way of achieving this as an additional select statement must be run each time.

Is there a better way of achieving the above?

Edit: For clarification neither `fund_id` nor `date` are unique fields; records sharing the same fund_id or date will exist but no record should have both the same fund_id and date as another.

SQL Insert into table only if record doesn&#39;t exist

I&#39;m writing a booking procedure for a mock airline booking database and what I really want to do is something like this:

    IF EXISTS (SELECT * FROM LeadCustomer 
        WHERE FirstName = &#39;John&#39; AND Surname = &#39;Smith&#39;) 
    THEN
       INSERT INTO LeadCustomer (Firstname, Surname, BillingAddress, email) 
       VALUES (&#39;John&#39;, &#39;Smith&#39;, &#39;6 Brewery close,
                Buxton, Norfolk&#39;, &#39;cmp.testing@example.com&#39;);

But Postgres doesn&#39;t support `IF` statements without loading the PL/pgSQL extension. I was wondering if there was a way to do some equivalent of this or if there&#39;s just going to have to be some user interaction in this step?

Conditional INSERT INTO statement in postgres

Is the database query faster if I insert multiple rows at once:

like

    INSERT....
    
    UNION
    
    INSERT....
    
    UNION

(I need to insert like 2-3000 rows)



Inserting multiple rows in mysql

I&#39;m looking for a good way to perform multi-row inserts into an Oracle 9 database.  The following works in MySQL but doesn&#39;t seem to be supported in Oracle.

    INSERT INTO TMP_DIM_EXCH_RT 
	(EXCH_WH_KEY, 
     EXCH_NAT_KEY, 
     EXCH_DATE, EXCH_RATE, 
     FROM_CURCY_CD, 
     TO_CURCY_CD, 
     EXCH_EFF_DATE, 
     EXCH_EFF_END_DATE, 
     EXCH_LAST_UPDATED_DATE) 
	VALUES
		(1, 1, &#39;28-AUG-2008&#39;, 109.49, &#39;USD&#39;, &#39;JPY&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;),
		(2, 1, &#39;28-AUG-2008&#39;, .54, &#39;USD&#39;, &#39;GBP&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;),
		(3, 1, &#39;28-AUG-2008&#39;, 1.05, &#39;USD&#39;, &#39;CAD&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;),
		(4, 1, &#39;28-AUG-2008&#39;, .68, &#39;USD&#39;, &#39;EUR&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;),
		(5, 1, &#39;28-AUG-2008&#39;, 1.16, &#39;USD&#39;, &#39;AUD&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;),
		(6, 1, &#39;28-AUG-2008&#39;, 7.81, &#39;USD&#39;, &#39;HKD&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;, &#39;28-AUG-2008&#39;);



All Sql Insert Solutions on Gang of Coders

Total of 19 Sql Insert Solutions