On ubuntu shell,
sudo su postgres
createdb mydb -O dbownername
If it says “dbownername” role does not exist, you can create it with
If above commands does not work, you may be missing following
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
for more information createdb manual createuser manual
You can use the mongo console to copy the database from one host (machine) to another host (machine). It is usually helpful when we are copying large database.
Start the mongo console
- by typing command “mongo”
- run following commands to copy database
For more information Mongo Documentation
to list all databases
to connect to database
to list tables in current database
psql is interactive frontend for PstgreSQL using which you can connect you postgres database and execute commands and SQL. The commands listed above fall under category meta commands.
More information can be found in psql documentation http://www.postgresql.org/docs/9.3/static/app-psql.html
WITH upsert AS (<update-query-here> RETURNING *) <insert-query-here> WHERE NOT EXISTS (SELECT * FROM upsert);
<insert-query-here> Should be of form
“INSERT INTO table_name (column1, column2, …) SELECT ‘column_1_value’, ‘column_2_value’, …”
Hence if update query succeeds, insert query wont execute due to WHERE clause.
Thanks to reference http://www.the-art-of-web.com/sql/upsert/
TABLE_SCHEMA = 'db_name'
AND TABLE_NAME = 'table_name'
AND COLUMN_NAME = 'column_name'
This is only for PHPMyAdmin users.
If you are using the mysql database , phpmyadmin offers lot of features to ease our database management tasks.
One of the task we usually come across is to keep log of schema changes in database table, like new fields addition, existing fields change, etc.
The traditional way how we do that is, we keep a list of sqls in a text file and pass this text file if anybody needs the schema change sqls. Well this is nice way, but
it requires us to make sure this file
1. Contains all the schema change sqls
2. Located to known place and can be easily retrieved. Remember where this file is, and is it correct? (this is for people with bad memory, like me)
Well I think the smarter way would be to use “tracking” feature of “Phpmyadmin”.
1. Click on “tracking” link from main menu of phpmyadmin.
2. It will show us list of untracked tables, click on “track table” link to start tracking the schema changes.
The drawback of this “tracking” is , you will need to start tracking from beginning i.e. when you created the table. This also means that the time you start tracking your tables schema changes will get recorded schema from that time. So, early bird wins here.
Since release 3.3.x a tracking mechanism is available.
For more details https://wiki.phpmyadmin.net/pma/Change_tracking.