How can I migrate data from on PostgreSQL database to another PostgreSQL database? Is there any technique to migrate data with SQL scripting?
If you migrate to a new version of PostgreSQL use pg_dump from the latest version. Anyway, this is a simple way to dump and restore :
pg_dump -U username -h hostname source_database | psql -U username target_database
If your destination database has only one additional column, I think the simplest thing to do would be to pg_dump the existing database, load that dump into a completely new database, then add the new column.
An alternative might be to use the
--column-inserts option (among others) for pg_dump. That will dump data as SQL INSERT statements with explicit column names. Those INSERT statements should work fine on tables that have had columns added.