Set the Current Timestamp as the Default Value of A Timestamp Column in A Table with Laravel Migrations

How to Set the current timestamp as the default value of a timestamp column in a table with Laravel migrations? Let’s find out:

SQL Statement

Use DEFAULT CURRENT_TIMESTAMP.

CREATE TABLE `products` (
         :
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
         :
) 

Laravel Migrations

Just use a raw expression, DB::raw() to set CURRENT_TIMESTAMP as a default value for a column:

$table->timestamp('created_at')
      ->default(DB::raw('CURRENT_TIMESTAMP'));

This works for every database driver.

Reset AUTO_INCREMENT in MySQL

When creating a new record in MySQL server with a primary key, the “id” field (the primary key) is set to be auto incremented. Meaning the value of the primary key (“id” field) starts from 1, and the value of the primary key of the next record is increased by 1, which is 2. MySQL server takes cares of it without user interference.

What if the records have been deleted, and the primary key has to be reset to 1?

We can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1

The primary key is reset to 1.

RENAME DATABASE name in MySQL

1. “RENAME” command

Syntax：

RENAME DATABASE db_name TO new_db_name

There is the “rename database” command for the early MySQL version, but it was removed after version 5.1.23. It was because the possibility of data lost when using it.

p.s. There shows an error when using "“rename database”:

mysql> rename database shop to mini_shop;  
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use 
near 'database shop to mini_shop' at line 1

2. Create a new database, rename all tables of old database to new one. Then drop the old database.

CREATE DATABASE new_db_name;  
RENAME TABLE db_name.table1 TO new_db_name.table1,  
db_name.table2 TO new_db_name.table2;  
DROP DATABASE db_name;

SQL Statement with Single or Double Quotes for PostgerSQL ?

Single Quotes or Double Quotes? That is a Question!

Consider a sql statement:

SELECT model AS 'Model Name' FROM orders

We usually write it in the format below in Ruby on Rails:

query_string = "SELECT model AS 'Model Name' FROM orders"

It works fine for SQLite and MySQL, but fails for PostgreSQL!

The root cause is that PostgreSQL only accepts double-quoted key words!

Solution: use double-quotes instead of single-quotes:

SELECT model AS "Model Name" FROM orders

In Rails, it should be:

query_string = 'SELECT model AS "Model Name" FROM orders'

Note that it turns to be single quotes for wrapping the whole query string.