+ Reply to Thread
Results 1 to 10 of 10

How to re-order fields in table?

  1. How to re-order fields in table?

    Hi,


    How can I change columns order in an existing table? I went through PG
    manual, also asked google but could not find answer, anyone knows?

    Thanks,

    Darek

  2. Re: How to re-order fields in table?

    On 27 Mrz., 11:34, Dariusz Pelka wrote:
    > How can I change columns order in an existing table? I went through PG
    > manual, also asked google but could not find answer, anyone knows?


    Why would you want to do that? You can change the "order" of the
    columns in a select-statement...?

  3. Re: How to re-order fields in table?

    Holgi wrote:

    >> How can I change columns order in an existing table? I went through PG
    >> manual, also asked google but could not find answer, anyone knows?

    >
    > Why would you want to do that?


    I don't know about the OP... but usually, it's because mysql can do that :-)

    I reckon it's not a Very Important Feature.

    > You can change the "order" of the columns in a select-statement...?


    Sure, but pretty diagrams (and classes that wrap tables, and the like)
    can require a bit of discipline if the columns are not logically grouped.

  4. Re: How to re-order fields in table?

    "Dariusz Pelka" wrote
    in message news:fsft9b$1lt$1@news.onet.pl...
    >
    > How can I change columns order in an existing table?


    Why?

    This question comes up fairly frequently and is amusing in
    its naiveté. %^)

    Just use SELECT A,B,C ... instead of SELECT * ...

    What presentation tool are you using that cannot simply display
    the columns in whatever order you desire?

    If you must attach value to the default column ordering, consider
    ALTER TABLE RENAME, CREATE TABLE, SELECT INTO,...
    or even just CREATE VIEW.

    > I went through PG manual, also asked google but could not
    > find answer, anyone knows?


    Apparently so.

    Google: "change database column order"

    Jon



  5. Re: How to re-order fields in table?


    >
    > This question comes up fairly frequently and is amusing in
    > its naiveté. %^)



    I think there are two main reasons why some people (say, me) would like
    to re-order table columns. First is the logical and meaningful order,
    for example it's good to keep some 'id' as a first column to make it
    more visible when viewing in phppgadmin. Second reason is because it is
    so easy to change fields order in mysql I thought pgsql can't be beaten
    on this field.

    Thanks for all answers, I understand and agree that fields order does
    not matter but I thought it's straight forward to change it. Just for my
    convenience :)

    Thanks again,

    greetings from Poland,
    Darek

  6. Re: How to re-order fields in table?

    "Dariusz Pelka"
    wrote in message news:fsg2fv$ifs$1@news.onet.pl...
    >> This question comes up fairly frequently and is amusing in
    >> its naiveté. %^)

    >
    > I think there are two main reasons why some people (say, me) would like to
    > re-order table columns. First is the logical and meaningful order, for
    > example it's good to keep some 'id' as a first column to make it more
    > visible when viewing in phppgadmin.


    When viewing database tables in generic viewer for
    *database administrative purposes*, the "logical and
    meaningful" column order from an application perspective
    are not very relevant. Key order is more relevant.

    If using a generic database viewer to show an *application-
    perspective*, then choose one that permits reordering of the
    columns for display purposes. Or create views that reorder
    the columns as desired, implement joins for foriegn key
    references, format numbers, etc... Column ordering is
    just the tip of the iceberg from the application perspective.

    > Second reason is because it is so easy to change fields
    > order in mysql I thought pgsql can't be beaten on this field.


    The generic SQL commands/procedures to accomplish
    column reordering are well-known, and in my experience,
    *rarely used* on deployed production systems.

    The MySQL ability to "ALTER TABLE ... ADD COLUMN ...
    FIRST/AFTER" is not the only vendor-specific SQL
    extension, nor perhaps the most useful. For instance, the
    REPLACE extension may be seen as more valuable.

    Jon



  7. Re: How to re-order fields in table?

    Dariusz Pelka writes:

    > How can I change columns order in an existing table?


    Table columns, like table rows, have no inherent sequence. They are
    conceptually a set, not a list.

    You specify whatever sequence you like each time you use the SELECT
    statement, or (by choosing not to sequence them) receive them in an
    arbitrary sequence decided by the implementation.

    --
    \ “I’'s easy to play any musical instrument: all you have to do |
    `\ is touch the right key at the right time and the instrument |
    _o__) will play itself.” —Johann Sebastian Bach |
    Ben Finney

  8. Re: How to re-order fields in table?

    In article , Dariusz Pelka wrote:
    >
    >>
    >> This question comes up fairly frequently and is amusing in
    >> its naiveté. %^)

    >
    >
    >I think there are two main reasons why some people (say, me) would like
    >to re-order table columns. First is the logical and meaningful order,
    >for example it's good to keep some 'id' as a first column to make it
    >more visible when viewing in phppgadmin. Second reason is because it is
    >so easy to change fields order in mysql I thought pgsql can't be beaten
    >on this field.
    >
    >Thanks for all answers, I understand and agree that fields order does
    >not matter but I thought it's straight forward to change it. Just for my
    >convenience :)
    >
    >Thanks again,
    >
    >greetings from Poland,
    >Darek


    You can do this with SQL Manager. There is a "Lite" version that is a free
    download.

    http://sqlmanager.net/products/postgresql/manager/

    Eric

  9. Re: How to re-order fields in table?

    EricF wrote:

    > You can do this with SQL Manager. There is a "Lite" version that is a free
    > download.


    Another way, if you seldom do that (*) and you can dump and restore the DB:

    pg_dump -d --attribute-inserts

    creates a dump with INSERT INTO (col1, col2...)


    Then, you manually change the column order in the CREATE TABLE
    statements, and restore the db.



    (*) no, I dont' believe too much in emergent db design :-)

  10. Smile Re: How to re-order fields in table?

    "(*) no, I dont' believe too much in emergent db design :-)"

    db design will be needed essentially in teamwork. suppose your team is already working the program, and suddenly the need to add field(s) happens, what would you do... ?

    1. add the field and reorder the field so it can be easily read by the programmers
    2. add the field anyway, even if it fell at the bottom of the table.. which can be quite bothersome for programmers to read the table logically...

    I'm not saying it's a must, but the need of re-ordering is sometimes much preferable.

    altering table field (with no re-ordering feature) took quite work effort especially when dealing with a referred table...

    as for the dumping and re-insert solutions, that would be even more risky solution when you're about altering table in a live db during maintenance..

+ Reply to Thread