If you need to connect to a remote server, Ubuntu 16.04 users can install the PostgreSQL client with:
sudo apt install postgresql-client-common postgresql-client-9.5
Enter the CLI as Master/Root User
postgres is the master user, (and comes with their own database by default).
sudo -u postgres psql
Enter the CLI Utility As Normal User
psql \ --user $USER \ --password \ -d $DATABASE \ --host $HOST
- If the user has their own database and you wish to connect to that, you can skip
-D $DATABASEas you would automatically connect to that.
- if you wish to connect to localhost, you can skip
- If the user doesnt need a password, you can skip
- If your current BASH user is the same name as the user in psql you wish to connect as, you can skip
Exiting the CLI Utility
Technically this command does not "switch" database, you are just closing one connection and opening another.
Double quotes (
") are used to denote column names, and single quotes (
') are also only used to denote values. When working with UUIDs, its best to always wrap in single quotes.
UPDATE my_table SET "my_column" = 'f68b8ef1-ed46-42bd-9619-4ae37dae3eb3' WHERE "uuid"='97c681fc-d217-4bcb-970d-e54793d8fd94';
CREATE USER programster WITH PASSWORD 'thisismypassword';
programster database, and they will have the ability to see and create tables in the other databases.
Create User With New Role
CREATE ROLE $MY_USER with password '$USER_PASSWORD' login;
login if you don't want that to be loginable.
Grant User Access To Database
GRANT ALL ON database $DATABASE_NAME TO $ROLE_OR_USER;
Check Role Privileges
Change User Password
sudo -u postgres psql postgres
Create A Database
CREATE DATABASE my_database_name ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE template0;
If you don't need utf8 and are fine with LATIN encoding, then you can just use:
CREATE DATABASE my_database_name;
DROP DATABASE my_database_name;
Create a table
CREATE TABLE broker ( id serial NOT NULL, name varchar(255) NOT NULL, redirect_url text NOT NULL, secret varchar(30) NOT NULL, modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) )
AUTO_INCREMENT but a special
Creating Tables With Foreign Keys
CREATE TABLE cities ( city varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references cities(city), temp_lo int );
By default, foreign keys will be set to use
NO ACTION for both update and delete actions (resulting in the same behavioiur as
RESTRICT). You can manually specify what you want like so:
CREATE TABLE weather ( city varchar(80) references cities(city) ON UPDATE CASCADE ON DELETE RESTRICT, temp_lo int );
Creating Tables With Enums
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); CREATE TABLE person ( name text, current_mood mood );
Creating Tables With Unique Column
CREATE TABLE users ( name varchar(255) NOT NULL, username varchar(255) UNIQUE NOT NULL );
Creating Combined Unique Column
CREATE TABLE myTable ( id uuid PRIMARY KEY, col1 INT NOT NULL, col2 INT NOT NULL, UNIQUE (col1, col2) )
Creating Tables With Indexes
You cannot specify an
INDEX in the create table definition. However, if you specify a column as unique, then an index is automatically created for that column.
If you have a column that is not unique but also needs to be indexed for quick selections, then you can add an index after the table has been created with:
CREATE INDEX on tableName ("column_name");
If you are using the psql cli tool:
If you are connecting using something like PHP, then you need to do:
SELECT tablename FROM pg_catalog.pg_tables
If you want to show a table, use:
Table "public.users" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------+------------------------+-----------+----------+---------+----------+--------------+------------- name | character varying(255) | | not null | | extended | | username | character varying(255) | | not null | | extended | | Indexes: "users_username_key" UNIQUE CONSTRAINT, btree (username)
... or you can use the following to get just the basic information:
Table "public.users" Column | Type | Collation | Nullable | Default ----------+------------------------+-----------+----------+--------- name | character varying(255) | | not null | username | character varying(255) | | not null | Indexes: "users_username_key" UNIQUE CONSTRAINT, btree (username)
SHOW CREATE TABLE tablename.
CREATE TABLE cities ( city varchar(80) primary key, location point ); INSERT INTO cities (city, location) VALUES ('Houston', '29.7604, -95.3698'), ('Dallas', '32.7767, -96.7970') ;
UPDATE cities SET city='Washington' WHERE city='Houston';
DELETE FROM cities WHERE city='Houston';
ALTER TABLE my_table ADD COLUMN "new_column" varchar NOT NULL;
Drop/Remove Type (Enum)
If you want to remove a type (such as an Enum you created), then you would do it like so:
DROP TYPE my_type;
ALTER TABLE my_table RENAME COLUMN "original_name" TO "new_column_name";
If you want to remove a constraint, such as an index or a foreign key, then you would do it like so:
ALTER TABLE my_table DROP CONSTRAINT "my_constraint_name";
Working With JSON
If you use the JSON data type for a column, there are special operations you can do with it.
For example, I created a
server_dump column that holds the
json_encode of the PHP $_SERVER superglobal and can then select the requestor's IP address from this with:
SELECT server_dump->'REMOTE_ADDR' FROM api_requests;
Show Running Queries
SELECT * FROM pg_stat_activity;
Ouput of Nulls
Unlike MySQL which will show "null" as a value when showing data, like so:
mysql> select * from cities; +---------+----------+ | city | location | +---------+----------+ | Houston | NULL | +---------+----------+ 1 row in set (0.00 sec)
...PostgreSQL will just show emptiness like so:
postgres=# select * from cities; city | location ------------+-------------------- Dallas | (32.7767,-96.797) Washington | (29.7604,-95.3698) London | (3 rows)
Scrolling Results - Turn Off Paging
If there are a lot of results, PostgreSQL will implement scrolling by default. To scroll down through the results, one presses
spacebar. However, if like me, you find this annoying, you can turn off paging with:
\pset pager off
pg_dump \ --host $HOST \ --port "5432" \ --username $USERNAME \ --file /path/to/dump/file.sql \ $DB_NAME
To restore a dump taken using the way we created a pg_dump, you would just run the SQL file like so:
psql \ --host $HOST \ --port "5432" \ --username $USERNAME \ -d $DATABASE \ -f $FILEPATH
pg_restore command, but it wouldn't work on a plain SQL dump like we created.
Show Settings / Variables
If you want to see all the settings for postgresql, then run:
If you want to see a specific setting then specify it instead of all, like:
No Wrap Output
If you have lots of columns in your tables and you don't want word wrapping in your output, you can run the following command inside PostgreSQL to use
less as your pager, and tell it not to wrap lines.
\setenv PAGER 'less -S'
If you are using a PostgreSQL docker container it won't have less installed, so you would need to install
less, and then set the environment variable like so:
Getting Table Stats
The command below is a good way to get some stats about your tables. It can be quite useful when you want to see how "active" your tables are, or see how many "dead tuples" are taking up space and need deleting by the vacuum process. (Dead tuples are rows that have been deleted but have not been removed from disk yet by the vacuum process).
SELECT relname as table_name, n_tup_ins as "inserts", n_tup_upd as "updates", n_tup_del as "deletes", n_live_tup as "live_tuples", n_dead_tup as "dead_tuples", last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count FROM pg_stat_user_tables ORDER BY table_name;
Converting Unix Timestamp Integer to Timestamp
SELECT to_timestamp(my_unix_timestamp_column) as my_timestamp FROM my_table;
To convert that to a date (such as to group by a day of the year) wrap it again like so:
SELECT DATE(to_timestamp(my_unix_timestamp_column)) as my_date FROM my_table;
- Stack Overflow - In Postgresql, Force unique on combination of two columns
- postgresqltutorial.com - PostgreSQL Foreign Key
- Stack Overflow - Show tables in PostgreSQL
- Why unsigned integer is not available in PostgreSQL?
- How to configure postgresql for the first time?
- Stack Overflow - How do you create a read-only user in PostgreSQL?
- Stack Overflow - Amazon RDS PostgreSQL: how to create user?
- Stack Overflow - Check Postgres access for a user
- PostgreSQL Docs - Enumerated Types
- PostgreSQL: Can you create an index in the CREATE TABLE definition?
- Stack Overflow - How to be able to vertically scroll terminal to see all data from a large table
- PostgreSQLDocs - JSON Functions and Operators
- Stack Overflow - input file appears to be a text format dump. Please use psql
- Ask Ubuntu - Set PostgreSQL pager to less?
First published: 16th August 2018