Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Resumo dos comandos Postgresql
$ 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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment