Postgresql notes

su - postgres #password is:

psql -d crmdatabase -U sqluserid

CREATE DATABASE dbname TEMPLATE template0;

ALTER DATABASE crmdatabase SET geqo TO off;

DROP DATABSE test;


		SELECT
		 *
		FROM
		 pg_catalog.pg_tables
		WHERE
		 schemaname != 'pg_catalog'
		AND schemaname != 'information_schema';

\l or \list list databases

\c test;

\c crmdatabase

create table test3 (i int4, t text, b bytea, t2 text,t3 text);

CREATE TABLE log ( pkey SERIAL PRIMARY KEY,
callNbr int4,
userid varchar(30),
action varchar(100),
created_at timestamptz NOT NULL DEFUALT now());

CREATE SEQUENCE pkey_sequence start 1; incremet 1;

INSERT INTO log (pkey,callNbr,usrid,action,created_at)
VALUES (nextval('pkey_sequence'),1,'Smith','Record added',now());

\du: list available roles

\dp: list access privileges

\password: change the password for user name that follows

\conninfor: Get information about the current database and connections

\dt list tables

SELECT i,t,t2 FROM test3 WHERE i BETWEEN 3 AND 6;

SELECT t3.i, t3.t, t3.t2, t.street, t.phone FROM test3 AS t3, test AS t
WHERE t3.i = t.i;

SELECT l.userid,b.userid
  log3 AS l
  log2 AS b
  WHERE l.userid = b.userid;

---------- output to csv file
   \f ','
   \a
   \o '/home/sqluser/output.csv' - after chmod 777
   SELECT * FROM test3;
   \q
---------- done

create copy of table and data
CREATE TABLE log2 AS SELECT * FROM log;
CREATE TABLE log2 AS TABLE new_table;
SELECT * INTO newtable FROM log2;

CREATE TABLE log
(i1 INT,i2 INT ,userid VARCHAR(30),action VARCHAR(100),
created_at timestamptz NOT NULL DEFAULT now());

\d log <- describe table 'log'

crmdatabase=> INSERT INTO log VALUES (4,2,'Voltair','record added by Voltair',now());

When default tablespace is set to anything but an empty string,
it supplies an implicit TABLESPACE clause for CREATE TABLE and
CREATE INDEX commands that do not have an explicit one.

SELECT i1,i2,userid,action,created_at FROM log3 ORDER BY creted_at;

ALTER DATABASE mydb SET geqo TO off;

CREATE TABLESPACE fastspace LOCATION /ssd1/postgresql/data;

DROP fastspace;

SET default_tablespace = fastspace;

CREATE TABLE foo(i int);

CREATE TABLE foo(i int) TABLESPACE fastspace;

\d pg_database;

postgres=# \d pg_database;

postgres=# \d pg_tablespace

pg_dump -U sqluser crmdatabase > crmdatabase.bak

UPDATE log3 SET action = 'modified' WHRE i1 = 3;

DROP TABLE log2;

DROP TABLE log3; - otherwise it will append all records and duplicate data;

su - postgres

psql crmdatabase - /home/jacek/crmdatabase.bak

UPDATE log3 SET action = 'mod with update', created_at = NOW() WHERE i1 = 10;

SELECT * FROM log3 WHERE userid LIKE '%l%';

SELECT * FROM log3 WHERE created_at::text LIKE '%-10%' ORDER BY created_at;