First Time with PostgreSQL – Creating Table


CREATE TABLE “public”.”product”
(
product_id integer NOT NULL,
product_name character varying,
supplier_name character varying,
unit_price integer
)

ALTER TABLE “public”.”product” ADD UNIQUE (“product_id”);
ALTER TABLE “public”.”product” ADD PRIMARY KEY (“product_id”);

 

source:

http://blog.enricostahn.com/2010/06/11/postgresql-add-primary-key-to-an-existing-table.html

 

Advertisements

PostgreSQL – Restore Backup with Command Line


There are two tools to look at, depending on how you created the dump file.

Your first source of reference should be the man page pg_dump(1) as that is what creates the dump itself. It says:

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql(1). Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications even on other SQL database products.

The alternative archive file formats must be used with pg_restore(1) to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

So depends on the way it was dumped out. You can probably figure it out using the excellent file(1)command – if it mentions ASCII text and/or SQL, it should be restored with psql otherwise you should probably use pg_restore

Restoring is pretty easy:

psql -U <username> -d <dbname> -1 -f <filename>.sql

or

pg_restore -U <username> -d <dbname> -1 -f <filename>.dump

Check out their respective manpages – there’s quite a few options that affect how the restore works. You may have to clean out your “live” databases or recreate them from template0 (as pointed out in a comment) before restoring, depending on how the dumps were generated.

 

source:

http://stackoverflow.com/questions/2732474/restore-a-postgres-backup-file-using-the-command-line

PostgreSQL – What is OID?


OIDs basically give you a built-in, globally unique id for every row, contained in a system column (as opposed to a user-space column). That’s handy for tables where you don’t have a primary key, have duplicate rows, etc. For example, if you have a table with two rows identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they’re non-standard), and their use is essentially deprecated:

In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

 

source:

http://stackoverflow.com/questions/5625585/sql-postgres-oids-what-are-they-and-why-are-they-useful