Page History

PostgreSQL table creation

You can use PGamin to create tables manually via GUI selection options. For advanced users we recommend passing SQL statements. In the PGamin screenshots below we do the same as in the following SQL code. SQL has two flavours: case insensitive (A and a are the same) and case-sensitive (A and a afre different). Here we choose the case-sensitive variant by surrounding all table and column names with "" quotes. This approach allows for easy exchange of variables with Matlab which is case-sensitive too. The column names "ObservationID" and "Value" have mixed case to be able to test this. This example comes from the OpenEarthTools postgresql toolbox. Sign-up as http://oss.deltares.nl to be able to access it via http://svn.oss.deltares.nl.

In this example the column "ObservationID" is the primary key. This value unique distinguishes the observations whose values are stored in column "Value". Primary keys can not be of all data types: real type is not possible (no error if given), integer types are recommended.

We follow best practice by letting the values of the primary key be determined automatically by an automatic integer counter. Such a counter is called a serial in PostgreSQL. Upon every addition of the "Value", the "ObservationID" is automatically updated with new unique values that distinguish the rows.

CREATE TABLE "TEST01" () WITH (OIDS=FALSE);
ALTER  TABLE "TEST01" OWNER TO postgres;
ALTER  TABLE "TEST01" ADD   COLUMN "ObservationID" integer;
ALTER  TABLE "TEST01" ALTER COLUMN "ObservationID" SET NOT NULL;
CREATE SEQUENCE "TEST01_ObservationID_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 6 CACHE 1;
ALTER TABLE "TEST01_ObservationID_seq" OWNER TO postgres;
ALTER TABLE "TEST01" ALTER COLUMN "ObservationID" SET DEFAULT nextval('"TEST01_ObservationID_seq"'::regclass);
ALTER TABLE "TEST01" ADD CONSTRAINT "TEST01_pkey" PRIMARY KEY("ObservationID" );
ALTER TABLE "TEST01" ADD COLUMN "Value" real;