PostgreSQL Database Operations

Source: Internet
Author: User
Tags postgresql psql unix domain socket


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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.