Ask Your Question

How to change the order of columns in table ?

asked 2019-01-11 10:30:00 +0100

Pavel_47 gravatar image

Hello, In MS Access it's quite easily to change the order o columns. Surprisingly LibreOffice doesn't provide such basic functionnality. Does exist some work-around ? Thanks

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-01-11 18:56:24 +0100

Ratslinger gravatar image


Changing the order of fields in a database is not a function of Base but rather that of the database. This is difficult to accomplish in the HSQLDB embedded DB but easy in Firebird embedded.

In HSQLDB embedded (default v1.8), it may be best to define a new table with the wanted structure then copy and paste the data from the old table into the new table taking care to match the fields correctly.

For Firebird embedded (default v3.0 with Experimental features enabled) you can use SQL:

Syntax: ALTER TABLE tablename ALTER [COLUMN] colname POSITION <newpos>

Example: ALTER TABLE salary_history2 ALTER old_salary POSITION 2

From Firebird documentation:
The POSITION keyword changes the position of an existing column in the notional “left-to-right” layout of the record.
Numbering of column positions starts at 1.
• If a position less than 1 is specified, an error message will be returned
• If a position number is greater than the number of columns in the table, its new position will be adjusted silently to match the number of columns.

Other databases may differ. You have not stated which database you are using. Base itself is not a database but rather a front end to a database.

edit flag offensive delete link more


Usually, tables are an "unrelevant" storage medium for record fields. By "unrelevant", I mean you don't process records relying on some implicit convention provided by the DB engine. Your fields are accessed through SQL statements which specify which fields will be used for retrieval, then you'll copy the "important" fields to variables or other records. The exact structure of the record, the order of fields does not really matter from a logical point of view.

When it comes to resource optimisation, this order may matter depending on alignment constraint in the DB engine. These constraints are not the same in every engine. So, a recipe for one is not valid for another. Unless you have specific needs, such as a very small embedded device, don't consider this. The most important is getting your application working as expected (remember that "early optimisation is the root of all evil ...(more)

ajlittoz gravatar imageajlittoz ( 2019-01-11 19:39:47 +0100 )edit

(continued) If you have a single "blob", list it last. Anyway, read the recommendations shipping with the engine.

(a note for this site maintainers: strange, I thought the 200-chars limit for comments had been recently relaxed)

ajlittoz gravatar imageajlittoz ( 2019-01-11 19:41:47 +0100 )edit

@ajlittoz My comment here allows for 1000 characters.

Ratslinger gravatar imageRatslinger ( 2019-01-11 19:46:38 +0100 )edit

@Ratslinger: I posted yesterday comments larger than 200 chars, but I was caught here at this outdated limit. That's why I wrote "strange". Sometimes machines, including computers, are really nasty ;-)

ajlittoz gravatar imageajlittoz ( 2019-01-11 20:21:32 +0100 )edit

Hello, The suggestion of Jan doesn't work: link text

Pavel_47 gravatar imagePavel_47 ( 2019-01-29 09:54:05 +0100 )edit

@Pavel_47 Please note there is no Jan here. Jan is a month.

From the image provided, you are executing the ALTER statement in an incorrect area. You cannot do this under the Query section as this is mainly for Select statements (there are some exceptions to this). Rather, this is done from the main Base menu item Tools->SQL...

Ratslinger gravatar imageRatslinger ( 2019-01-29 16:01:25 +0100 )edit

Thank you Ratslinger. I'm sorry for "Jan". I've tried this command in the location you suggested. Also doesn't work. Here is screenshot: link text

Pavel_47 gravatar imagePavel_47 ( 2019-01-30 08:18:46 +0100 )edit

Have re-tried this multiple times on Mint18.3 with LO v6.1.4.2 without problems. Possibly a LO Windows version problem. Best to file Bug report.

Ratslinger gravatar imageRatslinger ( 2019-01-30 11:38:31 +0100 )edit

Works !!! Although table_name and column_name must be enclosed in quotation marks

Pavel_47 gravatar imagePavel_47 ( 2019-01-31 11:03:09 +0100 )edit

Quotation marks are only necessary depending upon case used and database itself.

Ratslinger gravatar imageRatslinger ( 2019-01-31 18:40:03 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-11 10:30:00 +0100

Seen: 45 times

Last updated: Jan 11