Use data-modifying CTEs: WITH ins1 AS ( INSERT INTO sample(firstname, lastname) VALUES (‘fai55’, ‘shaggk’) — ON CONFLICT DO NOTHING — optional addition in Postgres 9.5+ RETURNING id AS sample_id ) , ins2 AS ( INSERT INTO sample1 (sample_id, adddetails) SELECT sample_id, ‘ss’ FROM ins1 RETURNING user_id ) INSERT INTO sample2 (user_id, value) SELECT user_id, ‘ss2’ … Read more
You can’t. However, you CAN use a transaction and have both of them be contained within one transaction. START TRANSACTION; INSERT INTO table1 VALUES (‘1′,’2′,’3’); INSERT INTO table2 VALUES (‘bob’,’smith’); COMMIT; http://dev.mysql.com/doc/refman/5.1/en/commit.html
Using NOT EXISTS: INSERT INTO TABLE_2 (id, name) SELECT t1.id, t1.name FROM TABLE_1 t1 WHERE NOT EXISTS(SELECT id FROM TABLE_2 t2 WHERE t2.id = t1.id) Using NOT IN: INSERT INTO TABLE_2 (id, name) SELECT t1.id, t1.name FROM TABLE_1 t1 WHERE t1.id NOT IN (SELECT id FROM TABLE_2) Using LEFT JOIN/IS NULL: INSERT INTO TABLE_2 (id, … Read more
You need to use bindValue, not bindParam bindParam takes a variable by reference, and doesn’t pull in a value at the time of calling bindParam. I found this in a comment on the PHP docs: bindValue(‘:param’, null, PDO::PARAM_INT); P.S. You may be tempted to do this bindValue(‘:param’, null, PDO::PARAM_NULL); but it did not work for … Read more
The error means that you are providing not as much data as the table wp_posts does contain columns. And now the DB engine does not know in which columns to put your data. To overcome this you must provide the names of the columns you want to fill. Example: insert into wp_posts (column_name1, column_name2) values … Read more
See populate a database in the PostgreSQL manual, depesz’s excellent-as-usual article on the topic, and this SO question. (Note that this answer is about bulk-loading data into an existing DB or to create a new one. If you’re interested DB restore performance with pg_restore or psql execution of pg_dump output, much of this doesn’t apply … Read more
Postgres 9.5 (released since 2016-01-07) offers an “upsert” command, also known as an ON CONFLICT clause to INSERT: INSERT … ON CONFLICT DO NOTHING/UPDATE It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.
Escape the apostrophe (i.e. double-up the single quote character) in your SQL: INSERT INTO Person (First, Last) VALUES (‘Joe’, ‘O”Brien’) /\ right here The same applies to SELECT queries: SELECT First, Last FROM Person WHERE Last=”O”‘Brien’ The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string … Read more
If you never want to have duplicates, you should declare this as a table constraint: CREATE TABLE bookmarks( users_id INTEGER, lessoninfo_id INTEGER, UNIQUE(users_id, lessoninfo_id) ); (A primary key over both columns would have the same effect.) It is then possible to tell the database that you want to silently ignore records that would violate such … Read more
In Oracle, to insert multiple rows into table t with columns col1, col2 and col3 you can use the following syntax: INSERT ALL INTO t (col1, col2, col3) VALUES (‘val1_1’, ‘val1_2’, ‘val1_3’) INTO t (col1, col2, col3) VALUES (‘val2_1’, ‘val2_2’, ‘val2_3’) INTO t (col1, col2, col3) VALUES (‘val3_1’, ‘val3_2’, ‘val3_3’) . . . SELECT 1 … Read more