PostgreSQL/Working with PostgreSQL
||This page or section is an undeveloped draft or outline.
You can help to develop the work, or you can ask for assistance in the project room.
- 1 Data types
- 2 Constraints
- 3 Managing a database
- 4 Managing storage
- 5 Users and Groups
Strings of characters can be specified to have a limited length or can be left unlimited. It is better to specify the length because:
- It helps guard you against storing an unintended value as an attribute.
- In principle, values could be stored more efficiently if their maximum length was known in advance but that is an implementation detail
Strings of unlimited length are declared as string or varchar. These are effectively equivalent:
- create table foo (bar varchar);
- create table foo (bar string);
Strings of limited length are declared as varchar(n) or character varying(n). These are equivalent:
- create table foo (bar varchar(9));
- create table foo (bar character varying(9));
The limit on the length is enforced. (Note that string constants are enclosed in single quotes.)
db=> insert into foo values('1234567890'); ERROR: value too long for type character varying(9)
There are numerous examples of string manipulation functions and operations in the documentation. Examples include:
- right('abcde', 2) which gives 'de'
- substr('alphabet', 3, 2) which gives 'ph'
- regexp_replace('Thomas', '.[mN]a.', 'M') which gives 'ThM'
Date and Time types
Network address data types
- Represents a MAC address
- Represents an IP network or host. This includes an optional netmask. For example,
- Represents an IP network, with an optional netmask. For example,
Primary key constraints
Managing a database
Each database is a separate collection of data. The only thing shared between databases is the list of users and groups. Each connection to the server must be to a single database, and cannot read or write data in any other database.
This is different to the way MySQL uses the term "database". A MySQL database is closer to PostgreSQL's concept of a schema.
You can create a new database from the SQL shell, with the command
CREATE DATABASE dbname;
Alternatively, you can create the database from the command line with the createdb script:
$ createdb dbname
createdb takes the same user authentication parameters as psql.