PostgreSQL assumes the role and the database will have the same name. Therefore, if you create a TheNewUser role, PostgreSQL will access a database with the same name.
Important! PostgreSQL uses Linux information for authentication. In the previous example, you need a user called TheNewUser.
Here are the PostgreSQL database setup steps:
Run the following to switch to the postgres user and create a new database:
sudo -u postgres createdb TheNewUserTo log in to the new database, create a Linux user account with the same name. Since the username contains capital letters, add the --force-badname option.
sudo adduser TheNewUser --force-badnameEnter the interactive PostgreSQL prompt using the new user account:
sudo -u TheNewUser psqlConfirm the connection using this command:
\conninfo
The user should now connect to the new database and be able to create a new table. For example, enter the following to create a buyer data table:
CREATE TABLE buyers(usr_id INT PRIMARY KEY, usr_name VARCHAR(240) NOT NULL, usr_location VARCHAR(240) NOT NULL);When creating a database table, consider the six PostgreSQL constraints:
- PRIMARY KEY – indicates that a column can be a row’s unique identifier.
- FOREIGN KEY – specifies that the values in a column must be the same as the ones in another table’s rows.
- NOT NULL – ensures a column’s value is not empty.
- CHECK – sets a column’s value so that it must satisfy a Boolean expression, which is either “true” or “false”.
- UNIQUE – guarantees that the values in a column are unique among all rows.
- EXCLUSION – prevents values in two rows from overlapping when compared.
To add values to your table, use the following statement. Don’t forget to replace the placeholders with the appropriate value and adjust the number of rows accordingly:
INSERT INTO tablename (column1, column2, column3) VALUES (row1, row2, row3);To show the table, enter the following statement:
SELECT * FROM tablename