Postgresql: Execute some simple commands

Postgresql logo

If you working with small scale database, you always using a database management software to done your task. For example, I usually use pgAdmin to integrated with Postgresql database. In some cases, you have to face with a large-scale database, using a database management isn't best choose. At that moments, I often use commands to done my jobs.

In this post, I will show you how to execute a SQL file from the terminal screen.

Assume you have been installed PostgreSQL database into your computer (server) and your username is Postgres. Let's log in your database by the following command:

sudo su - postgres

The last parameter is your username. After login, you have to choose a database which you will work on it. Assume my database named micard. Let's type following command:

psql micard

The first parameter is the name of the PostgreSQL database and the last parameter is the name of your database (micard in this case).

After login 

Look at above figure, I selected database named micard and I can working with tables in this database now. Following is some simple task:

• Create a new table: Using a command to create a table is a simple task. I will create a table with the following query:

CREATE TABLE student (
    studentid bigserial NOT NULL,
    name character varying(50),
    code character varying(20),
    birthday timestamp without time zone,
    gender boolean,
    address text,
    CONSTRAINT pkstudentid PRIMARY KEY (studentid)
);

You only type the query on terminal screen and press enter to execute. Note: the queries have to end with semi-colon (";'"). If you wanna new line, let's press Ctrl + Enter. And now, you have a new table named student.

A new table was created

• Insert a record: After creating, you can insert records. For examples: I will insert two records as:
  
  INSERT INTO student (name, code, birthday, gender, address)
  VALUES ('Peter Jole', 'SC001', '1995-01-01', true, 'Hanoi, Vietnam');
  INSERT INTO student (name, code, birthday, gender, address)
  VALUES ('Lan Nguyen', 'SC002', '1995-06-15', false, 'Ho Chi Minh, Vietnam');

  

  Insert two record

  
  
• Query: Query is the simple task. For example, I will shows all records which will be inserted by the following query:
  SELECT * FROM student;
  And under figure is the result of that query.
  



Result of select query

• Execute from SQL file: If you don't want to type the queries, there are an alternative resolution for you. You type your queries on a SQL file (for examples: script.sql). And the last, you only execute this file by simple command as:
  \i path/to/file.sql
  In my case, script.sql was placed in /home/chinv/micard folder. My command is
  \i home/chinv/micard/script.sql.

This post introduced some simple PostgreSQL command. I hope that it useful for you.