Resumo dos comandos Postgresql

postgresql-commands

$ dpkg -l | grep -i postgres

$ sudo apt-get install -y python3-dev python3-setuptools postgresql-12 postgresql-contrib-12 pgadmin4 libpq-dev binutils g++

$ sudo su - postgres

$ createdb mydb

$ dropdb mydb # to delete

$ createuser -P myuser

$ psql mydb # to open db

$ GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

\q # to quit

$ cat > person.csv << EOF
    name,age,city_id
    Abel,12,1
    Jose,54,2
    Thiago,15,3
    Veronica,28,1
EOF

$ cat > basics.sql << EOF
CREATE TABLE cities (id SERIAL PRIMARY KEY, city VARCHAR(50), uf VARCHAR(2));
INSERT INTO cities (city, uf) VALUES ('São Paulo', 'SP');
SELECT * FROM cities;
DROP TABLE cities;
EOF

$ psql mydb

mydb=> \i basics.sql

DROP TABLE # to delete table

CREATE TABLE cities (id SERIAL PRIMARY KEY, city VARCHAR(50), uf VARCHAR(2));
CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city_id INT REFERENCES cities(id),
    created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

\dt # to show the tables

\d cities # to show schema of table

DROP TABLE cities
DROP TABLE person

SET timezone = 'America/Sao_Paulo';

SET timezone = 'UTC';

INSERT INTO cities (city, uf) VALUES ('São Paulo', 'SP'),('Salvador', 'BA'),('Curitiba', 'PR');
INSERT INTO person (name, age, city_id) VALUES ('Regis', 35, 1);

$ sudo su - postgres
$ psql mydb
mydb=# COPY person (name,age,city_id) FROM '/home/user/person.csv' DELIMITER ',' CSV HEADER;

SELECT * FROM person ORDER BY name;
SELECT * FROM person INNER JOIN cities ON (person.city_id = cities.id) ORDER BY name;

SELECT cities.city, COUNT(person.city_id) AS persons
FROM cities INNER JOIN person ON cities.id = person.city_id
GROUP BY cities.city;

SELECT * FROM person WHERE city_id <> 1;

UPDATE person SET name = 'Jose da Silva', age = age - 2 WHERE name = 'Jose';

DELETE FROM person WHERE age < 18;


# Herança

$ sudo su - postgres
$ createdb vendas
$ psql vendas

## Problema:

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name TEXT
);
CREATE TABLE seller (
    id SERIAL PRIMARY KEY,
    name TEXT,
    commission DECIMAL(6,2)
);
INSERT INTO person (name) VALUES ('Paulo');
INSERT INTO seller (name,commission) VALUES ('Roberto',149.99);

CREATE VIEW peoples AS
    SELECT name FROM person
        UNION
    SELECT name FROM seller;
 
SELECT * FROM peoples;

## Solução:

DROP VIEW peoples;
DROP TABLE person, seller;

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE seller (
    commission DECIMAL(6,2)
) INHERITS (person);

\d person

\d seller

INSERT INTO person (name) VALUES ('Paulo'),('Fernando');
INSERT INTO seller (name,commission) VALUES
    ('Roberto',149.99),
    ('Rubens',85.01);

vendas=# SELECT name FROM ONLY person;

vendas=# SELECT name FROM seller;

ALTER TABLE person ADD COLUMN email VARCHAR(30);

ALTER TABLE person ALTER COLUMN name TYPE VARCHAR(80);

ALTER TABLE seller ADD COLUMN active BOOLEAN DEFAULT TRUE;
\d seller

UPDATE person SET email = lower(name) || '@example.com';
SELECT * FROM person;

# Backup

pg_dump mydb > bkp.dump
# ou
pg_dump -f bkp.dump mydb

dropdb mydb

createdb mydb; psql mydb < bkp.dump

Post completo em pythonclub.