Sometimes the Cache of SSMS gets “corrupted” or fails to update, this can lead to Red squiggles under column namnes, table names, basiacally anything (Also SSMS will claim the name does not exist with for instance the error message invalid column namn).
When this error lies in SSMS, then refreshing the cache is the solution, and this is done with “Ctrl-Shitf-R“
Category Archives: DataBase
MS SQL Truncating seconds (and milliseconds) from a datetime
By converting a DateTime to a char(16) the seconds and milliseconds are truncated from the DateTime.
DECLARE @d datetime SELECT @d = GETDATE() SELECT @d, CONVERT(CHAR(16), @d, 121) --2012-12-04 11:50:42.160 2012-12-04 11:50 |
MS SQL Update WHERE value is NULL
This example replaces null with an empty string in SomeTable.SomeColum
UPDATE SomeTable SET SomeColumn = '' WHERE SomeColumn IS NULL |
The important thing to note here is that the check to find the value that is NULL is based on “IS NULL” (it is not possible to use the equals sign to check for NULL)
MS SQL SELECT WHERE value is NULL
Here is how to do a select where a column have NULL as the stored value.
SELECT * FROM SomeTable WHERE SomeColumn IS NULL |
Equals sign does not work for null, trying to use the equals operator generates the result Unknown (it is not true nor false)
IF i = NULL THEN SELECT 'Result is True' ELSEIF NOT(i = NULL) THEN SELECT 'Result is False' ELSE SELECT 'Result is Unknown'; --This will end with Result is Unknown |
This is based on the fact that null was introduced to represent “missing information and inapplicable information” in the database model and based on that statement using equals null was not implemented.
SQLServer Adding a column to a table
Adding a column to existing table is not that hard. One thing to keep in mind, if the column is not nullable then a default value is mandatory.
ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} |
PostgreSQL getting the lastest item from a history table (the entry with the latest timestamp)
SELECT * FROM history_items WHERE timeofhistory=(SELECT MAX(timeofhistory) FROM history_items) |
The sub query will fetch the highest timestamp and then that is used as a condition for the query.
note: If you are using a version older than 8.1, then MIN and MAX is not able to use indexes, so instead use a ORDER BY and LIMIT clause
SELECT * FROM history_items ORDER BY timeofhistory DESC LIMIT 1 |
This will of course work for versions 8.1 and higher as well, it should be identical to MAX and MIN in time.
PostgreSQL Getting a unix time from a timstamp with time zone
If we have a timestamp with a timezone then getting the unix time from this is simple.
Note now() returns a timestamp with timezone so this query gets the current unix time
SELECT EXTRACT(epoch FROM now()); |
PostgreSQL 8.3 adding uuid-ossp on Debian
UUID is a nice way to generate (almost) unique id’s.
However on PostgreSQL 8.3 the data type exists, but if you wish to be able to generate UUID’s you need to get a module (in this case uuid-ossp).
On Debian this module is in the contrib package, so if you have not installed this before do it now
user@server:~$ sudo apt-get install postgresql-contrib |
Then you need to “load” the module into PostgreSQL; or more accurately you tell PostgreSQL about the new functions this module has, how to reach them etc.
Depending on you security settings (in pg_hba.conf) you may need to log in as the postgres user on your system.
postgres@sever:~$ psql -d MYDB -U postgres -f /usr/share/postgresql/8.3/contrib/uuid-ossp.sql |
MySQL execute statements directly from the (linux) console
Executing a query quickly from the console is nice to be able to do (without having to enter the MySQL console).
- This will simply output the result to the console where you are.
user@server:~$ mysql -uUSERNAME -p DATABASENAME --execute "REPAIR TABLE position";
Replace USERNAME and DATABASENAME with your vars
- If you would like the output to a file
mysql -uUSERNAME -p DATABASENAME --execute "SELECT * FROM test" > test.sql;
Replace USERNAME and DATABASENAME with your vars
- If you would like the output to be sent as a email
mysql -uUSERNAME -p DATABASENAME --execute "SELECT * FROM test" | mail -s 'SUBJECT' example@mail.com
Replace USERNAME, DATABASENAME , SUBJECT and example@mail.com with your vars
MYSQL Creating several UUIDs at one single time
I recently ran into the problem that I had too add two rows to a database that uses a UUID (in this case converted to a binary) as a key.
The problem I had was that running two insert statements after each other (just separated by “;”) then the same id was generated.
A workaround I have found is to sleep for a second between the two statements.
Examples (using SELECT instead of INSERT):
Getting the same id twice
SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1, UNHEX(REPLACE(UUID(),'-','')) AS uuid2; -- Shows: "849392548f1311df91b70019dbd264f1" "849392908f1311df91b70019dbd264f1" |
Adding a sleep to get different ids
SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1, SLEEP(1), UNHEX(REPLACE(UUID(),'-','')) AS uuid2 -- Shows: "3901c1928f1511df91b70019dbd264f1" "0" "399a5b148f1511df91b70019dbd264f1" |
And another one using INSERTS
INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-',''))); SLEEP(1); INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-',''))); |