Home > Enterprise >  order by 1 yield an error while order by column name is fine
order by 1 yield an error while order by column name is fine

Time:04-29

select trigger_name,
       event_manipulation,
       event_object_schema,
       event_object_table,
       action_order,
       action_condition,
       action_orientation,
       action_timing,
       action_reference_old_table,
       action_reference_new_table
from information_schema.triggers
where event_object_table in ('main_table')
order by 1 collate "C", 2;

yield an error:

ERROR:  42804: collations are not supported by type integer
LINE 13: order by 1 collate "C", 2;

However: order by trigger_name collate "C", 2; is fine.
In collate "C",2, what does the 2 refer to?
the main_table is just a simple table, that have some triggers.
code source: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/test/regress/expected/triggers.out;h=cd812336f2c70b1703d546f850a270f797829130;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c

CodePudding user response:

You have the answer in the first line of your error message. The RDBMS days that it cannot complete a 1 without seeing that it relates to a text column that to which collation could apply.

ERROR:  42804: collations are not supported by type integer
LINE 13: order by 1 collate "C", 2;

The ,2 is simply a second level of sort by column 2. If there are identical values in column 1 these rows will be sorted by column 2.
This is the same as

order by trigger_name collate "C",event_manipulation
  • Related