在 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 20 | audit_dml_state_select 21 | audit_enabled复制
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
外国CTO也感兴趣的开源数据库项目——openHalo
小满未满、
458次阅读
2025-04-21 16:58:09
9.9 分高危漏洞，尽快升级到 pgAdmin 4 v9.2 进行修复
严少安
356次阅读
2025-04-11 10:43:23
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
332次阅读
2025-04-15 14:48:05
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
严少安
305次阅读
2025-04-07 12:14:29
postgresql+patroni+etcd高可用安装
necessary
168次阅读
2025-03-28 10:11:23
转发有奖 | PostgreSQL 16 PGCM高级认证课程直播班招生中！
墨天轮小教习
149次阅读
2025-04-14 15:58:34
墨天轮PostgreSQL认证证书快递已发（2025年3月批）
墨天轮小教习
129次阅读
2025-04-03 11:43:25
SQL 优化之 OR 子句改写
xiongcc
93次阅读
2025-04-21 00:08:06
融合Redis缓存的PostgreSQL高可用架构
梧桐
87次阅读
2025-04-08 06:35:40
PostgreSQL拓展PGQ实现解析
chirpyli
86次阅读
2025-04-07 11:23:17
