How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?

How to add a new column in a table after the 2nd or 3rd column in the table using postgres?

My code looks as follows

ALTER TABLE n_domains ADD COLUMN contract_nr int after owner_id

No, there's no direct way to do that. And there's a reason for it - every query should list all the fields it needs in whatever order (and format etc) it needs them, thus making the order of the columns in one table insignificant.

If you really need to do that I can think of one workaround:

• dump and save the description of the table in question (using pg_dump --schema-only --table=<schema.table> ...)
• add the column you want where you want it in the saved definition
• rename the table in the saved definition so not to clash with the name of the old table when you attempt to create it
• create the new table using this definition
• populate the new table with the data from the old table using 'INSERT INTO <new_table> SELECT field1, field2, <default_for_new_field>, field3,... FROM <old_table>';
• rename the old table
• rename the new table to the original name
• eventually drop the old, renamed table after you make sure everything's alright

The order of columns is not irrelevant, putting fixed width columns at the front of the table can optimize the storage layout of your data, it can also make working with your data easier outside of your application code.

PostgreSQL does not support altering the column ordering (see [Alter column position][1] on the PostgreSQL wiki); if the table is relatively isolated, your best bet is to recreate the table:

CREATE TABLE foobar_new ( ... );
INSERT INTO foobar_new SELECT ... FROM foobar;
DROP TABLE foobar CASCADE;
ALTER TABLE foobar_new RENAME TO foobar;

If you have a lot of views or constraints defined against the table, you can re-add all the columns after the new column and drop the original columns (see the PostgreSQL wiki for an example).

[1]: http://wiki.postgresql.org/wiki/Alter_column_position "Alter column position"

The real problem here is that it's not done yet. Currently PostgreSQL's logical ordering is the same as the physical ordering. That's problematic because you can't get a different logical ordering, but it's even worse because the table isn't physically packed automatically, so by moving columns you can get different performance characteristics.

Arguing that it's that way by intent in design is pointless. It's somewhat likely to change at some point when an acceptable patch is submitted.

All of that said, is it a good idea to rely on the ordinal positioning of columns, logical or physical? Hell no. In production code you should never be using an implicit ordering or *. Why make the code more brittle than it needs to be? Correctness should always be a higher priority than saving a few keystrokes.

As a work around, you can in fact modify the column ordering by recreating the table, or through the "add and reorder" game

See also,

• Column tetris reordering in order to make things more space-efficient

The column order is relevant to me, so I created this function. See if it helps. It works with indexes, primary key, and triggers. Missing Views and Foreign Key and other features are missing.

Example:

SELECT xaddcolumn('table', 'col3 int NOT NULL DEFAULT 0', 'col2');

Source code:

CREATE OR REPLACE FUNCTION xaddcolumn(ptable text, pcol text, pafter text)  RETURNS void AS $BODY$
DECLARE
	rcol RECORD;
	rkey RECORD;
	ridx RECORD;
	rtgr RECORD;
	vsql text;
	vkey text;
	vidx text;
	cidx text;
	vtgr text;
	ctgr text;
	etgr text;
	vseq text;
	vtype text;
	vcols text;
BEGIN
	EXECUTE 'CREATE TABLE zzz_' || ptable || ' AS SELECT * FROM ' || ptable;
	--colunas
	vseq = '';
	vcols = '';
	vsql = 'CREATE TABLE ' || ptable || '(';
	FOR rcol IN SELECT column_name as col, udt_name as coltype, column_default as coldef,
		is_nullable as is_null, character_maximum_length as len,
		numeric_precision as num_prec, numeric_scale as num_scale
		FROM information_schema.columns
		WHERE table_name = ptable
		ORDER BY ordinal_position
	LOOP
		vtype = rcol.coltype;
		IF (substr(rcol.coldef,1,7) = 'nextval') THEN
			vtype = 'serial';
			vseq = vseq || 'SELECT setval(''' || ptable || '_' || rcol.col || '_seq'''
				|| ', max(' || rcol.col || ')) FROM ' || ptable || ';';
		ELSIF (vtype = 'bpchar') THEN
			vtype = 'char';
		END IF;
		vsql = vsql || E'\n' || rcol.col || ' ' || vtype;
		IF (vtype in ('varchar', 'char')) THEN
			vsql = vsql || '(' || rcol.len || ')';
		ELSIF (vtype = 'numeric') THEN
			vsql = vsql || '(' || rcol.num_prec || ',' || rcol.num_scale || ')';
		END IF;
		IF (rcol.is_null = 'NO') THEN
			vsql = vsql || ' NOT NULL';
		END IF;
		IF (rcol.coldef <> '' AND vtype <> 'serial') THEN
			vsql = vsql || ' DEFAULT ' || rcol.coldef;
		END IF;
		vsql = vsql || E',';
		vcols = vcols || rcol.col || ',';
		--
		IF (rcol.col = pafter) THEN
			vsql = vsql || E'\n' || pcol || ',';
		END IF;
	END LOOP;
	vcols = substr(vcols,1,length(vcols)-1);
	--keys
	vkey = '';
	FOR rkey IN SELECT constraint_name as name, column_name as col
		FROM information_schema.key_column_usage
		WHERE table_name = ptable
	LOOP
		IF (vkey = '') THEN
			vkey = E'\nCONSTRAINT ' || rkey.name || ' PRIMARY KEY (';
		END IF;
		vkey = vkey || rkey.col || ',';
	END LOOP;
	IF (vkey <> '') THEN
		vsql = vsql || substr(vkey,1,length(vkey)-1) || ') ';
	END IF;
	vsql = substr(vsql,1,length(vsql)-1) || ') WITHOUT OIDS';
	--index
	vidx = '';
	cidx = '';
	FOR ridx IN SELECT s.indexrelname as nome, a.attname as col
		FROM pg_index i LEFT JOIN pg_class c ON c.oid = i.indrelid
		LEFT JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey)
		LEFT JOIN pg_stat_user_indexes s USING (indexrelid)
		WHERE c.relname = ptable AND i.indisunique != 't' AND i.indisprimary != 't'
		ORDER BY s.indexrelname
	LOOP
		IF (ridx.nome <> cidx) THEN
			IF (vidx <> '') THEN
				vidx = substr(vidx,1,length(vidx)-1) || ');';
			END IF;
			cidx = ridx.nome;
			vidx = vidx || E'\nCREATE INDEX ' || cidx || ' ON ' || ptable || ' (';
		END IF;
		vidx = vidx || ridx.col || ',';
	END LOOP;
	IF (vidx <> '') THEN
		vidx = substr(vidx,1,length(vidx)-1) || ')';
	END IF;
	--trigger
	vtgr = '';
	ctgr = '';
	etgr = '';
	FOR rtgr IN SELECT trigger_name as nome, event_manipulation as eve,
		action_statement as act, condition_timing as cond
		FROM information_schema.triggers
		WHERE event_object_table = ptable
	LOOP
		IF (rtgr.nome <> ctgr) THEN
			IF (vtgr <> '') THEN
				vtgr = replace(vtgr, '_@eve_', substr(etgr,1,length(etgr)-3));
			END IF;
			etgr = '';
			ctgr = rtgr.nome;
			vtgr = vtgr || 'CREATE TRIGGER ' || ctgr || ' ' || rtgr.cond || ' _@eve_ '
				|| 'ON ' || ptable || ' FOR EACH ROW ' || rtgr.act || ';';
		END IF;
		etgr = etgr || rtgr.eve || ' OR ';
	END LOOP;
	IF (vtgr <> '') THEN
		vtgr = replace(vtgr, '_@eve_', substr(etgr,1,length(etgr)-3));
	END IF;
	--exclui velha e cria nova
	EXECUTE 'DROP TABLE ' || ptable;
	IF (EXISTS (SELECT sequence_name FROM information_schema.sequences
		WHERE sequence_name = ptable||'_id_seq'))
	THEN
		EXECUTE 'DROP SEQUENCE '||ptable||'_id_seq';
	END IF;
	EXECUTE vsql;
	--dados na nova
	EXECUTE 'INSERT INTO ' || ptable || '(' || vcols || ')' ||
		E'\nSELECT ' || vcols || ' FROM zzz_' || ptable;
	EXECUTE vseq;
	EXECUTE vidx;
	EXECUTE vtgr;
	EXECUTE 'DROP TABLE zzz_' || ptable;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

@Jeremy Gustie's solution above almost works, but will do the wrong thing if the ordinals are off (or fail altogether if the re-ordered ordinals make incompatible types match). Give it a try:

CREATE TABLE test1 (one varchar, two varchar, three varchar);
CREATE TABLE test2 (three varchar, two varchar, one varchar);
INSERT INTO test1 (one, two, three) VALUES ('one', 'two', 'three');
INSERT INTO test2 SELECT * FROM test1;
SELECT * FROM test2;

The results show the problem:

testdb=> select * from test2;
 three | two |  one
-------+-----+-------
 one   | two | three
(1 row)

You can remedy this by specifying the column names in the insert:

INSERT INTO test2 (one, two, three) SELECT * FROM test1;

That gives you what you really want:

testdb=> select * from test2;
 three | two | one
-------+-----+-----
 three | two | one
(1 row)

The problem comes when you have legacy that doesn't do this, as I indicated above in my comment on peufeu's reply.

Update: It occurred to me that you can do the same thing with the column names in the INSERT clause by specifying the column names in the SELECT clause. You just have to reorder them to match the ordinals in the target table:

INSERT INTO test2 SELECT three, two, one FROM test1;

And you can of course do both to be very explicit:

INSERT INTO test2 (one, two, three) SELECT one, two, three FROM test1;

That gives you the same results as above, with the column values properly matched.

> The order of the columns is totally irrelevant in relational databases

Yes.

For instance if you use Python, you would do :

cursor.execute( "SELECT id, name FROM users" )
for id, name in cursor:
    print id, name

Or you would do :

cursor.execute( "SELECT * FROM users" )
for row in cursor:
    print row['id'], row['name']

But no sane person would ever use positional results like this :

cursor.execute( "SELECT * FROM users" )
for id, name in cursor:
   print id, name

Well, it's a visual goody for DBA's and can be implemented to the engine with minor performance loss. Add a column order table to pg_catalog or where it's suited best. Keep it in memory and use it before certain queries. Why overthink such a small eye candy.

@ Milen A. Radev

The irrelevant need from having a set order of columns is not always defined by the query that pulls them. In the values from pg_fetch_row does not include the associated column name and therefore would require the columns to be defined by the SQL statement.

A simple select * from would require innate knowledge of the table structure, and would sometimes cause issues if the order of the columns were to change.

Using pg_fetch_assoc is a more reliable method as you can reference the column names, and therefore use a simple select * from.