gavinmh/psql.md

## psql.md

      
    Raw
  

              psql.md
            
          
    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)

psql -U postgres
Create a user

CREATE USER new_user;
Set the password (via psql)

\password new_user
Create a database owned by a specific user

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)

psql -U username
Connect to a specific database (via shell)

psql database_name
List databases (via psql)

\l
Switch database connection (via psql)

\c database_name
Disconnect / quit

\q
List psql commands (via psql)

\?

Informational commands

All 'informational' commands accept wildcards.
List tables

\dt
\dt *user*
\dt schema_name.*
List views

\dv
\dv schema_name.*
Describe table

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

\dn
Get extended information (add + to any command)

This allows you to see things like comments on tables, columns or other objects.
\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

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.
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.
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.
psql -U postgres my_database
Create extension

CREATE EXTENSION extension_name;

Import / Export

Dump the database (via shell)

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.
psql database_name -f path/to/filename.sql
Run an sql file (via psql)

\i path/to/filename.sql