在 Oracle 数据库中，Rownum 是非常常用的一个查询输出，通过 rownum 可以为结果记录增加一个类似行号的标识，在 PostgreSQL 数据库中缺省没有 Rownum 这个功能，但是可以通过分析函数来实现类似的效果。
# select row_number() OVER (ORDER BY name) ,name from pg_settings;
row_number | name
------------+------------------------------------
1 | DateStyle
2 | FencedUDFMemoryLimit
3 | IntervalStyle
4 | RepOriginId
5 | TimeZone
6 | UDFWorkerMemHardLimit
7 | acce_min_datasize_per_thread
8 | acceleration_with_compute_pool
9 | advance_xlog_file_num
10 | alarm_component
11 | alarm_report_interval
12 | allocate_mem_cost
13 | allow_concurrent_tuple_update
14 | allow_system_table_mods
15 | analysis_options
16 | application_name
17 | archive_command
18 | archive_mode
19 | archive_timeout
20 | array_nulls
21 | audit_copy_exec
22 | audit_data_format
23 | audit_database_process
24 | audit_directory
25 | audit_dml_state
26 | audit_dml_state_select
27 | audit_enabled
28 | audit_file_remain_threshold
29 | audit_file_remain_time
30 | audit_function_exec
31 | audit_grant_revoke
32 | audit_login_logout
33 | audit_resource_policy
34 | audit_rotation_interval
35 | audit_rotation_size
36 | audit_set_parameter
37 | audit_space_limit
38 | audit_system_object
39 | audit_user_locked
40 | audit_user_violation
41 | auth_iteration_count
42 | authentication_timeout
43 | autoanalyze
44 | autoanalyze_timeout
45 | autovacuum
46 | autovacuum_analyze_scale_factor
47 | autovacuum_analyze_threshold
48 | autovacuum_freeze_max_age
49 | autovacuum_io_limits
50 | autovacuum_max_workers
51 | autovacuum_mode
52 | autovacuum_naptime
53 | autovacuum_vacuum_cost_delay
54 | autovacuum_vacuum_cost_limit
55 | autovacuum_vacuum_scale_factor
56 | autovacuum_vacuum_threshold
57 | backend_flush_after
58 | backslash_quote
59 | backtrace_min_messages
60 | backwrite_quantity
61 | bbox_dump_count
62 | bbox_dump_path
63 | behavior_compat_options
64 | bgwriter_delay
65 | bgwriter_flush_after
66 | bgwriter_lru_maxpages
67 | bgwriter_lru_multiplier
68 | block_size
69 | bulk_read_ring_size
70 | bulk_write_ring_size
71 | bytea_output
72 | cache_connection
73 | cgroup_name
74 | check_function_bodies
75 | check_implicit_conversions
76 | checkpoint_completion_target
77 | checkpoint_flush_after
78 | checkpoint_segments
79 | checkpoint_timeout
80 | checkpoint_wait_timeout
81 | checkpoint_warning
82 | client_encoding
83 | client_min_messages
84 | cn_send_buffer_size
85 | codegen_cost_threshold
86 | codegen_mot_limit
87 | codegen_strategy
88 | comm_ackchk_time
89 | comm_control_port
90 | comm_debug_mode
91 | comm_max_receiver
92 | comm_memory_pool
93 | comm_memory_pool_percent
94 | comm_no_delay
95 | comm_quota_size
96 | comm_sctp_port
97 | comm_stat_mode
98 | comm_tcp_mode
99 | comm_timer_mode
100 | comm_usable_memory
101 | commit_delay
102 | commit_siblings
103 | config_file
104 | connection_alarm_rate
105 | connection_info
106 | constraint_exclusion
107 | convert_string_to_digit
108 | cost_param
109 | cpu_collect_timer
110 | cpu_index_tuple_cost
111 | cpu_operator_cost
112 | cpu_tuple_cost
113 | cstore_backwrite_max_threshold
114 | cstore_backwrite_quantity
115 | cstore_buffers
116 | cstore_insert_mode
117 | cstore_prefetch_quantity
118 | current_logic_cluster
119 | current_schema
120 | cursor_tuple_fraction
121 | data_directory
122 | data_replicate_buffer_size
123 | data_sync_retry
124 | datanode_heartbeat_interval
125 | deadlock_timeout
126 | debug_assertions
127 | debug_pretty_print
128 | debug_print_parse
129 | debug_print_plan
130 | debug_print_rewritten
131 | default_statistics_target
132 | default_storage_nodegroup
133 | default_tablespace
134 | default_text_search_config
135 | default_transaction_deferrable
136 | default_transaction_isolation
137 | default_transaction_read_only
138 | default_with_oids
139 | defer_csn_cleanup_time
140 | dfs_partition_directory_length
141 | disable_memory_protect
142 | dynamic_library_path
143 | effective_cache_size
144 | effective_io_concurrency
145 | enableSeparationOfDuty
146 | enable_absolute_tablespace
147 | enable_access_server_directory
148 | enable_adio_debug
149 | enable_adio_function
150 | enable_alarm
151 | enable_analyze_check
152 | enable_bbox_dump
153 | enable_beta_features
154 | enable_beta_nestloop_fusion
155 | enable_beta_opfusion
156 | enable_bitmapscan
157 | enable_bloom_filter
158 | enable_broadcast
159 | enable_cbm_tracking
160 | enable_change_hjcost
161 | enable_codegen
162 | enable_codegen_mot
163 | enable_codegen_mot_print
164 | enable_codegen_print
165 | enable_compress_spill
166 | enable_constraint_optimization
167 | enable_copy_server_files
168 | enable_csqual_pushdown
169 | enable_data_replicate
170 | enable_debug_vacuum
171 | enable_delta_store
172 | enable_double_write
173 | enable_early_free
174 | enable_extrapolation_stats
175 | enable_fast_allocate
176 | enable_fast_numeric
177 | enable_force_vector_engine
178 | enable_global_plancache
179 | enable_global_stats
180 | enable_hadoop_env
181 | enable_hashagg
182 | enable_hashjoin
183 | enable_hdfs_predicate_pushdown
184 | enable_incremental_catchup
185 | enable_incremental_checkpoint
186 | enable_index_nestloop
187 | enable_indexonlyscan
188 | enable_indexscan
189 | enable_instance_metric_persistent
190 | enable_instr_cpu_timer
191 | enable_instr_rt_percentile
192 | enable_instr_track_wait
193 | enable_kill_query
194 | enable_light_proxy
195 | enable_logical_io_statistics
196 | enable_material
197 | enable_memory_context_control
198 | enable_memory_limit
199 | enable_mergejoin
200 | enable_mix_replication
201 | enable_nestloop
202 | enable_nodegroup_debug
203 | enable_nonsysadmin_execute_direct
204 | enable_online_ddl_waitlock
205 | enable_opfusion
206 | enable_orc_cache
207 | enable_page_lsn_check
208 | enable_parallel_ddl
209 | enable_partitionwise
210 | enable_pbe_optimization
211 | enable_prevent_job_task_startup
212 | enable_resource_record
213 | enable_resource_track
214 | enable_save_datachanged_timestamp
215 | enable_seqscan
216 | enable_show_any_tuples
217 | enable_slot_log
218 | enable_sonic_hashagg
219 | enable_sonic_hashjoin
220 | enable_sonic_optspill
221 | enable_sort
222 | enable_stream_replication
223 | enable_thread_pool
224 | enable_tidscan
225 | enable_trigger_shipping
226 | enable_twophase_commit
227 | enable_upgrade_merge_lock_mode
228 | enable_user_metric_persistent
229 | enable_valuepartition_pruning
230 | enable_vector_engine
231 | enable_wdr_snapshot
232 | enable_xlog_prune
233 | enforce_oracle_behavior
234 | enforce_two_phase_commit
235 | escape_string_warning
236 | event_source
237 | exit_on_error
238 | expected_computing_nodegroup
239 | explain_dna_file
240 | explain_perf_mode
241 | external_pid_file
242 | extra_float_digits
243 | failed_login_attempts
244 | fast_extend_file_size
245 | fault_mon_timeout
246 | force_bitmapand
247 | force_pseudo_codegen_mot
248 | from_collapse_limit
249 | fsync
250 | full_page_writes
251 | gds_debug_mod
252 | geqo
253 | geqo_effort
254 | geqo_generations
255 | geqo_pool_size
256 | geqo_seed
257 | geqo_selection_bias
258 | geqo_threshold
259 | gin_fuzzy_search_limit
260 | gin_pending_list_limit
261 | gs_clean_timeout
262 | ha_module_debug
263 | hashagg_table_size
264 | hba_file
265 | hot_standby
266 | hot_standby_feedback
267 | ident_file
268 | ignore_checksum_failure
269 | ignore_system_indexes
270 | incremental_checkpoint_timeout
271 | instance_metric_retention_time
272 | instr_rt_percentile_interval
273 | instr_unique_sql_count
274 | instr_unique_sql_track_type
275 | integer_datetimes
276 | io_control_unit
277 | io_limits
278 | io_priority
279 | job_queue_processes
280 | join_collapse_limit
281 | krb_caseins_users
282 | krb_server_keyfile
283 | krb_srvname
284 | lastval_supported
285 | lc_collate
286 | lc_ctype
287 | lc_messages
288 | lc_monetary
289 | lc_numeric
290 | lc_time
291 | listen_addresses
292 | lo_compat_privileges
293 | local_bind_address
294 | local_preload_libraries
295 | lockwait_timeout
296 | log_autovacuum_min_duration
297 | log_checkpoints
298 | log_connections
299 | log_destination
300 | log_directory
301 | log_disconnections
302 | log_duration
303 | log_error_verbosity
304 | log_executor_stats
305 | log_file_mode
306 | log_filename
307 | log_hostname
308 | log_line_prefix
309 | log_lock_waits
310 | log_min_duration_statement
311 | log_min_error_statement
312 | log_min_messages
313 | log_pagewriter
314 | log_parser_stats
315 | log_planner_stats
316 | log_rotation_age
317 | log_rotation_size
318 | log_statement
319 | log_statement_stats
320 | log_temp_files
321 | log_timezone
322 | log_truncate_on_rotation
323 | logging_collector
324 | logging_module
325 | maintenance_work_mem
326 | max_cached_tuplebufs
327 | max_changes_in_memory
328 | max_cn_temp_file_size
329 | max_compile_functions
330 | max_connections
331 | max_files_per_process
332 | max_function_args
333 | max_identifier_length
334 | max_index_keys
335 | max_loaded_cudesc
336 | max_locks_per_transaction
337 | max_pred_locks_per_transaction
338 | max_prepared_transactions
339 | max_process_memory
340 | max_query_retry_times
341 | max_recursive_times
342 | max_replication_slots
343 | max_resource_package
344 | max_stack_depth
345 | max_standby_archive_delay
346 | max_standby_streaming_delay
347 | max_user_defined_exception
348 | max_wal_senders
349 | memory_detail_tracking
350 | memory_tracking_mode
351 | memorypool_enable
352 | memorypool_size
353 | minimum_pool_size
354 | modify_initial_password
355 | most_available_sync
356 | mot_allow_index_on_nullable_column
357 | mot_config_file
358 | ngram_gram_size
359 | ngram_grapsymbol_ignore
360 | ngram_punctuation_ignore
361 | nls_timestamp_format
362 | numa_distribute_mode
363 | omit_encoding_error
364 | opfusion_debug_mode
365 | pagewriter_sleep
366 | pagewriter_thread_num
367 | pagewriter_threshold
368 | partition_lock_upgrade_timeout
369 | partition_max_cache_size
370 | partition_mem_batch
371 | password_effect_time
372 | password_encryption_type
373 | password_lock_time
374 | password_max_length
375 | password_min_digital
376 | password_min_length
377 | password_min_lowercase
378 | password_min_special
379 | password_min_uppercase
380 | password_notify_time
381 | password_policy
382 | password_reuse_max
383 | password_reuse_time
384 | percentile
385 | pgxc_node_name
386 | plan_cache_mode
387 | plan_mode_seed
388 | pljava_vmoptions
389 | plog_merge_age
390 | pooler_maximum_idle_time
391 | port
392 | post_auth_delay
393 | pre_auth_delay
394 | prefetch_quantity
395 | primary_slotname
396 | psort_work_mem
397 | qrw_inlist2join_optmode
398 | query_band
399 | query_dop
400 | query_max_mem
401 | query_mem
402 | quote_all_identifiers
403 | raise_errors_if_no_files
404 | random_page_cost
405 | recovery_max_workers
406 | recovery_parallelism
407 | recovery_parse_workers
408 | recovery_redo_workers
409 | recovery_time_target
410 | remote_read_mode
411 | remotetype
412 | replconninfo1
413 | replconninfo2
414 | replconninfo3
415 | replconninfo4
416 | replconninfo5
417 | replconninfo6
418 | replconninfo7
419 | replication_type
420 | require_ssl
421 | resource_track_cost
422 | resource_track_duration
423 | resource_track_level
424 | resource_track_log
425 | restart_after_crash
426 | retry_ecode_list
427 | rewrite_rule
428 | schedule_splits_threshold
429 | search_path
430 | segment_size
431 | seq_page_cost
432 | server_encoding
433 | server_version
434 | server_version_num
435 | session_history_memory
436 | session_replication_role
437 | session_respool
438 | session_statistics_memory
439 | session_timeout
440 | shared_buffers
441 | shared_preload_libraries
442 | show_acce_estimate_detail
443 | skew_option
444 | sql_compatibility
445 | sql_inheritance
446 | sql_use_spacelimit
447 | ssl
448 | ssl_ca_file
449 | ssl_cert_file
450 | ssl_ciphers
451 | ssl_crl_file
452 | ssl_key_file
453 | ssl_renegotiation_limit
454 | standard_conforming_strings
455 | standby_shared_buffers_fraction
456 | statement_timeout
457 | stats_temp_directory
458 | string_hash_compatible
459 | support_batch_bind
460 | support_extended_features
461 | synchronize_seqscans
462 | synchronous_commit
463 | synchronous_standby_names
464 | sysadmin_reserved_connections
465 | syslog_facility
466 | syslog_ident
467 | table_skewness_warning_rows
468 | table_skewness_warning_threshold
469 | tcp_keepalives_count
470 | tcp_keepalives_idle
471 | tcp_keepalives_interval
472 | td_compatible_truncation
473 | temp_buffers
474 | temp_file_limit
475 | temp_tablespaces
476 | thread_pool_attr
477 | timezone_abbreviations
478 | topsql_retention_time
479 | trace_notify
480 | trace_recovery_messages
481 | trace_sort
482 | track_activities
483 | track_activity_query_size
484 | track_counts
485 | track_functions
486 | track_io_timing
487 | track_sql_count
488 | track_thread_wait_status_interval
489 | transaction_deferrable
490 | transaction_isolation
491 | transaction_pending_time
492 | transaction_read_only
493 | transaction_sync_naptime
494 | transaction_sync_timeout
495 | transform_null_equals
496 | transparent_encrypt_kms_region
497 | transparent_encrypt_kms_url
498 | transparent_encrypted_string
499 | twophase_clean_workers
500 | udf_memory_limit
501 | uncontrolled_memory_context
502 | unix_socket_directory
503 | unix_socket_group
504 | unix_socket_permissions
505 | update_lockwait_timeout
506 | update_process_title
507 | upgrade_mode
508 | use_workload_manager
509 | user_metric_retention_time
510 | vacuum_cost_delay
511 | vacuum_cost_limit
512 | vacuum_cost_page_dirty
513 | vacuum_cost_page_hit
514 | vacuum_cost_page_miss
515 | vacuum_defer_cleanup_age
516 | vacuum_freeze_min_age
517 | vacuum_freeze_table_age
518 | wait_dummy_time
519 | wal_block_size
520 | wal_buffers
521 | wal_keep_segments
522 | wal_level
523 | wal_log_hints
524 | wal_receiver_buffer_size
525 | wal_receiver_connect_retries
526 | wal_receiver_connect_timeout
527 | wal_receiver_status_interval
528 | wal_receiver_timeout
529 | wal_segment_size
530 | wal_sender_timeout
531 | wal_sync_method
532 | wal_writer_delay
533 | walsender_max_send_size
534 | wdr_snapshot_interval
535 | wdr_snapshot_query_timeout
536 | wdr_snapshot_retention_days
537 | work_mem
538 | xc_maintenance_mode
539 | xloginsert_locks
540 | xmlbinary
541 | xmloption
542 | zero_damaged_pages
如果不需要排序，则更加简单：
# select row_number() over() as rownum,name from pg_settings;
rownum | name
--------+------------------------------------
1 | acce_min_datasize_per_thread
2 | acceleration_with_compute_pool
3 | advance_xlog_file_num
4 | alarm_component
5 | alarm_report_interval
6 | allocate_mem_cost
7 | allow_concurrent_tuple_update
8 | allow_system_table_mods
9 | analysis_options
10 | application_name
11 | archive_command
12 | archive_mode
13 | archive_timeout
14 | array_nulls
15 | audit_copy_exec
16 | audit_data_format
17 | audit_database_process
18 | audit_directory
19 | audit_dml_state