0

I am using CodeIgniter 2.1.3 and PHP 5.4.8, and two PostgreSQL servers (P1 and P2).

I am having a problem with list_fields() function in CodeIgniter. When I retrieve fields from P1 server, fields are in the order that I originally created the table with. However, if I use the exact same codes to retrieve fields from P2 server, fields are in reverse order.

If fields from P1 is array('id', 'name', 'age'),

fields from P2 becomes array('age', 'name', 'id')

I don't think this is CodeIgniter specific problem, but rather general database configuration or PHP problem, because codes are identical.

This is the code that I get fields with.

$fields = $this->db->list_fields("clients");
kidonchu
  • 785
  • 1
  • 9
  • 20
  • So the order of columns that I defined when creating table doesn't define the order that gets retrieved? I assumed so because all my MSSQL and MySQL databases were behaving like so until I ran into this PGSQL database. I think I was wrong...then how do I determine the order of columns when getting field names or data? Defining every column name in SELECT clause? – kidonchu Mar 13 '13 at 18:32
  • I agree with you on having more maintainable code by specifying column names. I am fairly new to this field and still learning. Thanks for your input! – kidonchu Mar 13 '13 at 19:45
  • 1
    @muistooshort, on PostgreSQL I use select * all the time, since it guarantees I get a well-defined data type back. This allows for a number of other things to be done with the query after retrieval. – Chris Travers Mar 14 '13 at 13:56
  • @muistooshort: your comment triggered a corrective answer .. – Erwin Brandstetter Mar 15 '13 at 03:16

2 Answers2

2

I have to clarify something. @muistooshort claims in a comment above:

Technically there is no defined order to the columns in a table.

@mu may be thinking of the order or rows, which is arbitrary without ORDER BY.

It is completely incorrect for the order of columns, which is well defined and stored in the column pg_attribute.attnum. It's used in many places, like INSERT without column definition list or SELECT *. It is preserved through a dump / restore cycle and has significant bearing on storage size and performance.

You cannot simply change the order of columns in PostgreSQL, because it has not been implemented, yet. It's deeply wired into the system and hard to change. There is a Postgres Wiki page and it's on the TODO list of the project:

Allow column display reordering by recording a display, storage, and permanent id for every column?

Find out for your table:

SELECT attname, attnum
FROM   pg_attribute 
WHERE  attrelid = 'myschema.mytable'::regclass
AND    NOT attisdropped  -- no dropped (dead) columns
AND    attnum > 0        -- no system columns
ORDER  BY attnum;

It is unwise to use SELECT * in some contexts, where the columns of the underlying table may change and break your code. It is explicitly wise to use SELECT * in other contexts, where you need all columns (in default order).

As to the primary question

This should not occur. SELECT * returns columns in a well defined order in PostgreSQL. Some middleware must be messing with you.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • But the column order is well defined *within one database*. Quite often you'll have equivalent tables in different databases (dev1, dev2, and production for example) but the column order won't be the same. Perhaps "instance defined" would be more accurate than "undefined". – mu is too short Mar 15 '13 at 03:34
  • @muistooshort: If you use the same `CREATE` script, the column order is the same. If you dump / restore, the column order is still the same. Of course, better not rely on it if you don't have to ... – Erwin Brandstetter Mar 15 '13 at 03:46
  • @ErwinBrandstetter I tried with your sql query, and got the same order of columns...I guess CodeIgniter is messing up with it then...thanks for your input on getting exact order of columns so that I can determine where the problem doesn't lie on. – kidonchu Mar 15 '13 at 13:23
  • I've had tables come out different (but equivalent) when multiple branches are modifying the schema. Sure, the schema is supposed to sacrosanct and untouchable but reality is messy etc. So perhaps you're helping me untangle invalid assumptions that actually lead to good advice and good habits :) – mu is too short Mar 15 '13 at 19:42
  • @muistooshort: That may well be the case. As long as people don't know what they are doing, it's probably better to avoid `SELECT *` in persisted code - most of the time. – Erwin Brandstetter Mar 16 '13 at 00:27
0

I suspect you are used to MySQL which allows you to reorder columns post columns post-table creation. PostgreSQL does not let you do this, so when you:

ALTER TABLE foo ADD bar int;

It puts this on the end of the table always and there is no way to change the order.

On PostgreSQL you should not assume that the order of the columns is meaningful because these can differ from server to server based on the order in which the columns were defined.

However the fact that these are reversed is odd to me.

If you want to see the expected order on the db, use:

\d foo

from psql

If these are reversed then the issue is in the db creation (this is my first impression). That's the first thing to look at. If that doesn't show the problem then there is something really odd going on with CodeIgniter.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182