edu Toidayhoc

Category: postgresql

  • Khóa học miễn phí PostgreSQL – Where Clause nhận dự án làm có lương

    PostgreSQL – WHERE Clause

    The PostgreSQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables.

    If the given condition is satisfied, only then it returns specific value from the table. You can filter out rows that you do not want included in the result-set by using the WHERE clause.

    The WHERE clause not only is used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would examine in subsequent chapters.

    Syntax

    The basic syntax of SELECT statement with WHERE clause is as follows −

    SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

    You can specify a search_condition using like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear.

    Example

    Consider the table having records as follows −

    testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

    Here are simple examples showing usage of PostgreSQL Logical Operators. Following SELECT statement will list down all the records where AGE is greater than or equal to 25 AND salary is greater than or equal to 65000.00 −

    testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

    The above given PostgreSQL statement will produce the following result −

     id | name  | age |  address   | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

    The following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is greater than or equal to 65000.00 −

    testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

    The above given PostgreSQL statement will produce the following result −

     id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(4 rows)

    The following SELECT statement lists down all the records where AGE is not NULL which means all the records, because none of the record has AGE equal to NULL −

    testdb=#  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

    The above given PostgreSQL statement will produce the following result −

      id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   1 | Paul  |  32 | California |  20000
   2 | Allen |  25 | Texas      |  15000
   3 | Teddy |  23 | Norway     |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   5 | David |  27 | Texas      |  85000
   6 | Kim   |  22 | South-Hall |  45000
   7 | James |  24 | Houston    |  10000
(7 rows)

    The following SELECT statement lists down all the records where NAME starts with ”Pa”, does not matter what comes after ”Pa”.

    testdb=# SELECT * FROM COMPANY WHERE NAME LIKE ''Pa%

    The above given PostgreSQL statement will produce the following result −

     id | name | age |address    | salary
----+------+-----+-----------+--------
  1 | Paul |  32 | California|  20000

    The following SELECT statement lists down all the records where AGE value is either 25 or 27 −

    testdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

    The above given PostgreSQL statement will produce the following result −

     id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

    The following SELECT statement lists down all the records where AGE value is neither 25 nor 27 −

    testdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

    The above given PostgreSQL statement will produce the following result −

     id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(4 rows)

    The following SELECT statement lists down all the records where AGE value is in BETWEEN 25 AND 27 −

    testdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

    The above given PostgreSQL statement will produce the following result −

     id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

    The following SELECT statement makes use of SQL subquery where subquery finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by sub-query −

    testdb=# SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

    The above given PostgreSQL statement will produce the following result −

     age
-----
  32
  25
  23
  25
  27
  22
  24
(7 rows)

    The following SELECT statement makes use of SQL subquery where subquery finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from outside query is greater than the age in the result returned by sub-query −

    testdb=# SELECT * FROM COMPANY
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

    The above given PostgreSQL statement will produce the following result −

     id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Like Clause nhận dự án làm có lương

    PostgreSQL – LIKE Clause

    The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1.

    There are two wildcards used in conjunction with the LIKE operator −

    • The percent sign (%)
    • The underscore (_)

    The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations.

    If either of these two signs is not used in conjunction with the LIKE clause, then the LIKE acts like the equals operator.

    Syntax

    The basic syntax of % and _ is as follows −

    SELECT FROM table_name
WHERE column LIKE ''XXXX%''

or

SELECT FROM table_name
WHERE column LIKE ''%XXXX%''

or

SELECT FROM table_name
WHERE column LIKE ''XXXX_''

or

SELECT FROM table_name
WHERE column LIKE ''_XXXX''

or

SELECT FROM table_name
WHERE column LIKE ''_XXXX_''

    You can combine N number of conditions using AND or OR operators. Here XXXX could be any numeric or string value.

    Example

    Here are number of examples showing WHERE part having different LIKE clause with ”%” and ”_” operators −

    S. No. Statement & Description
    1

    WHERE SALARY::text LIKE ”200%”

    Finds any values that start with 200
    2

    WHERE SALARY::text LIKE ”%200%”

    Finds any values that have 200 in any position
    3

    WHERE SALARY::text LIKE ”_00%”

    Finds any values that have 00 in the second and third positions
    4

    WHERE SALARY::text LIKE ”2_%_%”

    Finds any values that start with 2 and are at least 3 characters in length
    5

    WHERE SALARY::text LIKE ”%2”

    Finds any values that end with 2
    6

    WHERE SALARY::text LIKE ”_2%3”

    Finds any values that have 2 in the second position and end with a 3
    7

    WHERE SALARY::text LIKE ”2___3”

    Finds any values in a five-digit number that start with 2 and end with 3
    Postgres LIKE is String compare only. Hence, we need to explicitly cast the integer column to string as in the examples above.

    Let us take a real example, consider the table , having records as follows −

    # select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

    The following is an example, which would display all the records from COMPANY table where AGE starts with 2 −

    testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE ''2%

    This would produce the following result −

     id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
(7 rows)

    The following is an example, which would display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text −

    testdb=# SELECT * FROM COMPANY WHERE ADDRESS  LIKE ''%-%

    This would produce the following result −

     id | name | age |                      address              | salary
----+------+-----+-------------------------------------------+--------
  4 | Mark |  25 | Rich-Mond                                 |  65000
  6 | Kim  |  22 | South-Hall                                |  45000
(2 rows)

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Delete Query nhận dự án làm có lương

    PostgreSQL – DELETE Query

    The PostgreSQL DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete the selected rows. Otherwise, all the records would be deleted.

    Syntax

    The basic syntax of DELETE query with WHERE clause is as follows −

    DELETE FROM table_name
WHERE [condition];

    You can combine N number of conditions using AND or OR operators.

    Example

    Consider the table , having records as follows −

    # select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

    The following is an example, which would DELETE a customer whose ID is 7 −

    testdb=# DELETE FROM COMPANY WHERE ID = 2;

    Now, COMPANY table will have the following records −

     id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
(6 rows)

    If you want to DELETE all the records from COMPANY table, you do not need to use WHERE clause with DELETE queries, which would be as follows −

    testdb=# DELETE FROM COMPANY;

    Now, COMPANY table does not have any record because all the records have been deleted by the DELETE statement.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Select Database nhận dự án làm có lương

    PostgreSQL – SELECT Database

    This chapter explains various methods of accessing the database. Assume that we have already created a database in our previous chapter. You can select the database using either of the following methods −

    • Database SQL Prompt
    • OS Command Prompt

    Database SQL Prompt

    Assume you have already launched your PostgreSQL client and you have landed at the following SQL prompt −

    postgres=#

    You can check the available database list using l, i.e., backslash el command as follows −

    postgres-# l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     |
(4 rows)

postgres-#

    Now, type the following command to connect/select a desired database; here, we will connect to the testdb database.

    postgres=# c testdb;
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

    OS Command Prompt

    You can select your database from the command prompt itself at the time when you login to your database. Following is a simple example −

    psql -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

    You are now logged into PostgreSQL testdb and ready to execute your commands inside testdb. To exit from the database, you can use the command q.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Create Table nhận dự án làm có lương

    PostgreSQL – CREATE Table

    The PostgreSQL CREATE TABLE statement is used to create a new table in any of the given database.

    Syntax

    Basic syntax of CREATE TABLE statement is as follows −

    CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

    CREATE TABLE is a keyword, telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Initially, the empty table in the current database is owned by the user issuing the command.

    Then, in brackets, comes the list, defining each column in the table and what sort of data type it is. The syntax will become clear with an example given below.

    Examples

    The following is an example, which creates a COMPANY table with ID as primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table −

    CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

    Let us create one more table, which we will use in our exercises in subsequent chapters −

    CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

    You can verify if your table has been created successfully using d command, which will be used to list down all the tables in an attached database.

    testdb-# d

    The above given PostgreSQL statement will produce the following result −

               List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

    Use d tablename to describe each table as shown below −

    testdb-# d company

    The above given PostgreSQL statement will produce the following result −

            Table "public.company"
  Column   |     Type      | Modifiers
-----------+---------------+-----------
 id        | integer       | not null
 name      | text          | not null
 age       | integer       | not null
 address   | character(50) |
 salary    | real          |
 join_date | date          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Home nhận dự án làm có lương

    PostgreSQL tutorial

    PostgreSQL Tutorial







    PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. This tutorial will give you quick start with PostgreSQL and make you comfortable with PostgreSQL programming.

    Audience

    This tutorial has been prepared for the beginners to help them understand the basic to advanced concepts related to PostgreSQL Database.

    Prerequisites

    Before you start practicing with various types of examples given in this reference, I”m making an assumption that you are already aware of what a database is, especially RDBMS and what a computer language is.

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Environment Setup nhận dự án làm có lương

    PostgreSQL – Environment Setup

    To start understanding the PostgreSQL basics, first let us install the PostgreSQL. This chapter explains about installing the PostgreSQL on Linux, Windows and Mac OS platforms.

    Installing PostgreSQL on Linux/Unix

    Follow the given steps to install PostgreSQL on your Linux machine. Make sure you are logged in as root before you proceed for the installation.

    • Pick the version number of PostgreSQL you want and, as exactly as possible, the platform you want from

    • I downloaded postgresql-9.2.4-1-linux-x64.run for my 64 bit CentOS-6 machine. Now, let us execute it as follows −

    [root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run
[root@host]# ./postgresql-9.2.4-1-linux-x64.run
------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.

------------------------------------------------------------------------
Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.2]:

    • Once you launch the installer, it asks you a few basic questions like location of the installation, password of the user who will use database, port number, etc. So keep all of them at their default values except password, which you can provide password as per your choice. It will install PostgreSQL at your Linux machine and will display the following message −

    Please wait while Setup installs PostgreSQL on your computer.

 Installing
 0% ______________ 50% ______________ 100%
 #########################################

-----------------------------------------------------------------------
Setup has finished installing PostgreSQL on your computer.

    • Follow the following post-installation steps to create your database −

    [root@host]# su - postgres
Password:
bash-4.1$ createdb testdb
bash-4.1$ psql testdb
psql (8.4.13, server 9.2.4)

test=#

    • You can start/restart postgres server in case it is not running using the following command −

    [root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

    • If your installation was correct, you will have PotsgreSQL prompt test=# as shown above.

    Installing PostgreSQL on Windows

    Follow the given steps to install PostgreSQL on your Windows machine. Make sure you have turned Third Party Antivirus off while installing.

    • Pick the version number of PostgreSQL you want and, as exactly as possible, the platform you want from

    • I downloaded postgresql-9.2.4-1-windows.exe for my Windows PC running in 32bit mode, so let us run postgresql-9.2.4-1-windows.exe as administrator to install PostgreSQL. Select the location where you want to install it. By default, it is installed within Program Files folder.

    postgresql install dir

    • The next step of the installation process would be to select the directory where your data would be stored. By default, it is stored under the “data” directory.

    postgresql install data dir

    • Next, the setup asks for password, so you can use your favorite password.

    postgresql install password

    • The next step; keep the port as default.

    postgresql install port

    • In the next step, when asked for “Locale”, I selected “English, United States”.

    • It takes a while to install PostgreSQL on your system. On completion of the installation process, you will get the following screen. Uncheck the checkbox and click the Finish button.

    postgresql install finish

    After the installation process is completed, you can access pgAdmin III, StackBuilder and PostgreSQL shell from your Program Menu under PostgreSQL 9.2.

    Installing PostgreSQL on Mac

    Follow the given steps to install PostgreSQL on your Mac machine. Make sure you are logged in as administrator before you proceed for the installation.

    • Pick the latest version number of PostgreSQL for Mac OS available at

    • I downloaded postgresql-9.2.4-1-osx.dmg for my Mac OS running with OS X version 10.8.3. Now, let us open the dmg image in finder and just double click it which will give you PostgreSQL installer in the following window −

    postgresql install on Mac

    • Next, click the postgres-9.2.4-1-osx icon, which will give a warning message. Accept the warning and proceed for further installation. It will ask for the administrator password as seen in the following window −

    postgresql Password on Mac

    Enter the password, proceed for the installation, and after this step, restart your Mac machine. If you do not see the following window, start your installation once again.

    Postgresql Start installation on Mac

    • Once you launch the installer, it asks you a few basic questions like location of the installation, password of the user who will use database, port number etc. Therefore, keep all of them at their default values except the password, which you can provide as per your choice. It will install PostgreSQL in your Mac machine in the Application folder which you can check −

    Postgresql Application on Mac

    • Now, you can launch any of the program to start with. Let us start with SQL Shell. When you launch SQL Shell, just use all the default values it displays except, enter your password, which you had selected at the time of installation. If everything goes fine, then you will be inside postgres database and a postgress# prompt will be displayed as shown below −

    Postgresql SQL Shell on Mac

    Congratulations!!! Now you have your environment ready to start with PostgreSQL database programming.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Drop Database nhận dự án làm có lương

    PostgreSQL – DROP Database

    In this chapter, we will discuss how to delete the database in PostgreSQL. There are two options to delete a database −

    • Using DROP DATABASE, an SQL command.
    • Using dropdb a command-line executable.
    Be careful before using this operation because deleting an existing database would result in loss of complete information stored in the database.

    Using DROP DATABASE

    This command drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. This command cannot be executed while you or anyone else is connected to the target database (connect to postgres or any other database to issue this command).

    Syntax

    The syntax for DROP DATABASE is given below −

    DROP DATABASE [ IF EXISTS ] name

    Parameters

    The table lists the parameters with their descriptions.

    S. No. Parameter & Description
    1

    IF EXISTS

    Do not throw an error if the database does not exist. A notice is issued in this case.
    2

    name

    The name of the database to remove.
    We cannot drop a database that has any open connections, including our own connection from psql or pgAdmin III. We must switch to another database or template1 if we want to delete the database we are currently connected to. Thus, it might be more convenient to use the program dropdb instead, which is a wrapper around this command.

    Example

    The following is a simple example, which will delete testdb from your PostgreSQL schema −

    postgres=# DROP DATABASE testdb;
postgres-#

    Using dropdb Command

    PostgresSQL command line executable dropdb is a command-line wrapper around the SQL command DROP DATABASE. There is no effective difference between dropping databases via this utility and via other methods for accessing the server. dropdb destroys an existing PostgreSQL database. The user, who executes this command must be a database super user or the owner of the database.

    Syntax

    The syntax for dropdb is as shown below −

    dropdb  [option...] dbname

    Parameters

    The following table lists the parameters with their descriptions

    S. No. Parameter & Description
    1

    dbname

    The name of a database to be deleted.
    2

    option

    command-line arguments, which dropdb accepts.

    Options

    The following table lists the command-line arguments dropdb accepts −

    S. No. Option & Description
    1

    -e

    Shows the commands being sent to the server.
    2

    -i

    Issues a verification prompt before doing anything destructive.
    3

    -V

    Print the dropdb version and exit.
    4

    –if-exists

    Do not throw an error if the database does not exist. A notice is issued in this case.
    5

    –help

    Show help about dropdb command-line arguments, and exit.
    6

    -h host

    Specifies the host name of the machine on which the server is running.
    7

    -p port

    Specifies the TCP port or the local UNIX domain socket file extension on which the server is listening for connections.
    8

    -U username

    User name to connect as.
    9

    -w

    Never issue a password prompt.
    10

    -W

    Force dropdb to prompt for a password before connecting to a database.
    11

    –maintenance-db=dbname

    Specifies the name of the database to connect to in order to drop the target database.

    Example

    The following example demonstrates deleting a database from OS command prompt −

    dropdb -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****

    The above command drops the database testdb. Here, I have used the postgres (found under the pg_roles of template1) username to drop the database.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Overview nhận dự án làm có lương

    PostgreSQL – Overview

    PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development phase and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

    This tutorial will give you a quick start with PostgreSQL and make you comfortable with PostgreSQL programming.

    What is PostgreSQL?

    PostgreSQL (pronounced as post-gress-Q-L) is an open source relational database management system (DBMS) developed by a worldwide team of volunteers. PostgreSQL is not controlled by any corporation or other private entity and the source code is available free of charge.

    A Brief History of PostgreSQL

    PostgreSQL, originally called Postgres, was created at UCB by a computer science professor named Michael Stonebraker. Stonebraker started Postgres in 1986 as a follow-up project to its predecessor, Ingres, now owned by Computer Associates.

    • 1977-1985 − A project called INGRES was developed.

      • Proof-of-concept for relational databases

      • Established the company Ingres in 1980

      • Bought by Computer Associates in 1994

    • 1986-1994 − POSTGRES

      • Development of the concepts in INGRES with a focus on object orientation and the query language – Quel

      • The code base of INGRES was not used as a basis for POSTGRES

      • Commercialized as Illustra (bought by Informix, bought by IBM)

    • 1994-1995 − Postgres95

      • Support for SQL was added in 1994

      • Released as Postgres95 in 1995

      • Re-released as PostgreSQL 6.0 in 1996

      • Establishment of the PostgreSQL Global Development Team

    Key Features of PostgreSQL

    PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It supports text, images, sounds, and video, and includes programming interfaces for C / C++, Java, Perl, Python, Ruby, Tcl and Open Database Connectivity (ODBC).

    PostgreSQL supports a large part of the SQL standard and offers many modern features including the following −

    • Complex SQL queries
    • SQL Sub-selects
    • Foreign keys
    • Trigger
    • Views
    • Transactions
    • Multiversion concurrency control (MVCC)
    • Streaming Replication (as of 9.0)
    • Hot Standby (as of 9.0)

    You can check official documentation of PostgreSQL to understand the above-mentioned features. PostgreSQL can be extended by the user in many ways. For example by adding new −

    • Data types
    • Functions
    • Operators
    • Aggregate functions
    • Index methods

    Procedural Languages Support

    PostgreSQL supports four standard procedural languages, which allows the users to write their own code in any of the languages and it can be executed by PostgreSQL database server. These procedural languages are – PL/pgSQL, PL/Tcl, PL/Perl and PL/Python. Besides, other non-standard procedural languages like PL/PHP, PL/V8, PL/Ruby, PL/Java, etc., are also supported.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Drop Table nhận dự án làm có lương

    PostgreSQL – DROP Table

    The PostgreSQL DROP TABLE statement is used to remove a table definition and all associated data, indexes, rules, triggers, and constraints for that table.

    You have to be careful while using this command because once a table is deleted then all the information available in the table would also be lost forever.

    Syntax

    Basic syntax of DROP TABLE statement is as follows −

    DROP TABLE table_name;

    Example

    We had created the tables DEPARTMENT and COMPANY in the previous chapter. First, verify these tables (use d to list the tables) −

    testdb-# d

    This would produce the following result −

               List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

    This means DEPARTMENT and COMPANY tables are present. So let us drop them as follows −

    testdb=# drop table department, company;

    This would produce the following result −

    DROP TABLE
testdb=# d
relations found.
testdb=#

    The message returned DROP TABLE indicates that drop command is executed successfully.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc