postgresql 创建数据库问题

1、使用CREATE DATABASE

该命令将创建一个数据库PostgreSQL的shell提示符，但你应该有适当的权限来创建数据库。默认情况下，创建新的数据库将通过克隆标准系统数据库template1。

语法：

CREATE DATABASE语句的基本语法如下：

CREATE DATABASE dbname

其中dbname是要创建的数据库的名称。

例子：

下面是一个简单的例子，这将创建testdb 在PostgreSQL模式：

postgres=# CREATE DATABASE testdb

postgres-#

2、使用createdb的命令

PostgreSQL命令行可执行createdb是是SQL命令CREATE DATABASE一个包装器。此命令和SQL命令CREATE DATABASE之间唯一的区别是，前者可以直接在命令行中运行，它允许的注释被添加到数据库中，全部在一个命令。

语法：

createdb语法如下所示：

createdb [option...] [dbname [description]]

参数

下表列出了参数及它们的描述。

参数名称            描述

dbname    The name of a database to create.  

description    Specifies a comment to be associated with the newly created database.  

options    command-line arguments which createdb accepts.  

选项

下表列出了命令行参数CREATEDB接收：

选项                            描述

-D tablespace         Specifies the default tablespace for the database.  

-e                     Echo the commands that createdb generates and sends to the server.  

-E encoding    Specifies the character encoding scheme to be used in this database.  

-l locale    Specifies the locale to be used in this database.  

-T template    Specifies the template database from which to build this database.  

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

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

-p port    Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections.  

-U username    User name to connect as.  

-w    Never issue a password prompt.  

-W    Force createdb to prompt for a password before connecting to a database.  

打开命令提示符，然后去是PostgreSQL安装所在的目录。进入到bin目录，执行下面的命令创建一个数据库。

createdb -h localhost -p 5432 -U postgress testdb

password ******

上面的命令会提示Postgres的默认的PostgreSQL管理用户的密码，以便提供密码和继续创建新的数据库。

一旦创建数据库时可以使用上述方法，可以检查它在列表中的数据库使用l即反斜线el命令如下：

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-#

如将一Shapfile文件“c:\road.shp”导入到数据表“road”中，数据库为“sjzmap”。

1、运行“命令提示符”。

2、切换至PostgreSQL数据库安装目录中的bin目录下。

3、执行此目录下的shp2pgsql命令：“shp2pgsql c:\road.shp road >c:\road.sql”。

4、如将此文件直接导入数据库（不推荐）：“shp2pgsql -c c:\road.shp road sjzmap | psql -d sjzmap”。

5、使用pgAdmin3 选择数据库，再导入表。

注：

Drops the database table before creating a new table with the data in the Shape file.

Appends data from the Shape file into the database table. Note that to use this option to load multiple files, the files must have the same attributes and same data types.

Creates a new table and populates it from the Shape file.

Only produces the table creation SQL code, without adding any actual data. This can be used if you need to completely separate the table creation and data loading steps.

Use the PostgreSQL "dump" format for the output data. This can be combined with -a, -c and -d. It is much faster to load than the default "insert" SQL format. Use this for very large data sets.

Creates and populates the geometry tables with the specified SRID.

Keep identifiers' case (column, schema and attributes). Note that attributes in Shapefile are all UPPERCASE.

Coerce all integers to standard 32-bit integers, do not create 64-bit bigints, even if the DBF header signature appears to warrant it.

Create a GiST index on the geometry column.

Output WKT format, for use with older (0.x) versions of PostGIS. Note that this will introduce coordinate drifts and will drop M values from shapefiles.

Specify encoding of the input data (dbf file). When used, all attributes of the dbf are converted from the specified encoding to UTF8. The resulting SQL output will contain a command, so that the backend will be able to reconvert from UTF8 to whatever encoding the database is configured to use internally.

---