Thursday, 7 January 2016

psqlrc (psql startup file) in Windows

We can have startup script in Windows and Linux when running psql (Postgresql command line interface). By using Postgresql documentation is obvious for Linux but for Windows it is not.
In windows there is cryptic notes %APPDATA%\postgresql\psqlrc.conf.

So let's explore what does it mean.
APPDATA is environment variable for Windows OS and we can get it very easily.
We open command line (CMD) and type following: echo %APPDATA%.
In Windows 7 some like this will be visible on the screen:
c:> cd C:\Users\User\AppData\Roaming (User is active user)

So when we combine the appdata directory and postgresql we get following:

So if we want to go to that directory we need to enter following command:
cd C:\Users\User\AppData\Roaming\postgresql

We can also go to that diretory if we write in the command line:
C:> CD %APPDATA%\postgresql

The latter way is much easier and we don't need to think about name of the user.

So now when we are in the folder and we try to list the files we notice that there is not psqlrc.conf file. Does it mean that we are in the wrong folder, not. We need to create a file. I recommend any text editor will do the job, not MS Word or LibreOffice Writer. Text editor like Notepad will do the job. 

So now that when we configured the setup file how we can use it or not to use it.
If we want to use it we run the usual syntax for runnig psql program psql -U postgres 
Or if we don't want to use configuration file we need to use -X when invoking command
so it looks like this: psql -X -U postgres

Usually we don't need password for postgres (configured in pgpass.conf). I'll write about it in some another blogpost.

There are great blogs about psql online, some research will do.
Some useful pages for customization:
psql official documentation
psqlrc psql startup file for postgres
psqlrc file for dbas
how to customize the postgresql prompt with psqlrc on ubuntu 14.04

My main motivation for configuring psqlc was setting null automatically and changing default editor to vim, so I have everything in the console.
Since I am Vim user this helps me to practice server like environment. That file is temporary and it last while session lasts. If you want normal file run: 
#\! vim perm.sql  and perm.sql file will be created in the directory where you started psql

Example of my psqlrc.conf:

\set QUIET 1

\x auto

\pset border 2
\pset null <NULL>
\setenv editor 'c:\\Program Files\\Vim\\vim74\\vim.exe'
\set VERBOSITY verbose

\set QUIET 0

\echo 'Welcome to PostgreSQL! \n'
\echo 'Type :version to see PostgreSQL version \n'
\echo 'Type :extensions to see the available extensions'

\set version 'SELECT version();'
\set extensions 'select * from pg_available_extensions;'

1 comment:

  1. Excellent article was really struggling to find the file..