mysql cache SQL_NO_CACHE

enable query cache (maybe default depends of the server configuration)

SELECT SQL_CACHE SUM(points) FROM account;

turn off mysql query cach

SELECT SQL_NO_CACHE SUM(points) FROM account;

the mysql query cache

Bucardo – Asynchronous PostgreSQL Replication System

Bucardo is an asynchronous PostgreSQL replication system, allowing for both multi-master and multi-slave operations. It was developed at Backcountry.com by Jon Jensen and Greg Sabino Mullane of End Point Corporation, and is now in use at many other organizations.

bucardo

postgresql version of mysql UNIX_TIMESTAMP()

MySQL

mysql> SELECT UNIX_TIMESTAMP();
mysql> SELECT UNIX_TIMESTAMP('1973-11-29 21:33:09');

PostgreSQL

postgres=> SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0));
postgres=> SELECT EXTRACT(EPOCH FROM TIMESTAMP '1973-11-29 21:33:09');

MySQL from_unixtime and unix_timestamp

postgresql add on delete cascade to foreign key


ERROR: update or delete on table "page" violates foreign key constraint "info_page_id_fkey" on table "info" DETAIL: Key (page_id)=(106) is still referenced from table "info"

you have to drop cascade and recreating the new one.

psql> \d info

psql> alter table info drop constraint info_page_id_fkey;
psql> alter table info add constraint "info_page_id_fkey" foreign key(page_id) references page on delete cascade;

postgresql complete backup

run command under user postgres

to backup

$pg_dumpall > db.sql

to restore

$psql -f db.sql template1

extract date and time from timestamp column on postgresql

to extract date from coldate,

SELECT date(coldate) FROM mytable;


to extract time, doing time(coldate) will give an error instead,

SELECT "time"(coldate) FROM mytable


another way

SELECT coldate::time FROM mytable

postgresql date/time doc
extract date from timestamp

upgrade postgresql-8.1 to postgresql-8.3 in debian etch

postgresql-8.3 available for etch on backports repositories. Add to /etc/apt/sources.list

deb http://www.backports.org/debian etch-backports main contrib non-free

then install postgresql-8.3

#apt-get -t etch-backports install postgresql-8.3
#pg_dropcluster --stop 8.3 main
#pg_upgradecluster 8.1 main
#pg_dropcluster --stop 8.1 main
#apt-get remove postgresql-8.1


read more

inverting value in mysql table

It is possible to invert the value in mysql table without knowing the present value. This working on mysql and may not work on postgresql.


UPDATE table_name SET column = NOT column


display new icon based on db timestamp

this works in mysql5+. we want to display a small icon if the post_date is less than 2 days old. Unix timestamp is second. so for 2 days

60 * 60 * 24 * 2 = 172800

SQL query

SELECT IF((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(post_date)) < 172800, 1, 0) AS is_new FROM tbl_news

then the php code will be

$p = mysqli_fecth_object($sql)
$p->is_new ? echo '<img src="new.gif">' : '';


mysql prepared statement

Prepared statement is one good way to avoid sql injection.

mysql> PREPARE stmt_name FROM "SELECT name FROM Country WHERE code = ?";
Query OK, 0 rows affected (0.09 sec)
Statement prepared

mysql> SET @test_parm = "FIN";
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt_name USING @test_parm;
+---------+
| name |
+---------+
| Finland |
+---------+
1 row in set (0.03 sec)

mysql> DEALLOCATE PREPARE stmt_name;
Query OK, 0 rows affected (0.00 sec)


More