⚡

Postgresql技能Skill postgresql

这项技能提供全面的PostgreSQL数据库开发辅助，内容涵盖SQL查询、数据库设计、性能调优和高级功能，适用于需要使用PostgreSQL数据库、编写SQL或管理数据库系统的场合。

数据分析 0 次安装 2 次浏览 更新于 3/1/2026

Postgresql Skill

Comprehensive assistance with postgresql development, generated from official documentation.

When to Use This Skill

This skill should be triggered when:

  • Working with postgresql
  • Asking about postgresql features or APIs
  • Implementing postgresql solutions
  • Debugging postgresql code
  • Learning postgresql best practices

Quick Reference

Common Patterns

Pattern 1: 32.1. Database Connection Control Functions # 32.1.1. Connection Strings 32.1.2. Parameter Key Words The following functions deal with making a connection to a PostgreSQL backend server. An application program can have several backend connections open at one time. (One reason to do that is to access more than one database.) Each connection is represented by a PGconn object, which is obtained from the function PQconnectdb, PQconnectdbParams, or PQsetdbLogin. Note that these functions will always return a non-null object pointer, unless perhaps there is too little memory even to allocate the PGconn object. The PQstatus function should be called to check the return value for a successful connection before queries are sent via the connection object. Warning If untrusted users have access to a database that has not adopted a secure schema usage pattern, begin each session by removing publicly-writable schemas from search_path. One can set parameter key word options to value -csearch_path=. Alternately, one can issue PQexec(conn, “SELECT pg_catalog.set_config(‘search_path’, ‘’, false)”) after connecting. This consideration is not specific to libpq; it applies to every interface for executing arbitrary SQL commands. Warning On Unix, forking a process with open libpq connections can lead to unpredictable results because the parent and child processes share the same sockets and operating system resources. For this reason, such usage is not recommended, though doing an exec from the child process to load a new executable is safe. PQconnectdbParams # Makes a new connection to the database server. PGconn *PQconnectdbParams(const char * const *keywords, const char * const *values, int expand_dbname); This function opens a new database connection using the parameters taken from two NULL-terminated arrays. The first, keywords, is defined as an array of strings, each one being a key word. The second, values, gives the value for each key word. Unlike PQsetdbLogin below, the parameter set can be extended without changing the function signature, so use of this function (or its nonblocking analogs PQconnectStartParams and PQconnectPoll) is preferred for new application programming. The currently recognized parameter key words are listed in Section 32.1.2. The passed arrays can be empty to use all default parameters, or can contain one or more parameter settings. They must be matched in length. Processing will stop at the first NULL entry in the keywords array. Also, if the values entry associated with a non-NULL keywords entry is NULL or an empty string, that entry is ignored and processing continues with the next pair of array entries. When expand_dbname is non-zero, the value for the first dbname key word is checked to see if it is a connection string. If so, it is “expanded” into the individual connection parameters extracted from the string. The value is considered to be a connection string, rather than just a database name, if it contains an equal sign (=) or it begins with a URI scheme designator. (More details on connection string formats appear in Section 32.1.1.) Only the first occurrence of dbname is treated in this way; any subsequent dbname parameter is processed as a plain database name. In general the parameter arrays are processed from start to end. If any key word is repeated, the last value (that is not NULL or empty) is used. This rule applies in particular when a key word found in a connection string conflicts with one appearing in the keywords array. Thus, the programmer may determine whether array entries can override or be overridden by values taken from a connection string. Array entries appearing before an expanded dbname entry can be overridden by fields of the connection string, and in turn those fields are overridden by array entries appearing after dbname (but, again, only if those entries supply non-empty values). After processing all the array entries and any expanded connection string, any connection parameters that remain unset are filled with default values. If an unset parameter’s corresponding environment variable (see Section 32.15) is set, its value is used. If the environment variable is not set either, then the parameter’s built-in default value is used. PQconnectdb # Makes a new connection to the database server. PGconn *PQconnectdb(const char *conninfo); This function opens a new database connection using the parameters taken from the string conninfo. The passed string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace, or it can contain a URI. See Section 32.1.1 for details. PQsetdbLogin # Makes a new connection to the database server. PGconn *PQsetdbLogin(const char *pghost, const char *pgport, const char *pgoptions, const char *pgtty, const char *dbName, const char *login, const char *pwd); This is the predecessor of PQconnectdb with a fixed set of parameters. It has the same functionality except that the missing parameters will always take on default values. Write NULL or an empty string for any one of the fixed parameters that is to be defaulted. If the dbName contains an = sign or has a valid connection URI prefix, it is taken as a conninfo string in exactly the same way as if it had been passed to PQconnectdb, and the remaining parameters are then applied as specified for PQconnectdbParams. pgtty is no longer used and any value passed will be ignored. PQsetdb # Makes a new connection to the database server. PGconn *PQsetdb(char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName); This is a macro that calls PQsetdbLogin with null pointers for the login and pwd parameters. It is provided for backward compatibility with very old programs. PQconnectStartParamsPQconnectStartPQconnectPoll # Make a connection to the database server in a nonblocking manner. PGconn *PQconnectStartParams(const char * const *keywords, const char * const *values, int expand_dbname); PGconn *PQconnectStart(const char *conninfo); PostgresPollingStatusType PQconnectPoll(PGconn *conn); These three functions are used to open a connection to a database server such that your application’s thread of execution is not blocked on remote I/O whilst doing so. The point of this approach is that the waits for I/O to complete can occur in the application’s main loop, rather than down inside PQconnectdbParams or PQconnectdb, and so the application can manage this operation in parallel with other activities. With PQconnectStartParams, the database connection is made using the parameters taken from the keywords and values arrays, and controlled by expand_dbname, as described above for PQconnectdbParams. With PQconnectStart, the database connection is made using the parameters taken from the string conninfo as described above for PQconnectdb. Neither PQconnectStartParams nor PQconnectStart nor PQconnectPoll will block, so long as a number of restrictions are met: The hostaddr parameter must be used appropriately to prevent DNS queries from being made. See the documentation of this parameter in Section 32.1.2 for details. If you call PQtrace, ensure that the stream object into which you trace will not block. You must ensure that the socket is in the appropriate state before calling PQconnectPoll, as described below. To begin a nonblocking connection request, call PQconnectStart or PQconnectStartParams. If the result is null, then libpq has been unable to allocate a new PGconn structure. Otherwise, a valid PGconn pointer is returned (though not yet representing a valid connection to the database). Next call PQstatus(conn). If the result is CONNECTION_BAD, the connection attempt has already failed, typically because of invalid connection parameters. If PQconnectStart or PQconnectStartParams succeeds, the next stage is to poll libpq so that it can proceed with the connection sequence. Use PQsocket(conn) to obtain the descriptor of the socket underlying the database connection. (Caution: do not assume that the socket remains the same across PQconnectPoll calls.) Loop thus: If PQconnectPoll(conn) last returned PGRES_POLLING_READING, wait until the socket is ready to read (as indicated by select(), poll(), or similar system function). Note that PQsocketPoll can help reduce boilerplate by abstracting the setup of select(2) or poll(2) if it is available on your system. Then call PQconnectPoll(conn) again. Conversely, if PQconnectPoll(conn) last returned PGRES_POLLING_WRITING, wait until the socket is ready to write, then call PQconnectPoll(conn) again. On the first iteration, i.e., if you have yet to call PQconnectPoll, behave as if it last returned PGRES_POLLING_WRITING. Continue this loop until PQconnectPoll(conn) returns PGRES_POLLING_FAILED, indicating the connection procedure has failed, or PGRES_POLLING_OK, indicating the connection has been successfully made. At any time during connection, the status of the connection can be checked by calling PQstatus. If this call returns CONNECTION_BAD, then the connection procedure has failed; if the call returns CONNECTION_OK, then the connection is ready. Both of these states are equally detectable from the return value of PQconnectPoll, described above. Other states might also occur during (and only during) an asynchronous connection procedure. These indicate the current stage of the connection procedure and might be useful to provide feedback to the user for example. These statuses are: CONNECTION_STARTED # Waiting for connection to be made. CONNECTION_MADE # Connection OK; waiting to send. CONNECTION_AWAITING_RESPONSE # Waiting for a response from the server. CONNECTION_AUTH_OK # Received authentication; waiting for backend start-up to finish. CONNECTION_SSL_STARTUP # Negotiating SSL encryption. CONNECTION_GSS_STARTUP # Negotiating GSS encryption. CONNECTION_CHECK_WRITABLE # Checking if connection is able to handle write transactions. CONNECTION_CHECK_STANDBY # Checking if connection is to a server in standby mode. CONNECTION_CONSUME # Consuming any remaining response messages on connection. Note that, although these constants will remain (in order to maintain compatibility), an application should never rely upon these occurring in a particular order, or at all, or on the status always being one of these documented values. An application might do something like this: switch(PQstatus(conn)) { case CONNECTION_STARTED: feedback = “Connecting…”; break; case CONNECTION_MADE: feedback = “Connected to server…”; break; . . . default: feedback = “Connecting…”; } The connect_timeout connection parameter is ignored when using PQconnectPoll; it is the application’s responsibility to decide whether an excessive amount of time has elapsed. Otherwise, PQconnectStart followed by a PQconnectPoll loop is equivalent to PQconnectdb. Note that when PQconnectStart or PQconnectStartParams returns a non-null pointer, you must call PQfinish when you are finished with it, in order to dispose of the structure and any associated memory blocks. This must be done even if the connection attempt fails or is abandoned. PQsocketPoll # Poll a connection’s underlying socket descriptor retrieved with PQsocket. The primary use of this function is iterating through the connection sequence described in the documentation of PQconnectStartParams. typedef int64_t pg_usec_time_t; int PQsocketPoll(int sock, int forRead, int forWrite, pg_usec_time_t end_time); This function performs polling of a file descriptor, optionally with a timeout. If forRead is nonzero, the function will terminate when the socket is ready for reading. If forWrite is nonzero, the function will terminate when the socket is ready for writing. The timeout is specified by end_time, which is the time to stop waiting expressed as a number of microseconds since the Unix epoch (that is, time_t times 1 million). Timeout is infinite if end_time is -1. Timeout is immediate (no blocking) if end_time is 0 (or indeed, any time before now). Timeout values can be calculated conveniently by adding the desired number of microseconds to the result of PQgetCurrentTimeUSec. Note that the underlying system calls may have less than microsecond precision, so that the actual delay may be imprecise. The function returns a value greater than 0 if the specified condition is met, 0 if a timeout occurred, or -1 if an error occurred. The error can be retrieved by checking the errno(3) value. In the event both forRead and forWrite are zero, the function immediately returns a timeout indication. PQsocketPoll is implemented using either poll(2) or select(2), depending on platform. See POLLIN and POLLOUT from poll(2), or readfds and writefds from select(2), for more information. PQconndefaults # Returns the default connection options. PQconninfoOption *PQconndefaults(void); typedef struct { char keyword; / The keyword of the option */ char envvar; / Fallback environment variable name */ char compiled; / Fallback compiled in default value */ char val; / Option’s current value, or NULL */ char label; / Label for field in connect dialog / char dispchar; / Indicates how to display this field in a connect dialog. Values are: “” Display entered value as is "" Password field - hide value “D” Debug option - don’t show by default / int dispsize; / Field size in characters for dialog */ } PQconninfoOption; Returns a connection options array. This can be used to determine all possible PQconnectdb options and their current default values. The return value points to an array of PQconninfoOption structures, which ends with an entry having a null keyword pointer. The null pointer is returned if memory could not be allocated. Note that the current default values (val fields) will depend on environment variables and other context. A missing or invalid service file will be silently ignored. Callers must treat the connection options data as read-only. After processing the options array, free it by passing it to PQconninfoFree. If this is not done, a small amount of memory is leaked for each call to PQconndefaults. PQconninfo # Returns the connection options used by a live connection. PQconninfoOption *PQconninfo(PGconn *conn); Returns a connection options array. This can be used to determine all possible PQconnectdb options and the values that were used to connect to the server. The return value points to an array of PQconninfoOption structures, which ends with an entry having a null keyword pointer. All notes above for PQconndefaults also apply to the result of PQconninfo. PQconninfoParse # Returns parsed connection options from the provided connection string. PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg); Parses a connection string and returns the resulting options as an array; or returns NULL if there is a problem with the connection string. This function can be used to extract the PQconnectdb options in the provided connection string. The return value points to an array of PQconninfoOption structures, which ends with an entry having a null keyword pointer. All legal options will be present in the result array, but the PQconninfoOption for any option not present in the connection string will have val set to NULL; default values are not inserted. If errmsg is not NULL, then *errmsg is set to NULL on success, else to a malloc’d error string explaining the problem. (It is also possible for *errmsg to be set to NULL and the function to return NULL; this indicates an out-of-memory condition.) After processing the options array, free it by passing it to PQconninfoFree. If this is not done, some memory is leaked for each call to PQconninfoParse. Conversely, if an error occurs and errmsg is not NULL, be sure to free the error string using PQfreemem. PQfinish # Closes the connection to the server. Also frees memory used by the PGconn object. void PQfinish(PGconn *conn); Note that even if the server connection attempt fails (as indicated by PQstatus), the application should call PQfinish to free the memory used by the PGconn object. The PGconn pointer must not be used again after PQfinish has been called. PQreset # Resets the communication channel to the server. void PQreset(PGconn *conn); This function will close the connection to the server and attempt to establish a new connection, using all the same parameters previously used. This might be useful for error recovery if a working connection is lost. PQresetStartPQresetPoll # Reset the communication channel to the server, in a nonblocking manner. int PQresetStart(PGconn *conn); PostgresPollingStatusType PQresetPoll(PGconn *conn); These functions will close the connection to the server and attempt to establish a new connection, using all the same parameters previously used. This can be useful for error recovery if a working connection is lost. They differ from PQreset (above) in that they act in a nonblocking manner. These functions suffer from the same restrictions as PQconnectStartParams, PQconnectStart and PQconnectPoll. To initiate a connection reset, call PQresetStart. If it returns 0, the reset has failed. If it returns 1, poll the reset using PQresetPoll in exactly the same way as you would create the connection using PQconnectPoll. PQpingParams # PQpingParams reports the status of the server. It accepts connection parameters identical to those of PQconnectdbParams, described above. It is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connection attempt. PGPing PQpingParams(const char * const *keywords, const char * const *values, int expand_dbname); The function returns one of the following values: PQPING_OK # The server is running and appears to be accepting connections. PQPING_REJECT # The server is running but is in a state that disallows connections (startup, shutdown, or crash recovery). PQPING_NO_RESPONSE # The server could not be contacted. This might indicate that the server is not running, or that there is something wrong with the given connection parameters (for example, wrong port number), or that there is a network connectivity problem (for example, a firewall blocking the connection request). PQPING_NO_ATTEMPT # No attempt was made to contact the server, because the supplied parameters were obviously incorrect or there was some client-side problem (for example, out of memory). PQping # PQping reports the status of the server. It accepts connection parameters identical to those of PQconnectdb, described above. It is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connection attempt. PGPing PQping(const char *conninfo); The return values are the same as for PQpingParams. PQsetSSLKeyPassHook_OpenSSL # PQsetSSLKeyPassHook_OpenSSL lets an application override libpq’s default handling of encrypted client certificate key files using sslpassword or interactive prompting. void PQsetSSLKeyPassHook_OpenSSL(PQsslKeyPassHook_OpenSSL_type hook); The application passes a pointer to a callback function with signature: int callback_fn(char *buf, int size, PGconn *conn); which libpq will then call instead of its default PQdefaultSSLKeyPassHook_OpenSSL handler. The callback should determine the password for the key and copy it to result-buffer buf of size size. The string in buf must be null-terminated. The callback must return the length of the password stored in buf excluding the null terminator. On failure, the callback should set buf[0] = ‘\0’ and return 0. See PQdefaultSSLKeyPassHook_OpenSSL in libpq’s source code for an example. If the user specified an explicit key location, its path will be in conn->sslkey when the callback is invoked. This will be empty if the default key path is being used. For keys that are engine specifiers, it is up to engine implementations whether they use the OpenSSL password callback or define their own handling. The app callback may choose to delegate unhandled cases to PQdefaultSSLKeyPassHook_OpenSSL, or call it first and try something else if it returns 0, or completely override it. The callback must not escape normal flow control with exceptions, longjmp(…), etc. It must return normally. PQgetSSLKeyPassHook_OpenSSL # PQgetSSLKeyPassHook_OpenSSL returns the current client certificate key password hook, or NULL if none has been set. PQsslKeyPassHook_OpenSSL_type PQgetSSLKeyPassHook_OpenSSL(void); 32.1.1. Connection Strings # Several libpq functions parse a user-specified string to obtain connection parameters. There are two accepted formats for these strings: plain keyword/value strings and URIs. URIs generally follow RFC 3986, except that multi-host connection strings are allowed as further described below. 32.1.1.1. Keyword/Value Connection Strings # In the keyword/value format, each parameter setting is in the form keyword = value, with space(s) between settings. Spaces around a setting’s equal sign are optional. To write an empty value, or a value containing spaces, surround it with single quotes, for example keyword = ‘a value’. Single quotes and backslashes within a value must be escaped with a backslash, i.e., ' and \. Example: host=localhost port=5432 dbname=mydb connect_timeout=10 The recognized parameter key words are listed in Section 32.1.2. 32.1.1.2. Connection URIs # The general form for a connection URI is: postgresql://[userspec@][hostspec][/dbname][?paramspec] where userspec is: user[:password] and hostspec is: [host][:port][,…] and paramspec is: name=value[&…] The URI scheme designator can be either postgresql:// or postgres://. Each of the remaining URI parts is optional. The following examples illustrate valid URI syntax: postgresql:// postgresql://localhost postgresql://localhost:5433 postgresql://localhost/mydb postgresql://user@localhost postgresql://user:secret@localhost postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp Values that would normally appear in the hierarchical part of the URI can alternatively be given as named parameters. For example: postgresql:///mydb?host=localhost&port=5433 All named parameters must match key words listed in Section 32.1.2, except that for compatibility with JDBC connection URIs, instances of ssl=true are translated into sslmode=require. The connection URI needs to be encoded with percent-encoding if it includes symbols with special meaning in any of its parts. Here is an example where the equal sign (=) is replaced with %3D and the space character with %20: postgresql://user@localhost:5433/mydb?options=-c%20synchronous_commit%3Doff The host part may be either a host name or an IP address. To specify an IPv6 address, enclose it in square brackets: postgresql://[2001:db8::1234]/database The host part is interpreted as described for the parameter host. In particular, a Unix-domain socket connection is chosen if the host part is either empty or looks like an absolute path name, otherwise a TCP/IP connection is initiated. Note, however, that the slash is a reserved character in the hierarchical part of the URI. So, to specify a non-standard Unix-domain socket directory, either omit the host part of the URI and specify the host as a named parameter, or percent-encode the path in the host part of the URI: postgresql:///dbname?host=/var/lib/postgresql postgresql://%2Fvar%2Flib%2Fpostgresql/dbname It is possible to specify multiple host components, each with an optional port component, in a single URI. A URI of the form postgresql://host1:port1,host2:port2,host3:port3/ is equivalent to a connection string of the form host=host1,host2,host3 port=port1,port2,port3. As further described below, each host will be tried in turn until a connection is successfully established. 32.1.1.3. Specifying Multiple Hosts # It is possible to specify multiple hosts to connect to, so that they are tried in the given order. In the Keyword/Value format, the host, hostaddr, and port options accept comma-separated lists of values. The same number of elements must be given in each option that is specified, so that e.g., the first hostaddr corresponds to the first host name, the second hostaddr corresponds to the second host name, and so forth. As an exception, if only one port is specified, it applies to all the hosts. In the connection URI format, you can list multiple host:port pairs separated by commas in the host component of the URI. In either format, a single host name can translate to multiple network addresses. A common example of this is a host that has both an IPv4 and an IPv6 address. When multiple hosts are specified, or when a single host name is translated to multiple addresses, all the hosts and addresses will be tried in order, until one succeeds. If none of the hosts can be reached, the connection fails. If a connection is established successfully, but authentication fails, the remaining hosts in the list are not tried. If a password file is used, you can have different passwords for different hosts. All the other connection options are the same for every host in the list; it is not possible to e.g., specify different usernames for different hosts.