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.
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';
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;
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.
- 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
First published: 16th August 2018