CREATE TABLE “public”.”product”
product_id integer NOT NULL,
product_name character varying,
supplier_name character varying,
ALTER TABLE “public”.”product” ADD UNIQUE (“product_id”);
ALTER TABLE “public”.”product” ADD PRIMARY KEY (“product_id”);
to get list of columns on certain table use this command:
select column_name, data_type from INFORMATION_SCHEMA.COLUMNS where table_name = ‘table’;
for version >= 8.4.0:
pg_dump –column-inserts –data-only –table=<table> <database>
for version < 8.4.0:
pg_dump -D -t <table> <database>
We usually use DESCRIBE command in MySQL to display table structure, in PostgreSQL we use this command:
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ‘table’;
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
Restoring is pretty easy:
psql -U <username> -d <dbname> -1 -f <filename>.sql
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.
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.