Archive for the ‘DataBase’ Category

SSMS Invalid Column name

Friday, December 21st, 2012

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

MS SQL Truncating seconds (and milliseconds) from a datetime

Wednesday, December 19th, 2012

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

Monday, December 3rd, 2012

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

Monday, December 3rd, 2012

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

Monday, August 13th, 2012

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)

Friday, March 16th, 2012
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

Friday, March 16th, 2012

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

Sunday, October 3rd, 2010

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

Wednesday, August 18th, 2010

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

Wednesday, July 14th, 2010

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(),'-','')));