Archive for the ‘Databases/SQL’ Category

Databases/SQL — PostgreSQL — get databases

September 22, 2006

In order to get the list of all the available databases in PostgreSQL, connect to any database (e.g., postgres) and run:

SELECT datname FROM pg_database;

Databases/SQL — SQLite — Dump database

August 20, 2006

In order to dump a SQLite database you can use the following command:

sqlite database .dump > database.sql

To put the data into a SQLite database from a sql file use:

sqlite database < database.sql

Databases/SQL — PostgreSQL — get tables and columns

July 29, 2006

In order to get the list of the available tables of a database in PostgreSQL, connect to that particular database and run:

SELECT tablename FROM pg_tables
WHERE tablename NOT LIKE ‘pg\\_%’
AND tablename NOT LIKE ‘sql\\_%’;

In order to get the list of columns for a particular table in that database, run:

SELECT attname FROM pg_attribute, pg_type
WHERE typname = ‘table_name
AND attrelid = typrelid
AND attname NOT IN (‘cmin’, ‘cmax’, ‘ctid’, ‘oid’, ‘tableoid’, ‘xmin’, ‘xmax’);

Databases/SQL — MySQL — create database

July 20, 2006

How to create a database in MySQL:

# mysql -u root -p
mysql> create database db_name;
mysql> grant all privileges on db_name.* to
db_user@”localhost” identified by ‘db_pass‘;