1

I need to add a column to a specific position in a table. I'm doing this because I'm merging tables from two different databases:

DB1.schema1.table1 has these columns:

name_pk | date | type

while DB2.schema1.table1 has these:

name_pk | date | value | type

I need to add value in the first database so that I can dump its data and restore it to DB3 (along with DB2 data, DB3 has the same columns than DB2).

What would be the best way to solve this? I thought:

  1. Just add the "value" column to DB1 before dumping the data. However, because PostgreSQL doesn't have INSERT ... AFTER clause, the column gets added to the end, which is not good.
  2. Dumping the table with schema-only into schema.sql, editing the resulting file manually and adding the missing column to the right spot. It does the trick, but it requires manual work and not suitable for my script going through multiple databases.
  3. Adding column to the end, then adding the rest columns after that and deleting the ones before it. This requires finding out the names and types of the rest of the columns.
  4. Is there any other way of doing this easily?
  • Can you explain how you are going to merge the two tables? Surely if you generate single INSERT statements (eg: insert into yourtable (col1,col3,col2) values(...) ) then the order actually doesn't matter? I'd personally import the table into the 2nd database with a different name, then just do a straight INSERT into the table that you're merging with - the order of columns won't matter then – Philᵀᴹ May 16 at 11:35
  • @Philᵀᴹ I'm pg_dumping the data from all databases to be merged, and then restoring them into single database. It indeed is only single INSERT statements, but the order it generates them does matter. I get errors like Command was: INSERT INTO alarm VALUES (2158, NULL, 20140... pg_restore: [archiver (db)] could not execute query: ERROR: invalid input syntax for integer: "ELEV" LINE 1: ..., 1, 'ELEV', 'L... Which means DB1 tries to insert a string value to a place where there is an integer in DB2. – Asgmch May 16 at 11:53
2

If you're using pg_dump you can use the --attribute-inserts flag when dumping the data - it adds column lists to the generated INSERT statements:

postgres=# create table test ( col1 integer, col2 integer, col4 integer, col3 integer ) ;
CREATE TABLE
postgres=# insert into test values(1,2,4,3);
INSERT 0 1
postgres=# \q

postgres@ubuntu-xenial:~$ pg_dump --attribute-inserts postgres 
--
-- PostgreSQL database dump
--
-- * OUTPUT SNIPPED *
--
-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.test (col1, col2, col4, col3) VALUES (1, 2, 4, 3);

By doing this the order of columns does not matter.

  • Thank you! Attribute-inserts worked! – Asgmch May 16 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.