CREATE DATABASE

The CREATE DATABASE statement creates a new database. Any user logged in to a database can issue the statement. The user must also have permission from the operating system to create files in the specified location.

CREATE DATABASE [ IF NOT EXISTS ] database-name DICTIONARY_PATH 'dict-path-name' [ DATA_PATH 'data-path-name' ] [ ; 'data-path-name' ]... ] [ NO_REFERENTIAL_INTEGRITY ] [ BOUND ] [ REUSE_DDF ] [ DBSEC_AUTHENTICATION ] [ DBSEC_AUTHORIZATION ] [ V1_METADATA | V2_METADATA ] [ ENCODING < 'codepage-name' | 'CPcodepage-number' | DEFAULT > ]

If you are using ODBC, keep in mind that CREATE DATABASE creates only a database, not an associated data source name (DSN). You will need to create a DSN separately if you want one. See Setting Up ODBC Database Access in PSQL User's Guide.

The CREATE DATABASE statement cannot be used to create the first database on a server. The reason is that a user must log on to a database before issuing the CREATE DATABASE statement. Therefore, at least one database must already exist.

Database-name specifies a name for the new database. The database names must be unique within a server and conform to the rules for identifiers. See Identifier Restrictions by Identifier Type in Advanced Operations Guide.

An error occurs if the database exists and you omit the IF NOT EXISTS clause (status code 2303). No error returns if you include the IF NOT EXISTS clause.

Dict-path-name specifies where the dictionary files (DDFs) reside on physical storage. The data files are also placed in this same location when you use the CREATE TABLE statement or create tables using PSQL Control Center (PCC). See Dictionary Location in PSQL User's Guide.

Data-path-name specifies a possible location of the data files for the database (see note below). You can specify multiple path names by delimiting them with a semicolon.

Data-path-name can be any path that is valid from the database engine point of view, but not from the calling application perspective. The location specified must already exist. The CREATE DATABASE statement does not create directories.

Omit data-path-name if you want to use the same location for the data files as for the dictionary files. You may also specify the same location by passing an empty string for data-path-name. For example, specifying DATA_PATH '' indicates an empty string for the data path.

Note If you create tables using the CREATE TABLE statement or with PCC, the data files are placed in the first dict-path-name specified. If no dict-path-names are specified, data files are created in the dict-path-name location.

Data-path-name is useful if you are creating tables through the Distributed Tuning Interface (DTI). The DTI function PvAddTable allows you to specify where you want the data files located. See PvAddTable() in Distributed Tuning Interface Guide.

By default, the database engine enforces referential integrity. If you specify the NO_REFERENTIAL_INTEGRITY clause, then any triggers and referential integrity defined in the database are not enforced.

If BOUND is specified, the DDFs are bound to the database. A bound database associates a database name with a single set of DDFs, which refer to only one set of data files. The DDFs are bound whether they already existed or are created through the execution of the CREATE DATABASE statement.

If DDFs are bound, you cannot use those DDFs for more than one database, nor can you refer to the data files by more than one set of DDFs.

The REUSE_DDF keyword associates any existing DDFs with the database. The existing DDFs must in the dict-path-name location.

If REUSE_DDF is omitted, new DDFs are created unless DDFs already exists in the dict-path-name location. If DDFs exists in the dict-path-name location, they are associated with the database instead of new DDFs being created.

•Classic. A user who successfully logs into the computer has access to the database contents at whatever level of file system rights the user has been assigned to the data file. File system rights are assigned through the operating system.

•Database. Database user accounts are unrelated to operating system user accounts. User access rights to the data are governed by user permissions set up in the database.

•Mixed. This policy has aspects of both of the other policies. Users log in using their operating system user names and passwords, but user access rights to the data are governed by user permissions set up in the database.

Keyword Included or Omitted in Statement		Security Model
DBSEC_AUTHENTICATION	DBSEC_AUTHORIZATION	Classic	Database	Mixed
omitted	omitted
included	included
omitted	included

The Relational Engine supports two versions of metadata, referred to as version 1 (V1) and version 2 (V2). Metadata version applies to all data dictionary files (DDFs) within that database. V1 metadata is the default.

Among other features, V2 metadata allows for many identifier names to be up to 128 bytes long and for permissions on views and stored procedures. See PSQL Metadata for a complete discussion.

You may include or omit the V1_METADATA keyword to specify V1 metadata. You must include the V2_METADATA keyword to specify V2 metadata.

An encoding is a standard for representing character sets. Character data must be put in a standard format, that is, encoded, so that a computer can process it digitally. An encoding must be established between the PSQL database engine (server) and a PSQL client application. A compatible encoding allows the server and client to interpret data correctly.

Encoding support is divided into database code page and client encoding. The two types of encoding are separate but interrelated (see Database Code Page and Client Encoding in Advanced Operations Guide).

Database code page and client encoding apply only to the Relational Engine. The MicroKernel Engine is not affected.

You specify a code page by using a name or by using the letters CP followed by a code page number. Both must be quoted with single quotation marks. For example, a valid name is 'UTF-8' and a valid number is 'CP1251'.

Windows, Linux, and macOS operating systems have a default encoding referred to as the OS encoding. The default OS encoding differs among the operating systems. The keyword DEFAULT allows you to specify the OS encoding on the server.

Note that, for SQL statement that involve the use of more than one database, you need to ensure that the database code page is the same for all of the databases. Otherwise, string data can be returned incorrectly.

Note The database engine does not validate the encoding of the data and metadata that an application inserts into a database. The engine assumes that all data was entered using the encoding of the server or the client, as explained in Database Code Page and Client Encoding in Advanced Operations Guide.

For SQL statements that involve the use of more than one database (such as a multidatabase join), ensure that the database code page is the same for all of the databases. Otherwise, string data can be returned incorrectly.

You can view the list of supported code page names and numbers with PCC. Start PCC and access the New Database dialog (see To create a new database in PSQL User's Guide). For the Database Code Page option, click Change code page. In the dialog that opens, click Database code page to see a list of available code pages.

On Linux and macOS, see the dbmaint utility man page to display a list of supported code page names and numbers. See the Examples topic for dbmaint in PSQL User's Guide.

The following example creates a database named inventorydb and specifies its location for DDFs on drive D: in the folder mydbfiles\ddf_location. New DDFs are created because none exist in D:\mydbfiles\ddf_location. The data files are placed in the same location as the DDFs. The database uses V1 metadata.

The following example creates a database named HRUSBenefits if it does not already exist, and specifies its location for DDFs on drive C: in the folder HRDatabases\US. Possible locations for the data files include the C: drive in a directory called HRDatabases\US\DataFiles and the E: drive in a directory called Backups\HRUSData (see note under Data Path). Existing DDFs are used if they exist in the DICTIONARY_PATH. The database uses V1 metadata.

CREATE DATABASE IF NOT EXISTS HRUSBenefits DICTIONARY_PATH 'C:\HRDatabases\US' DATA_PATH 'C:\HRDatabases\US\DataFiles ; E:\Backups\HRUSData' REUSE_DDF

The following example creates a database named EastEurope, specifies its location for DDFs on drive C: in the folder Europe\DbaseFiles, creates new DDFs and binds them to the database, sets the security policy to mixed, and uses V2 metadata.

The following example creates a database named Region5Acct, specifies its location for DDFs on drive D: in the folder Canada\Region5\Accounting, and sets the database code page to the default code page used on the server.

The following example creates a database named Region2Inventory, specifies its location for DDFs on drive G: in the folder Japan\Region2, and sets the database code page to 932.

The following example creates a database named VendorCodes, specifies its location for DDFs on drive C: in the folder Capitol_Equipment\Milling, creates new DDFs and binds them to the database, sets the security policy to mixed, uses V2 metadata, and sets the database code page to 1252.

CREATE DATABASE VendorCodes DICTIONARY_PATH 'C:\Capitol_Equipment\Milling' BOUND DBSEC_AUTHORIZATION V2_METADATA ENCODING 'CP1252'