su - postgres #password is: |
psql -d crmdatabase -U sqluserid |
CREATE DATABASE dbname TEMPLATE template0; |
ALTER DATABASE crmdatabase SET geqo TO off; |
DROP DATABSE test; |
\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;
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; |
\q |
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; |