-
Notifications
You must be signed in to change notification settings - Fork 1
CLI
GradedJestRisk edited this page Apr 27, 2024
·
3 revisions
Reminder:
- every not-escaped command should end with a semicolon (;) !!
- on
<code>
{=html} ERROR: current transaction is aborted, commands ignored until end of transaction block, you should rollback
All settings (otherwise stated), go to your personal settings file
~/.psqlrc
https://stackoverflow.com/questions/6523019/postgresql-scripting-psql-execution-with-password
Several options:
- shortest:
psql postgres://<USER_NAME>@<HOST_NAME>:<PORT_NUMBER>/<DATABASE_NAME>
- short:
psql -h <HOST_NAME> -p >PORT_NUMBER> -U <USER_NAME> -d <DATABASE_NAME>
- human:
psql --host <HOST_NAME> --port <PORT_NUMBER> -user <USER_NAME> -dbname <DATABASE_NAME>
You can shorten using an environment variable
export CONNECTION_STRING="host=<HOST_NAME> port=<PORT_NUMBER> dbname=<DATABASE_NAME> user=<USERNAME> password=<PASSWORD>";
psql --dbname $CONNECTION_STRING
Before
export PGHOST=localhost
export PGPORT=5433
export PGUSER=john
export PGPASSWORD=password123
export PGDATABASE=dummy
After
unset PGHOST
unset PGPORT
unset PGUSER
unset PGPASSWORD
unset PGDATABASE
touch ~/.pgpass
echo localhost:5432:example:postgres:password123 >> ~/.pgpass
- list database and exit: -l (--list)
- SQL script debug (ask before executing each statement): -s (--single-step)
- execute SQL command from argument:
-command <SQL_QUERY>
- execute SQL command from file:
--file <FILE>
- log query output in file:
--output
- display only error message:
--quiet
List:
- quit:
\q
- list databases:
\d
- connect:
\c <DATABASE_NAME>
orCONNECT <DATABASE_NAME>
- help:
- for non-SQL command
\?
will be your guide, - for SQL command
\help
- for non-SQL command
- get version
SELECT version();
- change current user password
\password
- show procedure/function source (read-only):
\ef <PROCEDURE_NAME>
Simple
psql postgresql://postgres@localhost:5432/pix --variable user_count=10
select * from generate_series(1,:user_count);
generate_series
-----------------
1
(..)
9
10
(10 rows)
Using bash environment
foo=id
echo $foo
psql postgresql://postgres@localhost:5432/pix --variable foo=$foo
CREATE TABLE test(id int);
INSERT INTO test VALUES (1);
SELECT * FROM test WHERE :foo=1;
id
----
1
(1 row)
Should be easier? I think so Doc
To display only messages whose level is ERROR
PGOPTIONS='--client-min-messages=error' psql --quiet
List
- connect to database
psql -U <ROLE_NAME> -d <DB_NAME> -h <HOST_NAME>
- check the connection
\conninfo
- create a table (see below)
- insert some data (see below)
- query it
SELECT * FROM playground;
- query it
SELECT * FROM playground WHERE color='blue';
Sample table creation statement
CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);
Sample table data
INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');
Install
sudo pip install pgcli
Test
pgcli postgresql://user@password:port/database
Feature:
- autocomplete
- template (named queries)
Template:
- list: n (named queries)
\n
- define: s (save)
\ns TEMPLATE_NAME QUERY
- use:
\n TEMPLATE_NAME PARAMS
- delete: d