Run the following command in the terminal.
$ sudo -i -u postgres
This command is used to open a shell session as the user "postgres" with an environment set up as if "postgres" had logged in directly. This is often useful when you need to perform administrative tasks or run commands specific to the PostgreSQL database system, which typically runs with a dedicated user account for security reasons.
Now, write psql command in terminal to open the command-line interface to PostgreSQL.
$ psql
We now have the PostgreSQL open in our terminal.
To fetch the all database, following command is used.
$ \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------------------+----------+----------+---------+-------+-----------------------
abc_development | postgres | UTF8 | en_IN | en_IN |
exp_development | postgres | UTF8 | en_IN | en_IN |
postgres | postgres | UTF8 | en_IN | en_IN |
template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
(7 rows)
To connect with specific database, use \c followed by the database name.
$ \c abc_development
You are now connected to database "abc_development" as user "postgres".
We're now connected with the abc_development database.
To list all the tables within given database, following command is used.
$ \dt
Did not find any relations.
We get the message "Did not find any relations." meaning that no table exists as of now in this database.
We can create the table using following command (do not run this command yet).
create table products (
id int primary key,
name varchar(256) not null
);
We're trying to add id as primary key and name as the another string column. But, this query has one problem - id is not auto increment for us by default. To have it that feature, we need to defined it as serial than int like this (now you can run this command).
create table products (
id serial primary key,
name varchar(256) not null
);
With this the products table is created.
Let's write a simple insert query to confirm the usage of the table.
insert into products (name) values ('Neem plant');
Finally, let's confirm that the product is added successfully in table by writing the select query.
select * from products;
id | name
----+--------------
1 | Neem plant
(1 row)
Labels: psql