Databases

A database is a system intended to organize, store, and retrieve large amounts of data easily.

snippet - Convert interval to number in postgresql
4 Feb, 2021
Convert interval to number in postgresql
PostgreSQL
There is a simple function for when you need to extract count of days from interval. It is returned as as double, but converted to integer in this example. You get interval when you subtract two...
snippet - Azure MSSQL: Create read only user
26 Jan, 2021
Azure MSSQL: Create read only user
MSSQL
How to create reader / read-only user in azure database.
snippet - Set smalldatetime default value to current date/time
25 Jan, 2021
Set smalldatetime default value to current date/time
MSSQL
This example shows how to set the default value of a smalldatetime column to current date time.
snippet - Move tables to another schema in Postgres
26 Nov, 2020
Move tables to another schema in Postgres
PostgreSQL
This form moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are moved as well.
snippet - Compare values of two tables in PostgreSQL
25 Mar, 2020
Compare values of two tables in PostgreSQL
PostgreSQL
Compare values from columns of multiple tables. This query can be used to compare two tables in such manner in postgres.
snippet - Clone/Duplicate a Table in PostgreSQL
25 Mar, 2020
Clone/Duplicate a Table in PostgreSQL
PostgreSQL
This snippet shows you how you can copy a whole table to another one. Useful if you are about to change data in a table and later need to compare it with EXCEPT.
snippet - PostgreSql return inserted row primary key
28 Nov, 2019
PostgreSql return inserted row primary key
PostgreSQL
When you have primary keys generated by the database not by the application, then you might need it to be returned. In postgres you have to use RETURNING function in your sql query in order to have...
snippet - Installing mssql server on ArchLinux
6 Jul, 2019
Installing mssql server on ArchLinux
MSSQL
Installing mssql server on Arch Linux is actually quite simple.
snippet - MySql create database with user
20 Feb, 2019
MySql create database with user
MySQL
This snippet shows how to create a new database and user with assigning permissions of the newly created database to the user. In this example I use database name as user name for clarity and...
snippet - MySQL simple update query example
15 Aug, 2018
MySQL simple update query example
MySQL
There are actually two ways of updating in MySQL. This example uses the simpler way. Other way is to define columns and corresponding values sepparately.
snippet - MySQL: insert/update multiple rows with a single query
16 May, 2018
MySQL: insert/update multiple rows with a single query
MySQL
To insert multiple rows with a single query you can use regular insert with multiple value rows. You can also use update if you have primary key set.
snippet - Import csv to MySQL FAST
16 May, 2018
Import csv to MySQL FAST
MySQL
Import csv file to MySQL, the fastest way is to use a direct import with query. You have to have the columns created the way you need.
snippet - MySQL export query as csv directly
8 May, 2018
MySQL export query as csv directly
MySQL
Example query for direct csv saving from mysql. Using query to import or export data is the fastest method. You can export your query results as csv with custom syntax. You have to use union to join...
snippet - Arch Linux:
1 Jun, 2017
Arch Linux: "mtop: installing libmariadbclient (10.1.24-1) breaks dependency 'libmysqlclient'"
MySQL
Got an error while trying to update the system "mtop: installing libmariadbclient (10.1.24-1) breaks dependency 'libmysqlclient'". The package 'libmariadbclient' dropped its provide for '...
snippet - MySql error: Incorrect date value: '0000-00-00'
27 Jan, 2017
MySql error: Incorrect date value: '0000-00-00'
MySQL
From version 5.7 MySQL stops supporting zeros value in date / datetime. If you have those values in your database you get errors for queries, like group by. Also you are not able to insert zero dates...
snippet - Is MySQL Table empty
21 Sep, 2016
Is MySQL Table empty
MySQL
If you need to show user different content whether a table is empty or not. So you would need to count table rows for that. Counting every row will iterate through the table rows and is really slow....
snippet - MySQL: Group by date when using datetime datatype
25 Apr, 2016
MySQL: Group by date when using datetime datatype
MySQL
Group by date when using datetime datatype. In this snippet the grouping is used to pull out monthly statistics. The query is pretty much self explanatory. Group by in mysql can help you to bring...
snippet - Show progress of MySQL import
6 Oct, 2015
Show progress of MySQL import
MySQL
Have you ever had to do bigger mysql import? If you do, you probably get annoyed by not being able to know how long it will take to complete. There is a command called "pv". Basically it allows a...
snippet - MySQL InnoDB performance tuning / tweaking
5 Dec, 2014
MySQL InnoDB performance tuning / tweaking
MySQL
MySQL configuration that I am using for developing Drupal in localhost. The config file can be found from /etc/mysql/my.cnf under Linux. I would use it for any apache instance tough. You might get...
snippet - MySQL: Convert table character set to utf8
14 Mar, 2014
MySQL: Convert table character set to utf8
MySQL
Using this the query in this snippet enables you to convert character set to utf8. This works in mysql or mysql like database, drop-in databases like mariadb for an example.
snippet - MySQL: String Replace
9 Nov, 2013
MySQL: String Replace
MySQL
Snippet showing, how to do string replace in MySQL. In this example we update a field with the same field content except we run it through string replace. This is the same as find...
snippet - Drop all tables from database in mysql
8 Nov, 2013
Drop all tables from database in mysql
MySQL
This snippet shows you how to drop all tables from database in mysql. Unsing drop database will just delete the database and later you just recreate it with create database query.
written tutorial - SQL Joins - Visual Representation
22 Jul, 2013
SQL Joins - Visual Representation
MySQL
Visualization of sql joins is very helpful when you are not wrinting sql every day. This example uses MySQL joins, but same applies to any SQL language (postgresql, mssql, etc...). Visual data and...
snippet - Command line: Import MySQL database
15 Jul, 2013
Command line: Import MySQL database
MySQL
How to import .sql file in MySQL through command line.
snippet - Command line: List all MySQL databases
15 Jul, 2013
Command line: List all MySQL databases
MySQL
Want to check the databases you have created using the MySQL Command Line Client? Use mysql command to connect to mysql server and list available databases.
snippet - MySQL - Get first letters
10 Jul, 2013
MySQL - Get first letters
MySQL
This snippet shows you how to select discinct first letters of titles. After getting the first letters you might want to transliterate the characters, you can do it in mysql by custom function where...
snippet - MySQL - Remove trailing carriage return
5 May, 2013
MySQL - Remove trailing carriage return
MySQL
Sometimes when you import lines from txt files you might get \r in the end of lines. When you think it's easier to remove trailing carriage returns than reimporting then you can use this query.
snippet - MySQL - Get years of from date field
21 Mar, 2013
MySQL - Get years of from date field
MySQL
If you have a lot of content with timestamps, you might want to get all the years where the content belongs. For an example created. With this query you can get years when some content was created.
snippet - MySQL Create Database Command Line
3 Jan, 2013
MySQL Create Database Command Line
MySQL
The MySQL command line client provides you with a means of interacting with your MySQL server from the command line. Open terminal window to connect to MySQL server also you may have to do that via...
snippet - Get tables names in PostgreSQL
17 Feb, 2012
Get tables names in PostgreSQL
PostgreSQL
Get tables names in PostgreSQL.
snippet - Get MSSQL column / field information
17 Feb, 2012
Get MSSQL column / field information
MSSQL
Get MSSQL column information. It includes column data type, lenght, name etc...
snippet - Get all tables from database in MSSQL
17 Feb, 2012
Get all tables from database in MSSQL
MSSQL
MSSQL query that gets all database tables.
snippet - Get MySQL tables names in PHP
14 Dec, 2011
Get MySQL tables names in PHP
MySQL
Getting MySQL tables names in PHP.
snippet - Get MySQL column types in PHP
14 Dec, 2011
Get MySQL column types in PHP
MySQL
How to get MySQL columns data of a table in PHP. The data includes name, type, null allowed or not, key, default value and extra for auto_increase and such.
snippet - Clear Data of MySQL Table
24 May, 2011
Clear Data of MySQL Table
MySQL
How to delete all rows of MySQL table. With reseting primary key auto increment and without reseting it.
snippet - MySQL Query Into Array in PHP
8 May, 2011
MySQL Query Into Array in PHP
MySQL
How to turn MySQL query into an array in PHP. Print query results on screen. This is useful for determining contents of a table.
snippet - MySQL Comments in Query
6 May, 2011
MySQL Comments in Query
MySQL
How to write comments in MySQL queries.
snippet - Delete all rows older than x minutes or any time in MySQL
6 May, 2011
Delete all rows older than x minutes or any time in MySQL
MySQL
This query example shows how to delete rows older than x amount of minutes, hours, days, months or even years. This is a part of MySQL date and time functions.