Tag Info

New answers tagged

0

You already found that you have to use dynamic SQL with EXECUTE. What you are missing: If child tables are not guaranteed to share the same row type , you must add a target column list to your INSERT statement or you are bound to run into errors or worse: it might work in surprising ways. You need to defend against SQL injection. Table names have to be ...


1

Try this form: EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW; It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays. An even more modern and cleaner version (quote the table's name if necessary): EXECUTE format('insert into %I values ($1.*)', child) USING NEW;


1

Your trigger tries to do something that cannot be done in MySQL. You cannot use an SQL statement (DELETE, in your case) on the table that is associated to the trigger. You will get an error like this: ERROR 1442 (HY000): Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. ...


2

Check Codd's rules - there's no mention of "ordering". You can order by a PRIMARY KEY (in the SELECT statement) but you aren't forced to. If you use ORDER BY in your SELECT for your INSERT and use MySQL's AUTO_INCREMENT to INSERT into your target table, then you should have the target table's PK be "ordered" by your criteria. You should be aware that the ...


5

A table is, by definition, an unordered bag of rows. There is no guarantee that if you say SELECT * FROM table you will get the rows back in the same order you inserted. Think about throwing a bunch of popsicle sticks on the ground while blindfolded; now take off the blindfold and tell me which one hit the floor last. No ORDER BY is essentially telling the ...


0

Why don't you make a third table called projects_payment and you use to make a relationship between projects and payments and you could have 1:N relation. projects_payment table should be like: projects_payment: project_id (PK) (FK to project.id) payment_id (PK) (FK to payment.id) modification_date (You can use for amount date) Any other field. ...


2

I've made a function where you just put the string and the output will be in Capital Letters: DELIMITER // CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1 DETERMINISTIC BEGIN DECLARE vPOSICION INT DEFAULT 0; DECLARE vTMP VARCHAR(150) DEFAULT ''; DECLARE vRESULTADO ...


1

CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1)) You missed one more closed parenthesis in your statement CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1))) 2.The query you wrote for concatenation returns only first letter in capital letters of both fist and last names and it doesn't print space between them. example :First ...


3

Insert query: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email,avatar_url,rep_type) SELECT Id, CONCAT(UCASE(MID(firstname,1,1)),LCASE(MID(firstname,2)),' ',UCASE(MID(lastname,1,1)),LCASE(MID(lastname,2))), username, firstname, lastname, email, 'www.some_static_url.com', '1' FROM veeva.user ...


1

When an exception occurs everything is rolled back. You want to execute selected code "outside" the current transaction context, which is commonly referred to as autonomous transactions. This is not currently implemented (as of pg 9.4). There is an item in the Postgres TODO wiki, but it's a tricky matter, don't hold your breath. For now you can use the ...


0

It is sorting by the "conGenLedger" uniqueidentifier field because you created a CLUSTERED primary key on that field. A clustered index will store the data in the database alphabetically. If you want the data to be "clustered" (sorted by default) by the ID field, you need to create a CLUSTERED index on the ID field. Since a table can only have 1 ...


8

If you don't specify an order by clause your RDBMS is free to return the results as it chooses. Usually it will return the results in the order it can retrieve the records the fastest. Since you have the column codGenLedger as the clustered key the records will be stored ordered by codGenLedger physically on disk (unless your indexes are fragmented, but ...


0

There is an error in line: $sql = "INSERT INTO transaction_details (`edi_transaction_id`,`details`) VALUES ($edi_transaction_id,$details) "; This should be: $sql = "INSERT INTO transaction_details (`transaction_id`,`details`) VALUES ($edi_transaction_id,$details) "; Finally in your code you assign the text of the command to the variable $sql, but do ...


0

An alternative is to find the duplicates BEFORE insertion, join to the list of duplicates and only insert the ones not found in the duplicate list. I'm an Oracle guy, so here's Oracle syntax which is not identical to PostgreSQL but should illustrate the point: WITH qry_find_dups as ( SELECT username, date, time, x0, x1, x2, COUNT(*) AS dupcount ...


0

Instead of handling the error try to avoid the exception by not inserting those rows that already exist: INSERT INTO entries (username, date, time, x0, x1, x2, x3, x4, x5, x6) SELECT t.username, t.date, t.time, t.x0, t.x1, t.x2, t.x3, t.x4, t.x5, t.x6 FROM mytesttable t where not exists (select 1 from entries e where ...


0

UPDATED The reason for the errors is that the code that you were trying to reproduce in Optimal way to ignore duplicate inserts? is the body of a PL/pgSQL procedure or of a SQL DO statement, in which the EXCEPTION block can be used to do something when a transaction fails. Instead you wrote the three statements as SQL statements, of which the first is ...


0

Why not just use distinct on like this: INSERT INTO entries(username, date, time, x0, x1, x2, x3, x4, x5, x6) SELECT DISTINCT ON (username, date, time, x0, x1, x2) username, date, time, x0, x1, x2, x3, x4, x5, x6 FROM mytesttable; ? But beware that this will retain the first row for every distinct combination -- which might not be the right thing without ...


1

You can use setNull(): preparedStatement.setNull(8, Types.VARCHAR); Although I have seen drivers that do not properly support that, in that case simply try: preparedStatement.setString(8, null);


0

You want to update field ID and set to 10? Some sample: UPDATE test.personal_info SET ID = '10' WHERE db1.table1 = db2.table2 OR UPDATE test.personal_info SET ID = '10' ON <DB.TABLE> JOIN TABLE1.FIELD = DB.TABLE2.FIELD


1

What you need is some Dynamic SQL. STEP 01 : Retrieve the DB SET @IMEI = 123456789123456; SELECT db INTO @db FROM imeiDB WHERE IMEI = @IMEI; STEP 02 : Create the SQL to do the INSERT into the retrieve DB SET @sql = CONCAT('INSERT INTO ',@db,'.table1 (IMEI,data) VALUES (',@IMEI,',"somestuff"'); SELECT @sql\G STEP 03 : Execute the SQL Statement you just ...



Top 50 recent answers are included