PostgreSQL Database Operations

Create a database

 
CREATE DATABASE name
    [ [ WITH ] 
           [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ CONNECTION LIMIT [=] connlimit ] ]

Parameters

• name

The name of the database to create. It is best to wrap them in double quotation marks to be case sensitive.

• user_name

The name of the database user who will be the owner of the new database, or use theDEFAULToption to specify the current default user (that is, the user who executes the command). To create a database of all the other roles, you must be a direct or indirect member of that role, or a super user.

• template

Template name, which template to create a new database from, or use theDEFAULToption to specify a default template (template1).

• encoding

The character encoding used to create the new database. You can use a literal name (for example‘SQL_ASCII‘), an integer number, orDEFAULTan option to specify (the encoding of the template database).

• lc_collate

Collation (lc_collate) for the new database. This affects the sort order in which the string is applied, such as in the query with order by, and in the order of the indexes used for the text columns. By default, the collation of the template database is used. Please see the additional restrictions below.

• lc_ctype

Character classification () for the new databaseLC_CTYPE. This affects the classification of characters, for example: lowercase, uppercase, and numeric. By default, the character classification of the template database is used. Please see the additional restrictions below.

• tablespace_name

The tablespace name associated with the new database, or theDEFAULTtable space using the option to represent the template database. This table space will be the default tablespace for objects created in this database.

• connlimit

How many concurrent connections the database can accept. -1 (default) means there is no limit.

Optional parameters can be written in any order, not just in the order shown above.

Instance

 
 
CREATE DATABASE "myDb"
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Multiple rows are not supported in Psql, so save the above SQL to a SQL file and execute\i FQ.sqlmultiple rows of SQL in Psql as follows:

postgres=# \i FQ.sql
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 myDb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Matters

If you see a message like this:

createdb: command not found

Then PostgreSQL is not installed: either it is not loaded at all, or the search path does not contain it. Try calling the command with an absolute path:

$ /usr/local/pgsql/bin/createdb mydb

This path may not be the same on your node. Please contact your administrator or see the installation instructions to get the correct location.

Another response might be this:

createdb: could not connect to database postgres: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

This means that the server does not start or does not start at thecreatedbexpected location. Also, you should check the installation instructions or find an administrator.

Another response might be this:

createdb: could not connect to database postgres: FATAL:  role "joe" does not exist

Mention your own login name here. These images can occur if the administrator has not created a PostgreSQL user account for you.

If you have a database user account but do not have the necessary permissions to create a database, you will see something like this:

createdb: database creation failed: ERROR:  permission denied to create database

Not all users have been authorized to create a new database. If PostgreSQL refuses to create a database for you, you need to have the site administrator give you permission to create the database. Please consult your site administrator when this happens. If you installed PostgreSQL yourself, then you should log in as the user who started the database server and then refer to the manual to complete the assignment of permissions.

You can also create a database with a different name. PostgreSQL allows you to create any number of databases on a single node. The database name must start with a letter and be less than 63 bytes long. A handy way to do this is to create a database with the same name as your current user name. Many tools assume that it is the default database name, so you can save keystrokes. To create such a database, you only need to type:

$ createdb

Switch database

• Using the\llist database in Psql, using the\cswitch database

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 myDb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres-# \c "myDb"
Password for user postgres: 
You are now connected to database "myDb" as user "postgres".
myDb-#

• You can alsopsql-dspecify the database name with a parameter when using a command

[PostgresFile] psql -U postgres -W -d "myDb"                                      
Password for user postgres:
psql (9.5.11
Type "help" for help

myDb=#

  

	
   


  

Deleting a database

DROP DATABASE [ IF EXISTS ] name

DROP DATABASEDeletes a database. Delete the directory entry for an existing database and delete the directory that contains the data. Only the database owner can execute this command. Also, you can't execute this command if you or anyone else is connecting to the target database. So topostgresconnect with or any other database, issue this command.DROP DATABASEdo not undo, use carefully!

Parameters

• IF EXISTS

If the specified database does not exist, a notice is emitted instead of throwing an error.

myDb=# DROP DATABASE IF EXISTS "myDb";
ERROR:  cannot drop the currently open database
myDb=# \c postgres
Password for user postgres: 
You are now connected to database "postgres" as user "postgres".
postgres=# DROP DATABASE IF EXISTS "myDb";
DROP DATABASE
postgres=#

You can see that the current databasemyDbis not deleted at the time of the operation.

PostgreSQL Database Operations