How do I alter the position of a column in a PostgreSQL database table?
"Alter column position" in the PostgreSQL Wiki says:
PostgreSQL currently defines column
order based on theattnum
column of
thepg_attribute
table. The only way
to change column order is either by
recreating the table, or by adding
columns and rotating data until you
reach the desired layout.
That's pretty weak, but in their defense, in standard SQL, there is no solution for repositioning a column either. Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax.
One other idea occurs to me: you can define a VIEW
that specifies the order of columns how you like it, without changing the physical position of the column in the base table.
How to change the position of column in postgresql without dumping
All you need is a custom insert statement,
For example,
Your table looks like this,
CREATE TABLE "public"."ada" (
"trandate" date,
"locname" text,
"totusers" integer,
"actusers" integer,
"datausage" integer,
"issues" integer,
"id" serial PRIMARY KEY,
"issuessolved" integer
);
and the insert statement can be written as
INSERT INTO "public"."ada" (
"trandate"
,"locname"
,"totusers"
,"actusers"
,"datausage"
,"issues"
,"issuessolved"
)
VALUES (
< trandate
,date >
,< locname
,text >
,< totusers
,integer >
,< actusers
,integer >
,< datausage
,integer >
,< issues
,integer >
,< issuessolved
,integer >
);
How to rearrange the column position in PostgreSQL
You can create a VIEW
for this,
see following demo
create table foo (entityid int,formattedfilename text);
insert into foo values (1,'file1');
insert into foo values (2,'file2');
select * from foo
RESULT
entityid formattedfilename
-------- -----------------
1 file1
2 file2
Now create a view like below
create or replace view vfoo as
select formattedfilename,entityid from foo .
select * from vfoo
RESULT
formattedfilename entityid
----------------- --------
file1 1
file2 2
Still you want to do it with the table itself then refer : https://wiki.postgresql.org/wiki/Alter_column_position
Is it possible to change the natural order of columns in Postgres?
You can actually just straight up change the column order, but I'd hardly recommend it, and you should be very careful if you decide to do it.
eg.
# CREATE TABLE test (a int, b int, c int);
# INSERT INTO test VALUES (1,2,3);
# SELECT * FROM test;
a | b | c
---+---+---
1 | 2 | 3
(1 row)
Now for the tricky bit, you need to connect to your database using the postgres user so you can modify the system tables.
# SELECT relname, relfilenode FROM pg_class WHERE relname='test';
relname | relfilenode
---------+-------------
test_t | 27666
(1 row)
# SELECT attrelid, attname, attnum FROM pg_attribute WHERE attrelid=27666;
attrelid | attname | attnum
----------+----------+--------
27666 | tableoid | -7
27666 | cmax | -6
27666 | xmax | -5
27666 | cmin | -4
27666 | xmin | -3
27666 | ctid | -1
27666 | b | 1
27666 | a | 2
27666 | c | 3
(9 rows)
attnum is a unique column, so you need to use a temporary value when you're modifying the column numbers as such:
# UPDATE pg_attribute SET attnum=4 WHERE attname='a' AND attrelid=27666;
UPDATE 1
# UPDATE pg_attribute SET attnum=1 WHERE attname='b' AND attrelid=27666;
UPDATE 1
# UPDATE pg_attribute SET attnum=2 WHERE attname='a' AND attrelid=27666;
UPDATE 1
# SELECT * FROM test;
b | a | c
---+---+---
1 | 2 | 3
(1 row)
Again, because this is playing around with database system tables, use extreme caution if you feel you really need to do this.
This is working as of postgres 8.3, with prior versions, your milage may vary.
How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?
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
How to change the column position of MySQL table without losing column data?
Try this:
ALTER TABLE table_name MODIFY password varchar(20) AFTER id
Related Topics
Normalization in Plain English
SQL Server Trigger Insert Values from New Row into Another Table
SQL Server 2005 Row_Number() Without Order By
Access - Compare Two Tables and Update or Insert Data in First Table
How to Do If Not Exists in SQLite
Best Way to Model Customer <--> Address
Sql: How to Get the Value of an Attribute in Xml Datatype
SQL Unique Constraint on a 2 Columns Combination
Why Can't I Use Column Aliases in the Next Select Expression
How to Get Other Columns When Using Spark Dataframe Groupby
Postgres Not Using Index When Index Scan Is Much Better Option
Linux Python3 - Can't Open Lib 'SQL Server'
How to Fill Date Gaps in MySQL