www.javadevhome.com

Google
   

POSTGRESQL

http://www.postgresql.org
http://www.pgadmin.org
http://www.postgresql.org/docs

Install verify/maintain (more or less - V8.3) that :
short version install
post install setup

installation on ubuntu with ubuntu software centre, then
su - postgres
createuser -P
(enter new userName other than 'postgres' and choose superuser)
run pgadmin3
connect with new userName

Basic Commands

su - postgresUserName (postgresUserName='postgres' in general)
$PG_HOME/postgres -D $PG_HOME/data (startup db)
initdb -D $PG_HOME/data (init db if $PG_HOME/data is empty)
pg_ctl start -D $PG_HOME/data
pg_ctl stop -D $PG_HOME/data
pg_ctl restart -D $PG_HOME/data
pg_ctl status -D $PG_HOME/data
createdb dbName (creates default DBs: 'tempalte0', 'template1', 'template2')
dropdb dbName
createuser -P (enter userName and and role as superuser - Yes) [default userName=postgresUserName='postgres']

pg_dump dbName > backupfile (backup)
psql dbName < backupfile (restore)
pg_dumpall > backupfile (backup)
psql -f backupfile postgresUserName (restore)

psql -d dbName (dbName='template1' by default )

PSQL Commands:
\encoding ISO-8859-1
copy schemaName.tableName FROM 'C:/home/myFolder/myfile_fr.txt';


Autoincrement and set primary key

CREATE TABLE mySchema.test (PK_SEQ serial, MY_VAL int);
INSERT INTO mySchema.test VALUES (1,1);
INSERT INTO mySchema.test VALUES (2,2);
INSERT INTO mySchema.test VALUES (3,3);
SELECT setval('mySchema.test_PK_SEQ_seq', 4);
INSERT INTO mySchema.test (MY_VAL) VALUES (4);

Serial Types :

CREATE TABLE tablename (
colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);

or :

CREATE TABLE tablename (
colname NOT NULL DEFAULT nextval('flight."colname_seq"'::regclass)
);

CREATE SEQUENCE flight."colname_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 3
CACHE 1;
ALTER TABLE flight."colname_seq" OWNER TO tableOwner;