My Postgresql snippets

Install psql

To get the psql, I simply installed the postgresapp for OSX which bundles the psql . We can start the psql from its GUI, or added its bin to our PATH. The specific path is: /Applications/Postgres.app/Contents/Versions/latest/bin

You can also get psql by installing postgresql one click installer.

Connect to DB with psql

psql -h DbUrl -p DbPort -U DbUser

typically a password prompt comes up(if not add -W to force it).

skip password

If we need to avoid entering pw interactively, we could set the pw as PGPASSWORD in the PATH so that psql could pickup.

export PGPASSWORD='password'

For all other environment variables, reference here.

run batch sql file

append -a -f YourScript.sql to the above command.

-a will print all nonempty input lines to standard output as they are read.

After the file is processed, psql terminates.

Or in the psql shell, \i path_to_sql_file

quit psql shell

use \quite

meta sqls

All DBs: select datname from pg_database;

All Tables: select tablename from pg_table where tableowner='YOUROWNER';

describe table: select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'YourTableName';
Or use  \d schemaName.tableName in psql shell.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s