> For the complete documentation index, see [llms.txt](https://wilmerags.gitbook.io/digital-garden/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://wilmerags.gitbook.io/digital-garden/code/databases/sql/postgresql.md).

# Postgresql

## Recipes

**Installing - Ubuntu**

```bash
sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
```

**Installing - Docker**

```bash
docker run --name pg_docker \
    -p 5432:5432 \
    -e POSTGRES_DB=db-name \
    -e POSTGRES_PASSWORD=pass \
    -d postgres
    
# Access from host
psql -h localhost -p 5432 -d db-name -U postgres --password
```

**Execute commands from current user (without user on db)**

```sh
echo "\d" | sudo -u postgres psql
```

**Create database**

```sql
CREATE DATABASE phishiq_db;
```

**Create user**

```sql
CREATE USER user PASSWORD 'pass';
```

**Grant privilage to such user**

```sql
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user;
ALTER ROLE user SUPERUSER;
ALTER ROLE user CREATEDB; 
ALTER ROLE user WITH LOGIN;
```

**Create table**

```sql
CREATE TABLE tbl([col [type]]...)
```

**List db** `\l`

**List users** `\du`

**List tables**

`\dt`

**Change user password**

```SQL
ALTER USER davide WITH PASSWORD 'hu8jmn3';
```

**List constraint for a table**

```SQL
\d+ tablename
```

**Create constraint**

```SQL
ALTER TABLE raw_backup ADD CONSTRAINT raw_backup_pk UNIQUE (ticker, period, date);
```

**Create index**

```SQL
CREATE INDEX idx_date_backup ON raw_backup(date);
```

**Remove constraint**

```SQL
ALTER TABLE affiliations
DROP CONSTRAINT affiliations_organization_id_fkey;
```

**Generating dump file**

```bash
pg_dump -U postgres --schema-only db_name > file.txt
```

**Loading dump file**

```bash
psql -U username dbname < dbexport.pgsql
```

**Kill postgresql session**

```sql
SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
;
```

## Useful tools

* [Postgrest](https://postgrest.org/en/v7.0.0/)
* [Cheatsheet](https://www.postgresqltutorial.com/wp-content/uploads/2018/03/PostgreSQL-Cheat-Sheet.pdf)


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://wilmerags.gitbook.io/digital-garden/code/databases/sql/postgresql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.