Useful PosgreSQL Commands

[nelsonic]

Please add commands which you have found useful...

using psql (the Postgres shell)

Create a new Database:

CREATE DATABASE dbname;

To connect to ("use") a specific database

\c dbname

http://stackoverflow.com/questions/10335561/use-database-name-command-in-postgresql

Show (list of) Tables (for a given database):

\dt

http://stackoverflow.com/questions/769683/show-tables-in-postgresql

[nelsonic]

List Databases:

\list or \l

https://dba.stackexchange.com/a/1288/89486

[ndrean]

You can connect directly to the database "db" using the option -d.

You can also run directly a command using the option -c, and it terminates:

psql -d my_db -c 'select * from my_table;'

Useful : \o and \i

• to save query streams into a file,, start with \o my_file and close it with \o again:

\o my_file
\dt
select * from my_table;
\o

and also use it from the command line;

psql -d my_db -c 'select * from my_table;' -o result.txt

Conversely, you can run commands in a session by reading from a txt file with \i:

\i my_cmds.txt

but also directly read the file with the option -f which will execute what is in the file:

psql -d my_db -f my_cms.txt

Quit!

\q

https://www.postgresql.org/docs/current/app-psql.html

[ndrean]

So from Elixir, you can run the command psql against the server and use the options with System.cmd

For example, you want to add some SQL trigger function. In a migration, you can grab the database_url from the config, and the env PGPASSWORD and parse all this.

Use -v or --set for the option assignment (eg --set on_error_stop=on as a command option).
It is equivalent in a psql session to:

\set var 5;
select * from my_table where id = :var;

Example:

use Ecto.Migration

def up do
  db_url = Application.fetch_env!(:app, App.Repo)[:url])
  pg_pwd = System.fetch_env!("PGPASSWORD")

  %URI{
      userinfo: unserinfo,
      host: db_host,
      port: port,
      path: path
   } = URI.parse(db_url)

  [db_user, db_password] = String.split(unserinfo, ":")
  db_name = String.trim_leading(path, "/")

  sql_path = "./my_trigger.sql"

  args = ~w(-h #{db_host} -d  #{db_name} -p #{port} -U #{db_user} -f #{sql_path} --set ON_ERROR_STOP=ON)

  case System.cmd("psql",  args, env: [{PGPASSWORD: pg_pwd}]) do
    { _, 0} -> :ok
    {err, _}  -> {:error, err}
  end
end

This is for testing. It works, but for production ready, not sure:
https://stackoverflow.com/questions/52743033/how-to-suspend-postgresqls-on-error-stop-for-just-part-of-a-psql-script