I use PostgreSQL via the psql client.  If you use a different client (eg. pgAdmin, etc.), I don't know how much will translate over.

One nice difference between psql and mysql (cli) is that if you press CTRL+C, it won't exit the client.

# User administration

## Login as superuser (via shell)

```sh
psql -U postgres
```

## Create a user

```sql
CREATE USER new_user;
```

## Set the password (via psql)

```sql
\password new_user
```

## Create a database owned by a specific user

```sql
CREATE DATABASE database_name WITH OWNER user_name;
```

-----

# Connecting to a database

Note: While the MySQL cli allows you to connect without specifying a database, PostgreSQL does not.  You will not be able to connect to a database that doesn't exist.

If you don't specify a database name (ie. just use the `psql` command), you'll be connected to the database that has the same name as the user you're connecting to.  If you don't specify a user, it will be assumed that your PostgreSQL user has the same name as the user you're logged into the server with.

## Connect as a specific user (via shell)

```sh
psql -U username
```

## Connect to a specific database (via shell)

```sh
psql database_name
```

## List databases (via psql)

```sql
\l
```

## Switch database connection (via psql)

```sql
\c database_name
```

## Disconnect / quit

```sql
\q
```

## List psql commands (via psql)

```sql
\?
```

-----

# Informational commands

All 'informational' commands accept wildcards.

## List tables

```sql
\dt
\dt *user*
\dt schema_name.*
```

## List views

```sql
\dv
\dv schema_name.*
```

## Describe table

Note: unlike MySQL, PostgreSQL will show all of the relationships the specified table has (incoming and outgoing).

```sql
\d table_name
```

## List schemas

```sql
\dn
```

## Get extended information (add `+` to any command)

This allows you to see things like comments on tables, columns or other objects.

```sql
\dt+
\dv+
\d+ table_name
```

-----

# Schemas

PostgreSQL offers schemas as a way to partition tables and other objects.  Every database gets a *public* schema by default that is owned by the superuser that created the database.  This makes it difficult for regular users to drop, so avoid testing in the public schema unless you're planning to drop & recreate the database.

I don't use schemas much outside of testing.

## Create / Drop schema

```sql
CREATE SCHEMA schema_name;
DROP SCHEMA schema_name; -- you'll get an error if it has objects that depend on it
DROP SCHEMA schema_name CASCADE; -- you won't get a warning on this, so use with care
```

## Querying

By default, `search_path` is set to `$user,public`, meaning it will search in the schema named after the user you're logged in as first, then it will search in public.  To query tables in schemas that are not in your current search_path or tables that have been shadowed by a schema with higher precidence, you must specify which schema you mean.

```sql
SELECT * FROM schema_name.table_name;
```

## Changing your current search_path

Note that this is not a permanent command, it only lasts for the duration of the current session.  Making a permanent change for a given user requires updating their account settings.

```sql
SET SEARCH_PATH TO schema1,schema2,public;
```

-----

# Extensions

Note: you must be logged in as a superuser and connected to the database you want the extension added to.

```sh
psql -U postgres my_database
```

## Create extension

```sql
CREATE EXTENSION extension_name;
```

-----

# Import / Export

## Dump the database (via shell)

```sh
pg_dump database_name > dump_file.sql
```

## Run an sql file (via shell)

Do NOT use `psql database_name < path/to/filename.sql`!  If the file you're importing imports additional files, relative paths might not resolve properly, depending on where you run the command from.

```sh
psql database_name -f path/to/filename.sql
```

## Run an sql file (via psql)

```sql
\i path/to/filename.sql
```