This is an essential primer for basic postgres sql operations. This will come in handy if your project uses postgres. With web2py we do not need to know most stuff as DAL takes care of most of the things in the background for us.
Postgres SQL Primer – The Basics
The file pg_hba.conf governs the basic constraints underlying connection to PostgreSQL.
Start postgres
su – postgres & then
psql postgres or
Logging in as superuser – This one works for me on Debian 9
sudo -u postgres psql
Quit postgres
\q
Switch database OR Connect to a database
\c <dbname> OR \connect postgres; (where postgres is database name)
Delete
delete from fotoz where id in (66, 97, 89,77);
Contains
SELECT id FROM fotoz WHERE caption LIKE ‘%robin%’;
Creatge database
CREATE DATABASE lusiadas;
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
create database bugbase;
create database pixy;
create database hms;
Check tables
SELECT * FROM pg_catalog.pg_tables;
Or \dt
List databases
\l or
\list if you have logged in as
Checking index on a table
\d table_name
\di is the “small brother” of the \d command which will list all relations of the current database. Thus \di certainly stand for “show me this databases indexes”.
Typing \diS will list all indexes used systemwide, which means you get all the pg_catalog indexes as well.
Checking the Installation
To check that the PostgreSQL server was correctly installed and is running, you can use the command ps:
# ps -ef | grep postgre
You should see something like this on the terminal:
postgres 32164 1 0 21:58 ? 00:00:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/ postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf
postgres 32166 32164 0 21:58 ? 00:00:00 postgres: checkpointer process
postgres 32167 32164 0 21:58 ? 00:00:00 postgres: writer process
postgres 32168 32164 0 21:58 ? 00:00:00 postgres: wal writer process
postgres 32169 32164 0 21:58 ? 00:00:00 postgres: autovacuum launcher process
postgres 32170 32164 0 21:58 ? 00:00:00 postgres: stats collector process
Success! PostgreSQL has been successfully installed and is running.
Updating a null value field with a string
update fotoz set imgthumb=’45.thumbnail.jpg’ WHERE imgthumb IS NULL;
pixy=# update fotoz set uploaded_by=’Admin’ WHERE uploaded_by IS NULL;
UPDATE 38
update fotoz set market_sell=’T’ where market_sell IS NULL;
update fotoz set market_sell=’F’ where id>30;
Accessing the PostgreSQL Database
On Debian, PostgreSQL is installed with a default user and default database both called postgres. To connect to the database, first you need to switch to the postgres user by issuing the following command while logged in as root (this will not work with sudo access):
su – postgres
You now should be logged as postgres. To start the PostgreSQL console, type psql:
psql
Done! You should be logged on the PostgreSQL console. You should see the following prompt:
psql (9.4.2)
Type “help” for help.
postgres=#
To exit the psql console just use the command \q.
Creating New Roles
By default, Postgres uses a concept called “roles” to aid in authentication and authorization. These are, in some ways, similar to regular Unix-style accounts, but PostgreSQL does not distinguish between users and groups and instead prefers the more flexible term “role”.
Upon installation PostgreSQL is set up to use “ident” authentication, meaning that it associates PostgreSQL roles with a matching Unix/Linux system account. If a PostgreSQL role exists, it can be signed in by logging into the associated Linux system account.
The installation procedure created a user account called postgres that is associated with the default
Altering Table column data type
For example here we modify a table column from VARCHAR to INTEGER
ALTER TABLE news ALTER COLUMN news_views TYPE INT USING news_views::INTEGER;
Postgres role
To create additional roles we can use the createuser command. Mind that this command should be issued as the user postgres, not inside the PostgreSQL console:
createuser –interactive
This basically is an interactive shell script that calls the correct PostgreSQL commands to create a user to your specifications. It will ask you some questions: the name of the role, whether it should be a superuser, if the role should be able to create new databases, and if the role will be able to create new roles. The man page has more information:
man createuser
Creating a New Database
PostgreSQL is set up by default with authenticating roles that are requested by matching system accounts. (You can get more information about this at postgresql.org). It also comes with the assumption that a matching database will exist for the role to connect to. So if I have a user called test1, that role will attempt to connect to a database called test1 by default.
You can create the appropriate database by simply calling this command as the postgres user:
createdb test1
The new database test1 now is created.
Connecting to PostgreSQL with the New User
Let’s assume that you have a Linux account named test1, created a PostgreSQL test1 role to match it, and created the database test1. To change the user account in Linux to test1:
su – test1
Then, connect to the test1 database as the test1 PostgreSQL role using the command:
psql
Now you should see the PostgreSQL prompt with the newly created user test1 instead of postgres.
Now that you know how to connect to the PostgreSQL database system, we will start to go over how to complete some basic tasks.
First, let’s create a table to store some data. Let’s create a table that describes playground equipment.
The basic syntax for this command is something like this:
CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);
As you can see, we give the table a name, and then define the columns that we want, as well as the column type and the max length of the field data. We can also optionally add table constraints for each column.
You can learn more about how to create and manage tables in Postgres in the How To Create, Remove, & Manage Tables in PostgreSQL on a Cloud Server article.
For our purposes, we’re going to create a simple table like this:
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
);
We have made a playground table that inventories the equipment that we have. This starts with an equipment ID, which is of the serial type. This data type is an auto-incrementing integer. We have given this column the constraint of primary key which means that the values must be unique and not null.
For two of our columns, we have not given a field length. This is because some column types don’t require a set length because the length is implied by the type.
We then give columns for the equipment type and color, each of which cannot be empty. We then create a location column and create a constraint that requires the value to be one of eight possible values. The last column is a date column that records the date that we installed the equipment.
To see the tables, use the command \dt on the psql prompt. The result would be similar to
List of relations
Schema | Name | Type | Owner
——–+————+——-+———-
public | playground | table | postgres
As you can see, we have our playground table.
Adding, Querying, and Deleting Data in a Table
Now that we have a table created, we can insert some data into it.
Let’s add a slide and a swing. We do this by calling the table we’re wanting to add to, naming the columns and then providing data for each column. Our slide and swing could be added like this:
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’);
You should notice a few things. First, keep in mind that the column names should not be quoted, but the column values that you’re entering do need quotes.
Another thing to keep in mind is that we do not enter a value for the equip_id column. This is because this is auto-generated whenever a new row in the table is created.
We can then get back the information we’ve added by typing:
SELECT * FROM playground;
The output should be
equip_id | type | color | location | install_date
———-+——-+——–+———–+————–
1 | slide | blue | south | 2014-04-28
2 | swing | yellow | northwest | 2010-08-16
Here, you can see that our equip_id has been filled in successfully and that all of our other data has been organized correctly. If our slide breaks, and we remove it from the playground, we can also remove the row from our table by typing:
DELETE FROM playground WHERE type = ‘slide’;
If we query our table again:
SELECT * FROM playground;
We will see our slide is no longer a part of the table:
equip_id | type | color | location | install_date
———-+——-+——-+———-+————–
1 | slide | blue | south | 2014-04-28
Useful Postgres Commands
Here are a few commands that can help you get an idea of your current environment:
\?: Get a full list of psql commands, including those not listed here.
\h: Get help on SQL commands. You can follow this with a specific command to get help with the syntax.
\q: Quit the psql program and exit to the Linux prompt.
\d: List available tables, views, and sequences in current database.
\du: List available roles
\dp: List access privileges
\dt: List tables
\l: List databases
\c: Connect to a different database. Follow this by the database name.
\password: Change the password for the username that follows.
\conninfo: Get information about the current database and connection.
With these commands you should be able to navigate the PostgreSQL databases, tables, and roles in no time.
Rename a postgres database
First check if there are no active connections to the existing database with command –
SELECT * FROM pg_stat_activity WHERE datname = ‘db’;
You may find that the database, which you want to rename, has many active connections. In this case, you need to inform the respective users as well as the application owners before terminating the connections to avoid data loss.
Then, terminate all the connections to the db database using the following statement:
| SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = ‘db’; |
After that, rename the db database to newdb using the ALTER DATABASE RENAME TO statement as follows:
- Log in to your server using SSH.
- At the command prompt, type the following command to connect to PostgreSQL. Replace dbname with the name of a database that is not the database you want to rename, and replace username with a PostgreSQL username that has access privileges to the database:
psql dbname username
You cannot rename a database while you are currently connected to it.
- To rename the database, type the following command. Replace old_dbname with the name of the database that you want to rename, and replace new_dbname with the database’s new name:
ALTER DATABASE "old_dbname" RENAME TO "new_dbname";
Example:
ALTER DATABASE "pixy" RENAME TO "backup_pixy_00"
- To verify that the database was renamed, type the following command to list the databases:
\list
Dependencies ( Postgres)
Install dependency
Open Terminal and execute the following commands:
| 1 2 | sudo apt-get update sudo apt-get -y install python-software-properties |
Add PostgreSQL 9.3 repository
Before installing PostgreSQL 9.3, we have to add the PostgreSQL repository where the packages are located.
Setup key:
| 1 | wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add – |
Setup repository:
Run the following command and note down the output. It is the codename of your Ubuntu release.
| 1 | lsb_release -c |
Replace the codename in the below command with the one you got from above command and run:
| 1 | sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt/ codename-pgdg main” > /etc/apt/sources.list.d/postgresql.list’ |
Install PostgreSQL 9.3 using aptitude
| 1 2 | sudo apt-get update sudo apt-get install postgresql-9.3 pgadmin3 |
Accessing Database from Terminal
Installation of PostgreSQL will automatically create a Unix/Ubuntu user, named postgres. We will have to login with this user in order to perform various database activities such as accessing database, taking and restoring backups, starting/stopping server etc.
We may need to reset the password for postgres user before we are able to login to that account.
The UNIX user, postgres is locked by default. Changing the password for this user will unlock the account. It is not preferred and even considered as a serious security threat.
To start with PostgreSQL, you can connect to the default database using the following command:
| 1 | sudo -u postgres psql |
For other activities like taking data base dump, you have to login as postgres UNIX user as shown below(assuming you have root user privileges):
| 1 | sudo su postgres |
Accessing Database from pgAdmin
Just like the Unix user, PostgreSQL will also create a database user named postgres, which we should use for login to database for the first time.
To be able to access database from pgAgent3, we should follow either one of the following two options:
Change the password for the postgres DB user
Change the database access rights in the pg_hba.conf file
We will proceed with the second method, since setting password for postgres DB user is not preferred.
After loging into postgres DB in terminal, run the following command to get the path to your pg_hba.conf file
| 1 | show hba_file ; |
In my case it returned: /etc/postgresql/9.3/main/pg_hba.conf
Open the file using any text editors
| 1 | sudo gedit /etc/postgresql/9.3/main/pg_hba.conf |
And replace the two lines mentioned below:
| 1 | local all all md5 |
to
| 1 | local all all trust |
And
| 1 | host all all 127.0.0.1/32 md5 |
to
| 1 | host all all 127.0.0.1/32 trust |
If you are not able to find any lines similar to above two lines, adding the substitute lines at the top of the file will also do the job.
Restart PostgreSQL server once.
If you want more information about the pg_hba.conf file and its contents, you can read it here or here.
That’s it! You’ve installed PostgreSQL 9.3 on Ubuntu and now you can use pgAdmin3 to login to your database with any password.
| 1 | sudo apt-get update sudo apt-get -y install python-software-properties |
| 1 | wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add – |
Armed with all the basics of postgres sql, we can now proceed further with next steps in deployment.
