PostgreSQL/Working with PostgreSQL

From Wikibooks, open books for an open world
Jump to: navigation, search

Data types[edit]

String types[edit]

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'

Numeric types[edit]

Date and Time types[edit]

Network address data types[edit]

Represents a MAC address
Represents an IP network or host. This includes an optional netmask. For example, or
Represents an IP network, with an optional netmask. For example, or 192.168.0

User-defined types[edit]


UNIQUE constraints[edit]

Primary key constraints[edit]

CHECK constraints[edit]

Managing a database[edit]



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


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.


Managing storage[edit]


Users and Groups[edit]