PostgreSQL : Java Glossary

Advantages of PostgreSQL over MySQL

Postgres is a mature product with its roots in Berkeley.

Postgres has many data types.

You can add your own data tapes by defining them with a little C code.

You can write triggers in Oracle stored procedure language PLpg/SQL and in some other languages, unfortunately not in SQL itself.

Postgres is highly compatible with Oracle. It handles triggers the same way.

Postgres does not crash under heavy loads.

Postgres has remote SSL (Secure Sockets Layer) access.

Postgres has PGADMIN3 for a graphical way of administration from a PC (Personal Computer).

Postgres allows inheritance in the SQL definitions, e. g. a capitals row definition can be derived from a cities row definition.

Postgres uses multiversion concurrency control, rather than locks. Each transaction sees a snapshot of the database at a logical instant in time. Reading does not block writing and vice versa.

PostgreSQL Supported Types

Name	Aliases	Description
bigint	int8	signed eight-byte integer
bigserial	serial8	autoincrementing eight-byte integer
bit		fixed-length bit string
bit varying(n)	varbit(n)	variable-length bit string
boolean	bool	logical Boolean (true/false)
box		rectangular box in the plane
bytea		binary data
character varying(n)	varchar(n)	variable-length character string
character(n)	char(n)	fixed-length character string
cidr		IPv4 (Internet Protocol Version 4) or IPv6 (Internet Protocol Version 6) network address
circle		circle in the plane
date		calendar date (year, month, day)
double precision	float8	double precision floating-point number
ENUM		define the enum like this CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); then use it as if it were a built-in type. It sorts in ordinal order. You set the values with ordinary Strings. The total length of all the possible values must be ≤ 63, a severe restriction.
inet		IPv4 or IPv6 host address
integer	int, int4	signed four-byte integer
interval(p)		time span
line		infinite line in the plane (not fully implemented)
lseg		line segment in the plane
macaddr		MAC (Media Access Control) address
money		currency amount
numeric [ (p, s) ]	decimal [ (p, s) ]	exact numeric with selectable precision
path		open and closed geometric path in the plane
point		geometric point in the plane
polygon		closed geometric path in the plane
real	float4	single precision floating-point number
smallint	int2	signed two-byte integer
serial	serial4	autoincrementing four-byte integer
text		variable-length character string
time [ (p) ] [ without time zone ]		time of day
time [ (p) ] with time zone	timetz	time of day, including time zone
timestamp [ (p) ] without time zone	timestamp	date and time
timestamp [ (p) ] [ with time zone ]	timestamptz	date and time, including time zone
tsquery		text search query
tsvector		text search document
txid_snapshot		user-level transaction ID snapshot
uuid		universally unique identifier
xml		XML (extensible Markup Language) data

Name

Aliases

Description

bigint

int8

signed eight-byte integer

bigserial

serial8

autoincrementing eight-byte integer

bit

fixed-length bit string

bit varying(n)

varbit(n)

variable-length bit string

boolean

bool

logical Boolean (true/false)

box

rectangular box in the plane

bytea

binary data

character varying(n)

varchar(n)

variable-length character string

character(n)

char(n)

fixed-length character string

cidr

IPv4 (Internet Protocol Version 4) or IPv6 (Internet Protocol Version 6) network address

circle

circle in the plane

date

calendar date (year, month, day)

double precision

float8

double precision floating-point number

ENUM

define the enum like this CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); then use it as if it were a built-in type. It sorts in ordinal order. You set the values with ordinary Strings. The total length of all the possible values must be ≤ 63, a severe restriction.

inet

IPv4 or IPv6 host address

integer

int, int4

signed four-byte integer

interval(p)

time span

line

infinite line in the plane (not fully implemented)

lseg

line segment in the plane

macaddr

MAC (Media Access Control) address

money

currency amount

numeric [ (p, s) ]

decimal [ (p, s) ]

exact numeric with selectable precision

path

open and closed geometric path in the plane

point

geometric point in the plane

polygon

closed geometric path in the plane

real

float4

single precision floating-point number

smallint

int2

signed two-byte integer

serial

serial4

autoincrementing four-byte integer

text

variable-length character string

time [ (p) ] [ without time zone ]

time of day

time [ (p) ] with time zone

timetz

time of day, including time zone

timestamp [ (p) ] without time zone

timestamp

date and time

timestamp [ (p) ] [ with time zone ]

timestamptz

date and time, including time zone

tsquery

text search query

tsvector

text search document

txid_snapshot

user-level transaction ID snapshot

uuid

universally unique identifier

xml

XML (extensible Markup Language) data

Gotchas

The biggest gotchas is that you must manually install some set environment parameters. If you don’t, PostgreSQL will reject your password.

File names must use /s not \s. e.g. \i E:/com/mindprod/mother/mboards.sql to run a script. If you forget, you will get a permission denied message.

There is no SQL USE command, so you must enter the dbname on the dbcreate or psql command line, or the DriverManager.getConnection statement. You can enter SQL commands at the psql.exe command line switch: psql -d dbname -U username --help. You can get it to explain the syntax with --help, \h and \? help commands.

The SHOW command does almost nothing. Use \d instead to get information about tables.

When you install a new version, it may tell you the new format is incompatible with the old one. It is up to you to somehow get the data out of your old database and reload it into you new one.

The command you need to exit the interactive command processor is not obvious \q.

Please email your feedback for publication, letters to the editor, errors, omissions, typos, formatting errors, ambiguities, unclear wording, broken/redirected link reports, suggestions to improve this page or comments to Roedy Green :

. If you want your message, your name or email kept confidential, not considered for public posting, please explicitly specify that. Unless you state otherwise, I will treat your message as a letter to the editor that I may or may not publish in the feedback section. After that, it will be too late to retract it. If you disagree with something I said, please quote it and cite the web page where you found it, tell me why you think it is wrong, and, if possible, provide some supporting evidence. Threatening to kill me or spouting obscenities has yet to persuade me to change my mind.

	You can get the freshest copy of this page from:	or possibly from your local J: drive (Java virtual drive/mindprod.com website mirror)
	http://mindprod.com/jgloss/postgresql.html	J:\mindprod\jgloss\postgresql.html
	Please email your feedback for publication, letters to the editor, errors, omissions, typos, formatting errors, ambiguities, unclear wording, broken/redirected link reports, suggestions to improve this page or comments to Roedy Green : . If you want your message, your name or email kept confidential, not considered for public posting, please explicitly specify that. Unless you state otherwise, I will treat your message as a letter to the editor that I may or may not publish in the feedback section. After that, it will be too late to retract it. If you disagree with something I said, please quote it and cite the web page where you found it, tell me why you think it is wrong, and, if possible, provide some supporting evidence. Threatening to kill me or spouting obscenities has yet to persuade me to change my mind.
	Canadian Mind Products
	mindprod.com IP:[65.110.21.43]
view Blog	Your face IP:[38.107.179.211]
Feedback	You are visitor number 17,659.

PostgreSQL : Java Glossary

Advantages of PostgreSQL over MySQL

Advantages of MySQL over PostgreSQL

PostgreSQL Supported Types

Gotchas