— Login to psql and run the following — What is the result? SELECT MAX(id) FROM your_table; — Then run… — This should be higher than the last result. SELECT nextval(‘your_table_id_seq’); — If it’s not higher… run this set the sequence last to your highest id. — (wise to run a quick pg_dump first…) BEGIN; … Read more
don’t forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous. When multiple threads will try to perform Insert-or-update you can easily get primary key violation. Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone. To avoid deadlocks and PK violations you can use something like this: begin … Read more
;WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn FROM DocumentStatusLogs ) SELECT * FROM cte WHERE rn = 1 If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead As for normalised or … Read more
ALTER USER myuser WITH SUPERUSER; You can read more at the Documentation
Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the … Read more
The short answer is yes, yes there is a way to get around mysql_real_escape_string(). #For Very OBSCURE EDGE CASES!!! The long answer isn’t so easy. It’s based off an attack demonstrated here. The Attack So, let’s start off by showing the attack… mysql_query(‘SET NAMES gbk’); $var = mysql_real_escape_string(“\xbf\x27 OR 1=1 /*”); mysql_query(“SELECT * FROM test … Read more
SELECT id, IF(type=”P”, amount, amount * -1) as amount FROM report See http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html. Additionally, you could handle when the condition is null. In the case of a null amount: SELECT id, IF(type=”P”, IFNULL(amount,0), IFNULL(amount,0) * -1) as amount FROM report The part IFNULL(amount,0) means when amount is not null return amount else return 0.
Here’s how I’d do it – For Schemas (or Databases – they are synonyms): SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = “schemaname”; For Tables: SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = “schemaname” AND T.table_name = “tablename”; For Columns: SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = “schemaname” AND table_name … Read more
GROUP BY YEAR(record_date), MONTH(record_date) Check out the date and time functions in MySQL.
Use sp_rename EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’ See: SQL SERVER – How to Rename a Column Name or Table Name Documentation: sp_rename (Transact-SQL) For your case it would be: EXEC sp_RENAME ‘table_name.old_name’, ‘new_name’, ‘COLUMN’ Remember to use single quotes to enclose your values.