<commit_before>---
layout: page
title: Postgresql
permalink: /postgresql/
---

## Basic Commands

### Login to postgresql

```bash
psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W
psql -U myuser -h myhost "dbname=mydb sslmode=require" # ssl connection
```

### Default Admin Login
sudo -u postgres psql -U postgres
sudo -u postgres psql

### List databases on postgresql server

```
psql -l [-U myuser] [-W]
```

### Turn off line pager pagination in psql:
\pset pager


### Determine system tables

```
select * from pg_tables where tableowner = 'postgres';
```

### List databases from within a pg shell

```
\l
```

### List databases from UNIX command prompt

```
psql -U postgres -l
```

### Describe a table

```
\d tablename
```
### Quit psql

```
\q
```

### Switch postgres database within admin login shell

```
\connect databasename
```

### Reset a user password as admin
```
alter user usertochange with password '<PASSWORD>';
```

### Show all tables
```
\dt
```

### List all Schemas
```
\dn
```

### List all users
```
\du
```

### Load data into postgresql

```bash
psql -W -U username -H hostname < file.sql
```

### Dump (Backup) Data into file

```
pg_dump -W -U username -h hostname database_name > file.sql
```

### Increment a sequence
```sql
SELECT nextval('my_id_seq');
```

### Create new user
```sql
CREATE USER lemmy WITH PASSWORD '<PASSWORD>';
# or

sudo -u postgres createuser lemmy -W
```

### Change user password
```sql
ALTER USER Postgres WITH PASSWORD '<PASSWORD>';
```

### Grant user createdb privilege
```sql<commit_msg>postgresql -- formatting update<commit_after>---
layout: page
title: Postgresql
permalink: /postgresql/
---

## Basic Commands

### Login to postgresql
```bash
psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W
psql -U myuser -h myhost "dbname=mydb sslmode=require" # ssl connection
```

### Default Admin Login
```bash
sudo -u postgres psql -U postgres
sudo -u postgres psql
```

### List databases on postgresql server
```
psql -l [-U myuser] [-W]
```

### Turn off line pager pagination in psql:
```
\pset pager
```

### Determine system tables
```
select * from pg_tables where tableowner = 'postgres';
```

### List databases from within a pg shell
```
\l
```

### List databases from UNIX command prompt
```
psql -U postgres -l
```

### Describe a table
```
\d tablename
```
### Quit psql
```
\q
```

### Switch postgres database within admin login shell
```
\connect databasename
```

### Reset a user password as admin
```
alter user usertochange with password '<PASSWORD>';
```

### Show all tables
```
\dt
```

### List all Schemas
```
\dn
```

### List all users
```
\du
```

### Load data into postgresql

```bash
psql -W -U username -H hostname < file.sql
```

### Dump (Backup) Data into file
```bash
pg_dump -W -U username -h hostname database_name > file.sql
```

### Increment a sequence
```sql
SELECT nextval('my_id_seq');
```

### Create new user
```sql
CREATE USER lemmy WITH PASSWORD '<PASSWORD>';
# or

sudo -u postgres createuser lemmy -W
```

### Change user password
```sql
ALTER USER Postgres WITH PASSWORD '<PASSWORD>';
```

### Grant user createdb privilege
```sql
{'fraction_non_alphanumeric': 0.1657214870825457, 'fraction_numerical': 0.0, 'mean_word_length': 3.8106060606060606, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4762263', 'n_tokens_mistral': 1181, 'n_tokens_neox': 1092, 'n_words': 401}