You aren’t inserting a value for id. Since you don’t explicitly set it, it’s implicitly given a null value, which is, of course, not a valid value for a primary key column. You can avoid this entire situation by defining this column as serial instead of a plain old integer, and leave all the heavy … Read more
To populate the column during insert, use a DEFAULT value: CREATE TABLE users ( id serial not null, firstname varchar(100), middlename varchar(100), lastname varchar(100), email varchar(200), timestamp timestamp default current_timestamp ) Note that the value for that column can explicitly be overwritten by supplying a value in the INSERT statement. If you want to prevent … 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
You can use a correlated subquery with a WHERE NOT EXISTS clause to ensure that your update will not generate duplicates, like : UPDATE mytable t SET (col1, col2) = (‘AAA’, ‘BBB’) WHERE t.col1 = ‘AAB’ and t.col2 = ‘BBA’ AND NOT EXISTS ( SELECT 1 FROM mytable WHERE col1 = ‘AAA’ AND col2 = … Read more
You’re missing quotes around the first value, it should be INSERT INTO LOCATION VALUES(‘PQ95VM’, ‘HAPPY_STREET’, ‘FRANCE’); Incidentally, you’d be well-advised to specify the column names explicitly in the INSERT, for reasons of readability, maintainability and robustness, i.e. INSERT INTO LOCATION (POSTCODE, STREET_NAME, CITY) VALUES (‘PQ95VM’, ‘HAPPY_STREET’, ‘FRANCE’);
Please try below code to insert values in tables using for loop. do $$ begin for r in 1..1000 loop insert into schema_name.table_name(id) values(r); end loop; end; $$;
Your column username seems to be an array type, so the literal ‘mahman’ is not valid input for it. It would have to be ‘{mahman}’: INSERT INTO user_data.user_data (username,randomint) VALUES (‘{mahman}’,1); (Or make it a plain varchar column or text column instead.) Update confirms it: character varying(50)[] is an array of character varying(50). About array … Read more
You may use -v : Verbose mode (show progress) in your command, or there’s another method using Pipe Viewer (pv) which shows the progress of the gzip, gunzip command as follows: $ pv database1.sql.gz | gunzip | mysql -u root -p database1 This will output progress similar to scp: $ pv database1.sql.gz | gunzip | … Read more
There are a number of factors at work here: Network latency and round-trip delays Per-statement overheads in PostgreSQL Context switches and scheduler delays COMMIT costs, if for people doing one commit per insert (you aren’t) COPY-specific optimisations for bulk loading Network latency If the server is remote, you might be “paying” a per-statement fixed time … Read more
You’re looking for UPDATE not insert. UPDATE mytable SET table_column = ‘test’; UPDATE will change the values of existing rows (and can include a WHERE to make it only affect specific rows), whereas INSERT is adding a new row (which makes it look like it changed only the last row, but in effect is adding … Read more