A cramped elephant at the first glance
Table of Contents
Intro
From the official documentation:
PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES, Version 4.2, developed at the University of California at Berkeley Computer Science Department. POSTGRES pioneered many concepts that only became available in some commercial database systems much later.
Where object-relational database management system:
An object-relational database (ORD), or object-relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data-types and methods.
And several penetratingly accurate remarks from Jayadevan Maymala:
PostgreSQL is an incredibly flexible and dependable open source relational database. Harnessing its power will make your applications more reliable and extensible without increasing costs. Using PostgreSQL's advanced features will save you work and increase performance, once you've discovered how to set it up.
Installation
Debian has a straightforward policy in OS administration: all
secure or unsecure operations are prohibited for ordinary user and
allowed for root only. Thus all Postgres post-installation (it's
a pun, but it is :) procedures should be accomplished as root.
# apt-get install postgresql-doc postgresql postgresql-client # apt-get install pgadmin3 pgadmin3-data
First Steps
Init DB and customary user
Exactly for security purpose we'll allow any OS user to connect
with Postgres server locally without password. We trust our
logged users :)
First of all log-in as root and:
~# su - postgres
~$ psql
postgres=# \du //check-up already existing users
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
// create a new one:
postgres=# CREATE USER <user_name> CREATEDB;
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
<user_name>| Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Now Postgres knows about <user_name>. It's time to log-in as a
<user_name>, and create DB with <user_name>:
~$ createdb
After these preparations it would feasible to connect to Postgres
server from localhost for log-in users without a specific password:
~$ psql psql (9.6.4) Type "help" for help. user_name=>
Emacs re-configuring
Now, when you're able to connect your Postgres client with
server it is time to tune in your Emacs on SQL verse. There are
several comprehensive tutorials below:
- Brief intro into SQLmode;
- Must-read sample for Org-mode in scientific research;
- SQL in Babel introduction
After then you're red these articles it might be not difficult to:
- Activate
SQL modein yourinit.el; - Restart
Emacs
After these manipulations feel free to open an embed SQL mode
documentation: M-s sql-help
Interactive mode
It's seems a standard way to do the business with Postgres
inside Emacs.
Just type M-x sql-postgres and three times <RETURN> as an
agreement to default connection options.
Figure 1: Postgres client's interactive window
Postgres Testing as a Literate Programming
Table creation
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);
CREATE TABLE CREATE TABLE
Check existing tables
\d
List of relations Schema Name Type Owner public cities table alioth public weather table alioth
Populating tables with data
-- Direct and blind approach
INSERT INTO weather VALUES
('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
-- Direct and straightforward method
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1
Retrieving data
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
city temp_lo temp_hi prcp date San Francisco 46 50 0.25 1994-11-27 San Francisco 43 57 0 1994-11-29 Hayward 37 54 1994-11-29
Tables deletion
DROP TABLE weather; DROP TABLE cities;
DROP TABLE DROP TABLE
Check existing tables
\d
No relations found.
blog comments powered by Disqus