pg_upgrade docs are confusing if PostgreSQL's versioning system/language isn't known to reader
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/pgupgrade.html Description: If a reader who is unfamiliar with PostgreSQLs versioning (where 9.5 and 9.6 are considered major versions) reads the documentation, it is unclear if they need to use pg_upgrade to migrate from 9.5 to 9.6, for example. The documentation says upgrading from 9.6.3 to the current major release requires pg_upgrade, but not from 9.6.2 to 9.6.3. The problem with that language is that the current release of PostgreSQL is 10. So is pg_upgrade required to upgrade from 9.6.3 to current (10) because 9 and 10 are major versions or because 9.6 and 10.0 are major versions? (the latter). It would be clearer if the documentation covered all three cases: 9.6.3 - 10.0.0 and 9.5.1 - 9.6.3: pg_upgrade should be used 9.6.2 - 9.6.3: pg_upgrade not needed Or if the documentation simply noted that the second decimal is considered a major release. Thanks for PostgreSQL! Jim
Missing column_constraint explanation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/sql-altertable.html Description: Missing column_constraint explanation in parameters section
titles dont have documentation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-syntax.html Description: I didnt see documentation in postgresql title
SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/sql-select.html Description: In the SELECT statement page the argument type of the (FOR SHARE/UPDATE) OF clause is listed to be a table_name. This is not *quite* accurate - it should reference the *alias* assigned to the table if one was given. The distinction is subtly important, as without this information the documentation implies that the choice of rows to lock can only be done per-table (i.e. that in a query mentioning the same table twice, *any* tuples being pulled from that table would be given the same treatment). But in fact postgres supports specifying the locking behaviour per-alias, which is a really powerful ability. And actually, trying to specify it by actual "table name" where an alias has been assigned won't work either. robert.
Wrong example
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/7.1/static/functions-math.html Description: The last row of table Table 4-4. Mathematical Functions seems to be wrong at example column, current value trunc(numeric, s integer) numeric truncate to s decimal places round(42.4382, 2) 42.43 Possible solution trunc(numeric, s integer) numeric truncate to s decimal places trunc(42.4382, 2) 42.43
Appendix A. PostgreSQL Error Codes
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/errcodes-appendix.html Description: Hi Would it be possible to present the codes in ascending sequence? This applies to all releases. Thanks
\i and \ir separated by \if now...
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/app-psql.html Description: While I get that we want alphabetical order an exception for \i and \ir seems warranted; or maybe make \ir part of the \i meta command description - \i[r] or \include[_relative] David J.
json_to_record Example
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/functions-json.html Description: Hi team, I had the following issue when going through your https://www.postgresql.org/docs/current/static/functions-json.html docs. Looking at the json_to_record example it took me quite a while that it is not possible to put the json_to_record function right after the the from clause but instead I would need to put the tables name in front, then use the json_to_record function. Then put the column definitions behind it and in the SELECT clause I need to query the columns using the alias. As you use a * in your examples, I assumed that json_to_record returns all values found in the json argument of that function. As an idea I would suggest to provide a sample json which contains key-value pairs as well as arrays and use this for the whole examples as someone would rather not query a json written by hand. Thank you very much and keep up the good work! I hope you understand and like my suggestion! Best regards, Yoshi
pg_stat_statements opening claim is potentially misleading
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/pgstatstatements.html Description: At the top of the pg_stat_statements docs page[1] it states: "The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server." This is not strictly true. In cases where large numbers of queries are cancelled, such as by `statement_timeout` on a loaded server, they are not in fact recorded by pg_stat_statements. This has been brought up on the mailing list [2] and has been stated as the intended behavior. This can be surprising particularly if cancelled queries account for a large amount of server execution time in pathological cases. Perhaps a simple change such as this would help clarify this: "The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements successfully executed by a server." Or an explicit callout that cancelled queries do not count regardless of how much execution time they actually use. -Casey [1] https://www.postgresql.org/docs/10/static/pgstatstatements.html [2] https://www.postgresql.org/message-id/2017111811.1464.28388%40wrigleys.postgresql.org
Missing documentation for FETCH FIRST in chapter 7.6
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/queries-limit.html Description: The documentation of the SELECT statement lists FETCH FIRST/OFFSET as an alternative to the proprietary LIMIT clause. https://www.postgresql.org/docs/current/static/sql-select.html#SQL-LIMIT However, chapter 7.6 about LIMIT/OFFSET does not mention the alternative. https://www.postgresql.org/docs/current/static/queries-limit.html
Crash when using PQgetvalue
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/libpq.html Description: Hello, I am not sure if I am doing something wrong or there is a bug in the code but I am getting a strange issue. When I try to run the following code I get a crash when attempting to accessing the values using PQgetvalue. I was able to print off all the column names using PQfname. If I replace PQgetvalue with PQgetlength the correct rows, fields and lengths are printed. Checking PQgetisnull displayed the value as not null. I also did a few checks using PQstatus and PQresultStatus to make sure the connection or result set didn't mess up somehow in the middle of the code. PGconn *con; PGresult *pg_result; int i = 0; int j = 0; int cols = 0; int rows = 0; con = PQconnectdb("host=localhost user=postgres password=test dbname=test"); if(PQstatus(con) != CONNECTION_OK) { printf("Could not connect to PostgreSQL database!"); fflush(stdout); } else { g_print("Connected.\n"); pg_result = PQexec(con, "SELECT * FROM test"); if(PQresultStatus(pg_result) == PGRES_TUPLES_OK) { rows = PQntuples(pg_result); cols = PQnfields(pg_result); for(i = 0; i < rows; ++i) { for(j = 0; j < cols; ++j) { printf("Row: %d - Col: %d = %s\n", i, j, PQgetvalue(pg_result, i, j)); //This is where the crash occurs. Also happens if not attempting to print and simply getting the value or calling the function. fflush(stdout); } } } PQclear(pg_result); PQfinish(con); con = NULL; }
ALTER
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/tutorial-sql.html Description: where is ALTER?
40 commands are missing. inserted or missing character?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/queries.html Description: try checking your XML syntax. missing attribute?
Isn't "publication" wrongly defined here?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/logical-replication-publication.html Description: In section 31.1 it says "A publication is a set of changes generated from a table or a group of tables"... that seems wrong. It's a set of relations, tables, whatevers. No the changes themselves. Right?
Documentation for CommandComplete is incorrect for CREATE TABLE with server version 10.2
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/protocol-message-formats.html Description: On this page: https://www.postgresql.org/docs/10/static/protocol-message-formats.html The documentation says: For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows is the number of rows retrieved. But what I get back from the server is this packet: 43 00 00 00 11 43 52 45 41 54 45 20 54 41 42 4c CCREATE TABL 0010 45 00E. The query I sent to the server was: create table table1(i int)
Autocommit with Postgres 10
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/ecpg-sql-set-autocommit.html Description: Hi, I am currently using PG10, and link my C program to the libpq.5.10.so file for client feature usage. I get the following error after trying to set auto commit (as I did before) : 2018-05-03 11:16:50.253 CEST [50141] ERROR: unrecognized configuration parameter "autocommit" 2018-05-03 11:16:50.253 CEST [50141] STATEMENT: SET AUTOCOMMIT TO ON Could you tell me how to set autocommit to ON with PG 10 client library ? Thank you
Regarding built-in logical replication
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/logical-replication.html Description: I am referring to link 'https://www.postgresql.org/docs/10/static/logical-replication.html' and it has been mentioned that 'Replicating between different major versions of PostgreSQL.' under the section of 'The typical use-cases for logical replication are:' but I don't think logical replication using PUBLICATION/SUBSCRIPTION model is possible between different PostgreSQL versions, which is only possible using pglogical.
Describe UNION's cast on derived table
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/typeconv-union-case.html Description: Hi! Whilst the type conversion works great on the query ;select 10 as col1 union select null as col1;, it does not on the almost same one ;select col1 from (select 10 as col1) t1 union select col1 from (select null as col1) t2;, giving the error "UNION types integer and text cannot be matched". I'm using a 64-bit PostgreSQL 10.1 on linux. Please, describe this situation better on the docs. Thank you so much.
typo: overlay
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.4/static/functions-string.html Description: https://www.postgresql.org/docs/9.4/static/functions-string.html in the overlay example, there should only be 'xxx' instead of ''. (the 'xxx' is being replaced with 'hom'.)
What does "Table rewrite" mean?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/sql-altertable.html Description: I see references to a "table rewrite" all over the place, but I cannot find one single definition on what that actually means. What does a table rewrite do? Does it drop and recreate the table? Everywhere I look people describe it with fear and trepedation as if it was some catastrophically dangerous operation to perform. What is it?
v10 changes to pg_ctl --wait
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/devel/static/app-pg-ctl.html Description: The docs for --wait still say: "When waiting for startup, pg_ctl repeatedly attempts to connect to the server." I believe it's been changed to examine postmaster.pid instead (thanks Tom!).
estudent
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/tutorial-sql.html Description: Programação Java
Windows instructions are incomplete
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/pgupgrade.html Description: In set 9. Run pg_upgrade: It says that windows users should execute this: RUNAS /USER:postgres "CMD.EXE" This step does not work unless you create a user called postgres in windows user management. Furthermore, the instructions that follow do not work unless you: 1. Add the user to the administrators user group. 2. Give "full control" permission to the postgresql data folders on both the old and new installations. I have also found that it is easier to add localhost with "trust" in the pg_hba.conf while you do the upgrade. Once completed, remove it again.
A simple question
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html Description: Hello, my client has a plethora of reserved words in their PostgreSQL database using Greenplum. They don't wish to use the " " to allow for them to remainmy question is, do you have a parameter I can set to bypass PostgreSQL from flagging the reserved word all together? Thank you so very much. Xavier californiasai...@yahoo.com
Update encryption options doc for SCRAM-SHA-256
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/encryption-options.html Description: Section "18.8. Encryption Options" only mentions MD5 as the password storage encryption mechanism, although PostgreSQL 10 introduced the superior SHA256 - somebody looking at the docs would get a bad idea of PostgreSQL's capabilities...
Developer
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/gin-intro.html Description: fff
information_schema.applicable_roles behavior does not match documentation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/infoschema-applicable-roles.html Description: The documentation for applicable_roles says: "The current user itself is also an applicable role." (see https://www.postgresql.org/docs/current/static/infoschema-applicable-roles.html) My understanding is that the current user should be listed in information_schema.applicable_roles, but a quick check shows that it is not. marc=> select * from information_schema.applicable_roles; grantee | role_name | is_grantable -+---+-- marc| foo | YES (1 row) It properly shows the role I am a member of, but not myself. By contrast, the docs for enabled_roles also say that it includes the current user, and it actually does: marc=> select * from information_schema.enabled_roles; role_name --- foo marc (2 rows) This also isn't a user vs role issue. If I "set role foo", it is no longer visible in applicable_roles.
Need PostgreSQL Key words list
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-keywords-appendix.html Description: Hi, Some people developing code formatting/highlighting plugins use this table as the list of PostgreSQL key words: https://www.postgresql.org/docs/current/static/sql-keywords-appendix.html However, that list isn't complete, eg. TIMESTAMPTZ, JSON, UUID... are missing, probably because these aren't in SQL standard. Would you please consider having a full list of PostgreSQL Key words, either in that table, or somewhere else? Thanks
Documentation of EXCEPT ALL may have a bug
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/queries-union.html Description: I believe that the documented behavior of EXCEPT is not in agreement with Postgres behavior (I'm using Postgres 9.5). The documents say: EXCEPT returns all rows that are in the result of query1 but not in the result of query2. (This is sometimes called the difference between two queries.) Again, duplicates are eliminated unless EXCEPT ALL is used. Here is a test script: drop table if exists t; drop table if exists u; create table t(x int); create table u(x int); insert into t values (1), (2), (2), (3), (3); insert into u values (1), (2); select * from t except select * from u; select * from t except all select * from u; And here is the output: DROP TABLE DROP TABLE CREATE TABLE CREATE TABLE INSERT 0 5 INSERT 0 2 x --- 3 (1 row) x --- 3 3 2 (3 rows) The output from EXCEPT matches the documented behavior. The output from EXCEPT ALL makes sense to me, but I think it is at odds with the documentation: "EXCEPT returns all rows that are in the result of query1 but not in the result of query2." This general statement is then modified by the discussion of EXCEPT ALL. That first sentence does not deal with duplicates in the input tables, and just discusses set membership. Each occurrence of (3) in query1 is therefore kept. For EXCEPT, the duplicates are eliminated (yielding the output [3]), and EXCEPT ALL should therefore yield [3, 3]. In the EXCEPT ALL case, both occurrences of (2) in query1 should be eliminated by the one occurrence in query2. I think this is a fair interpretation based on the wording. To match the observed behavior, I think that the description of EXCEPT ALL needs to be modified to something like this: EXCEPT ALL returns those rows of query1 in excess of matching rows in query2, as well as rows of query1 that have no match in query2.
complex documentation and hard to find the reference
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/functions-json.html Description: with the prior experience of mysql or mongoDb or any other. Documentation can be understood. But without any experience of those, hard to try the JSONB data type. Specify operand places in a select query (give the entire example query).
.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html Description: .
Engineering
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.3/static/runtime-config-connection.html Description: Engineering The Future
Functions in sort order - undocumented restriction
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/queries-order.html Description: This works: select distinct o.bid, organisation, posttown, replace(case when phone ='' then null else trim(phone) end, ' ', ''), phone, o.active, website, email, (select count(*) from notes as n where n.bid = o.bid) as nn from organisations as o right join notes as n on o.bid = n.bid where true order by replace(case when phone ='' then null else trim(phone) end, ' ', '') nulls last ; This does not work: select distinct (o.bid), organisation, posttown, replace(case when postcode ='' then null else trim(postcode) end, ' ', '') as pc, phone, o.active, website, email, (select count(*) from notes as n where n.bid = o.bid) as nn from organisations as o right join notes as n on o.bid = n.bid where true order by replace(case when phone ='' then null else trim(phone) end, ' ', '') nulls last ; ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ...n notes as n on o.bid = n.bid where true order by replace(ca... The documentation does not explain the restriction, nor that, or why, you cannot use 'as' to rename the field and then cite the renamed version.
Jr Software Engineer
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/7.0/static/security.htm Description: Learning PostgreSQL
dblink function description lacks security model
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.1/static/contrib-dblink-function.html Description: There is no information about what permissions should be setup for the user who wants to query a database through a dblink. Thank you to explicit the security model or refer to the right chapter applicable to dblinks. Regards, E.D.
prepared transactions improvements
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.3/static/sql-prepare-transaction.html Description: Under PREPARE TRANSACTION it says that transaction_id must be unique, it doesn't say what happens when it is not unique. It might be useful to add a note mentioning max_prepared_transactions needs to be configured before prepared transactions are enabled, although there's already a note saying to leave it at 0 if prepared transactions are not required. Under COMMIT PREPARED and ROLLBACK PREPARED it should also say what happens if transaction_id does not exist. Thanks, Jim
Pltcl install
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/pltcl-overview.html Description: Good morning, I already installed postgreSQL v:9.5 on my PC and I want to install pltcl language to write external procedure that are used to copy file from path to another. How can I install pltcl? Is possible to copy a file from tcl procedure and can this be called from not SU user? Thanks, Thomas
Support Inquiry
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/8.1/static/backup.html Description: Greetings, My name is Jorge Castro, I am an support engineer from Google Cloud Premier Partner in Mexico, we have a potencial client which uses your EnterpriseDB Postgres Services. They are currently migrating to Google Cloud, and there's already one "premade" solution in the Google Cloud Launcher with the EnterpriseDB licencing, but we want to make testing in the Cloud SQL, with the Postgres Beta Service. While migrating there was an error which couldn't help finish the import. I assume the EnterpriseDB is a paid licenced service and the SQL dump files have some particular details that doesn't allow to fulfill the client request. While searching in logs i got these messages: "FATAL: pg_hba.conf rejects connection for host "201.149.36.242", user "postgres", database "cloudsqladmin", SSL off" Also: "ERROR: unrecognized configuration parameter "edb_redwood_date"" The file i'm trying to import is a EnterpriseDB postgres sql file (version 9.6.6.11) and wanted to migrate to Cloud SQL Postgres (9.6), While reviewing the sql dump file i find redwood_date as an enterprise DB parameter for date, (i suppose this might be corrected if deleted). Do delete this parameter could solve this? Or do i have to ask client to export a new dump file? Does the EnterpriseDB can export a regular Postgres file? Why is this config file (which i believe is on the on premise server and not in the SQL dump file) affecting this Postgres import, since it's being done with a dump file and not transfered directly from the server? I hope you can help us so we as partners can recommend your solutions alongside with Google Cloud's. Thanks in advance for your assistance, Jorge C.Greetings, My name is Jorge Castro, I am an support engineer from Google Cloud Premier Partner in Mexico, we have a potencial client which uses your EnterpriseDB Postgres Services. They are currently migrating to Google Cloud, and there's already one "premade" solution in the Google Cloud Launcher with the EnterpriseDB licencing, but we want to make testing in the Cloud SQL, with the Postgres Beta Service. While migrating there was an error which couldn't help finish the import. I assume the EnterpriseDB is a paid licenced service and the SQL dump files have some particular details that doesn't allow to fulfill the client request. While searching in logs i got these messages: "FATAL: pg_hba.conf rejects connection for host "201.149.36.242", user "postgres", database "cloudsqladmin", SSL off" Also: "ERROR: unrecognized configuration parameter "edb_redwood_date"" The file i'm trying to import is a EnterpriseDB postgres sql file (version 9.6.6.11) and wanted to migrate to Cloud SQL Postgres (9.6), While reviewing the sql dump file i find redwood_date as an enterprise DB parameter for date, (i suppose this might be corrected if deleted). Do delete this parameter could solve this? Or do i have to ask client to export a new dump file? Does the EnterpriseDB can export a regular Postgres file? Why is this config file (which i believe is on the on premise server and not in the SQL dump file) affecting this Postgres import, since it's being done with a dump file and not transfered directly from the server? I'm still reviewing and saw an article in which a pg_dump file and pg_restore are mentioned as a must so i'm confused if i need another file or if i need to set a new file in the Cloud SQL Postgres instance. I hope you can help us so we as partners can recommend your solutions alongside with Google Cloud's. Thanks in advance for your assistance, Jorge C.
Basic security
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/8.3/static/auth-pg-hba-conf.html Description: My pg_hba.conf file gives 'cert' as an authentication method. this is not mentioned on this page. I think a basic pg_hba.conf to allow remote access require ssl, and to prevent access to the postgres table would be a useful addition. The more I see about this powerful environment the more nervous I get about exploits based on aspects of it's multitude of features of which I am completely unaware - what about PUBLIC for example ? ? A basic security guide to disable dangerous defaults would be very welcome
postgres_fdw incomplete examples
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/postgres-fdw.html Description: The examples about postgres_fdw do not tell about the need to have a HANDLER when we want to access foreign tables. There should be indications on how to build a handler, especially on loopback FDW (postgres-to-postgres like dblink).
The example for creating a check constraint is missing a comma
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-createtable.html Description: See the example from the documentation for CREATE TABLE below. There should be a comma before the CONSTRAINT keyword. CREATE TABLE distributors ( did integer, namevarchar(40) CONSTRAINT con1 CHECK (did > 100 AND name <> '') );
pg_hba_file_rules permission issue
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/view-pg-hba-file-rules.html Description: Hi, According to the documentation, I assume normal users will be able to view pg_hba_file_rules once they are granted select privileges. But for the privileged user it's giving following error while trying to view records: ERROR: permission denied for function pg_hba_file_rules SQL state: 42501 Thanks, Joby
Rolycore
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.1/static/backup.html Description: I am Rolycore
test
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.3/static/tutorial-start.html Description: test
pg_dumpall examples may lead to encoding problems
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/app-pg-dumpall.html Description: The example to dumb all databases at the bottom of the page pg_dumpall db.out works, but I think it would be better to suggest pg_dumpall -f db.out instead, as it uses the databases encoding for the db.out file. Using the current example on Windows led to the db.out file being in the wrong encoding and not working to restore the database.
J.2. Tool Sets/Appendix J. Documentation missing package
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/0.0/static/docguide-toolsets.html Description: In J.2.3 Debian Packages "docbook" and "dbtopub" missed in install command. make world works after above to package installed.
public schema
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/ddl-schemas.html Description: cite 5.8.3. The Schema Search Path There is nothing special about the public schema except that it exists by default. end cite It's not true. Also special that is public schema is opened for all users by default.
Docs for version 10 incorrectly claim that ~/.pgpass with 0600 perms work.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html Description: https://www.postgresql.org/docs/10/static/libpq-pgpass.html $ dpkg -l | grep -i postgres ii libpq5:amd6410.4-0ubuntu0.18.04 amd64PostgreSQL C client library ii postgresql-client 10+190 all front-end programs for PostgreSQL (supported version) ii postgresql-client-1010.4-0ubuntu0.18.04 amd64front-end programs for PostgreSQL 10 ii postgresql-client-common190 all manager for multiple PostgreSQL client versions .pgpass does not work with 10.4 /home/ubuntu/.pgpass is 0600 (tried 0400 alsp), owner and group ubuntu with psql run as ubuntu. I tried adding an extra carriage return at the end of the file. Using psql with a -w, I get `psql: fe_sendauth: no password supplied`. Removing the -w, the error message is `psql: FATAL: password authentication failed for user "ubuntu"` after entering a password. I'm using the format from the doc page, `hostname:port:database:username:password`. There are no special characters in the user or pass (or anywhere). The docs are wrong, apparently this has been deprecated.
dblink_error_message return value
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/contrib-dblink-error-message.html Description: Documentation says: Return Value Returns last error message, or an empty string if there has been no error in this connection. Which is invalid. Actually it returns 'OK' string if no error was raised. Secondly dblink_is_busy must be first called to make dblink_error_message returns an error message. (Tested on 9.6.9)
Global dict name is listed inconsistently
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/plpython-sharing.html Description: Page https://www.postgresql.org/docs/10/static/plpython-sharing.html says: "The global dictionary SD is available to store data between function calls." few lines lower it says: "global data and function arguments from myfunc are not available to myfunc2. The exception is the data in the GD dictionary, as mentioned above." Note that the global dictionary is named "SD" in the first quote and "GD" in the second one. Please correct either first or second sentence. Kind regards, Adam Bielański Happy PostgreSQL user :)
Documentation does not cover multiple WITH in one query
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/queries-with.html Description: Hi. I have found [DOC](https://www.postgresql.org/docs/10/static/queries-with.html) it is fine, but did not cover multiple WITH in one query. I found answer only [here](https://stackoverflow.com/a/38137037/4632019) May you please add the example: WITH table1 AS (...), table2 AS (...) SELECT * FROM table1, table2 thank you
Document the limit on the number of parameters
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-prepare.html Description: The backend protocol limits the number of parameters that can be passed for a prepared statement to 32767 (2 byte signed integer). As that is something that is independent of the client library, I think this should be documented in the user facing manual for the Postgres server somewhere. I did not find anything in the chapter about PREPARE https://www.postgresql.org/docs/current/static/sql-prepare.html or about the backend protocol: https://www.postgresql.org/docs/current/static/protocol.html nor on the FAQ page: https://wiki.postgresql.org/wiki/FAQ
sql-set-session-authorization
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/sql-set-session-authorization.html Description: https://www.postgresql.org/account/comments/new/9.6/sql-set-session-authorization.html/ You should also mention necessary rights required to switch as lower privileged user.
decimal digits precision for real and double precision types
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/datatype-numeric.html Description: In table 8.2 for numeric types, when the documentation states "6 decimal digits precision" for the real type, we are talking about 6 digits after the decimal point or 6 digits in total (before and after)?
Parameter types are inferred from context of first use only.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-prepare.html Description: Background can be found on the bugs list (pgsql-bug #15289). It was explained that when a prepared statement parameter needs to have its type inferred then, should the parameter be used more than once, only the first use is considered. (The example in that report is that the type of $1 can be inferred from "($1 = col) or ($1 is null)" but not from "($1 is null) or ($1 = col)".) The documentation as it stands says only that the parameter's type is inferred from the context in which it is used. The "first use only" proviso is not mentioned (hence "context" isn't so broad as to consist of all of the expressions in which the parameter appears).
PostgreSQL 9.5.14 Documentation /Chapter 28 / 28.1. Determining Disk Usage
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/disk-usage.html Description: PostgreSQL 9.5.14 Documentation / Chapter 28. Monitoring Disk Usage / 28.1. Determining Disk Usage (Applies for other Versions, too.) I'm wondering if the two statements given showing the index space size as described. I've the impression they show the space size of the tables. Regards --Michael Extract of the manual: You can easily display index sizes, too: SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'customer' AND c.oid = i.indrelid AND c2.oid = i.indexrelid ORDER BY c2.relname; relname| relpages --+-- customer_id_indexdex | 26 It is easy to find your largest tables and indexes using this information: SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; relname| relpages --+-- bigtable | 3290
Extend example of JOIN results for completeness
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/queries-table-expressions.html Description: Hi The section: https://www.postgresql.org/docs/10/static/queries-table-expressions.html#QUERIES-FROM shows result of INNER, LEFT and RIGHT joins. That worth nothing to put results of FULL join for completeness but would really informative.
Extend phrase by an example
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/rowtypes.html Description: Hi. On the page: https://www.postgresql.org/docs/10/static/rowtypes.html >then the same inventory_item composite type shown above would come into being as a byproduct, and could be used just as above. Note however an important restriction of the current implementation: since no constraints are associated with a composite type, the constraints shown in the table definition do not apply to values of the composite type outside the table. (A partial workaround is to use domain types as members of composite types.) The part: >(A partial workaround is to use domain types as members of composite types.) From here I can understand that workaround is possible. But what is workaround? and why it is partial? Would be great to have this description.
please inform data_directory
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/functions-admin.html Description: It is impossible to use pg_read_file(), etc. functions without SHOW data_directory
Typo in doc or wrong EXCLUDE implementation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-createtable.html Description: Hi. https://www.postgresql.org/docs/current/static/sql-createtable.html#sql-createtable-exclude If all of the specified operators test for equality, this is equivalent to a UNIQUE constraint Exclusion constraints are implemented using an index ALTER TABLE person add constraint person_udx_person_id2 EXCLUDE USING gist ( person_id WITH = ) ; tucha=> ALTER TABLE "person_x_person" ADD CONSTRAINT "person_x_person_fk_parent_person_id" tucha-> FOREIGN KEY ("parent_person_id") tucha-> REFERENCES "person" ("person_id") tucha-> ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE; ERROR: there is no unique constraint matching given keys for referenced table "person" because gist does not support unique indexes, I try with 'btree' ALTER TABLE person add constraint person_udx_person_id2 EXCLUDE USING btree ( person_id WITH = ) ; \d person ... "person_udx_person_id2" EXCLUDE USING btree (person_id WITH =) tucha=> ALTER TABLE "person_x_person" ADD CONSTRAINT "person_x_person_fk_parent_person_id" tucha-> FOREIGN KEY ("parent_person_id") tucha-> REFERENCES "person" ("person_id") tucha-> ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE; ERROR: there is no unique constraint matching given keys for referenced table "person" Why postgres does not add unique flag. Despite on: "this is equivalent to a UNIQUE constraint" I thought it should be: "person_udx_person_id2" UNIQUE EXCLUDE USING btree (person_id WITH =) PS. > For example, you can specify a constraint that no two rows in the table contain overlapping circles (see Section 8.8) by using the && operator. Also I expect that this: ALTER TABLE person add constraint person_udx_person_id EXCLUDE USING gist ( person_id WITH =, tstzrange(valid_from, valid_till, '[)' ) WITH && ) also should raise UNIQUE flag for exclusion thus we can use it in FK
Multivariate statistics
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/multivariate-statistics-examples.html Description: Hi All, I'm a little confused by the documentation here: https://www.postgresql.org/docs/10/static/multivariate-statistics-examples.html. In particular, the selectivity of the second query doesn't seem to match up with what is expected in the notes. I feel like I may be missing something? (I am referring to the output of this explain command: EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;)
RETURN NULL in the sample of insert trigger on partitioned table
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html Description: Why in provided samples of "insert" triggers on partitioned table at the end of the triggers you provide "return NULL" not "return NEW"? This causes, among other things, problems with the GET DIAGNOSTICS clause var = ROW_COUNT clause.
A cosmetic suggest for 4.2.7. Aggregate Expressions
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-expressions.html Description: Documentation for PostgreSQL 10.4 4.2.7. Aggregate Expressions ... aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] ... The first form of aggregate expression invokes the aggregate once for each input row. The second form is the same as the first, since ALL is the default. ... Since both definitions are the same will be better rewrite to aggregate_name ([ALL ]expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] to emphasize this.
Incomplete documentation for pg_restore option
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/app-pgrestore.html Description: Hi Team, The documentation for pg_restore doesn't mention the syntax to restore from a directory based back up created using -F d option in pg_dump. Could you please add this example to the documentation ? pg_restore -h host -p port -U user -d db_name Please let me know in case of any issues Thanks, Srini
Can we only add values to enums?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/static/sql-altertype.html Description: I need to add a value to an enum, so I found this page, which answered my question. However, I found it quite confusing that it only documents adding a value. I would expect it to also document how to remove a value from an enum? Is this not possible? If not, I consider it a bug. But either way, if it is not possible, you should explicitly document it on this page.
Synopsis of SELECT statement: UNION, INTERSECTION, EXCEPT
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-select.html Description: The SYNOPSIS section of the "SELECT" SQL command contains the line [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] (with a boldface "select"), but it is not clear what is meant by that "select". Further down the page, in the "UNION clause" section (and also INTERSECTION or EXCEPT), it is written: select_statement UNION [ ALL | DISTINCT ] select_statement which uses boldface "select_statement" instead of boldface "select" as in the synopsis. This is confusing. It would be great if the boldface "select" in the synopsis could be better defined.
Problems with pg_upgrade under Windows
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/pgupgrade.html Description: The postgresql service (since 9.x?) runs under Windows with the integrated NetworkService account and not with a dedicated postgres account anymore (at least by using the EnterpriseDB installer). You can't start a shell as NetworkService (which seems not to be necessary). But the owner and permissions of the data directory should be checked and changed afterwards if necessary. Another problem (not directly of pg_upgrade) is, that pg_upgrade internally uses xcopy.exe to copy the pg_clog/pg_xact directory. Now, if somebody redirects the standard output of pg_upgrade.exe but NOT the standard input, xcopy failes silently and the pg_xact directory (or files within) are missing. This is a problem of xcopy.exe, apparently fixed on newer versions of Windows but existent under Windows 7 x64. After many hours of testing and searching, I finally found the cause of the failure here: http://social.msdn.microsoft.com/Forums/en-US/netfxbcl/thread/ab3c0cc7-83c2-4a86-9188-40588b7d1a52/ I can confirm, that redirecting also the standard input of pg_upgrade.exe solves the problem.
Dead link in ltree documentation
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/ltree.html Description: Hi, https://www.postgresql.org/docs/current/static/ltree.html links to www.dmoz.org which now returns a 403, since being closed down in 2017. Maybe it could link to the mirror https://dmoztools.net/ or the wikipedia page instead.
how does jsonb_set work?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/functions-json.html Description: the documentation is missing some information how does the jsonb_set function work? does it create a whole new json and updates it in the column? or it actually only updates the specific key in "path" thanks
why is there no CSVQL?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/features.html Description: CSV files can be huge. most commercial spreadsheets cannot handle it. there are high-volume transactions such as for tax reporting which need a separate copy (user can handle that) with columns dropped, maybe renamed (yeah, ALTER shouldn't be so hard to use), and an SQL or SQL-like language for this would be excellent.
PQdescribePrepared / binary data
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/libpq.html Description: It should be mentioned, that the function PQfmod is not applicable for a valid PGresult returned by PQdescribePrepared. PQfmod delivers additional information for datatypes of variable size, like maximum size for varchar or precision/scale for numeric. This information is currently not available for parameters of prepared statements. On the other hand, for example: users of the binary interface should not experience any errors when providing correct binary data for a numeric parameter: if the specified scale does not match with the scale of the related numeric column in the database table, then postgres will handle it correctly and round it as needed.
Getting started guide does not start from first step
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.0/static/tutorial-createdb.html Description: I'm new to PostgreSQL and following the getting started guide. I expected it to start from zero, but it assumes the reader already has a database up and running and is already connected to it. 1.3 Creating a database I got here and tried the command `createdb mydb`, but it didn't work. I had connected to the database (sudo su - postgres) and thought that was the only step because the command prompt was now `postgre@username`. After quite a bit of beginner searching on the internet, I found I then had to type `psql` to actually connect. It'd be really great for new users if the Getting Started guide was more of a walkthrough that contained all the steps--even those little ones of getting connected that experienced users overlook.
Extra comma in documentation of array_fill
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/functions-array.html Description: In table 9.49 (https://www.postgresql.org/docs/10/static/functions-array.html#ARRAY-FUNCTIONS-TABLE) there is an extra comma in the syntax for array_fill. "array_fill(anyelement, int[], [, int[]])" should be "array_fill(anyelement, int[] [, int[]])"
PostgreSQL user
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/server-start.html Description: PostgreSQL user
Correction for 9.6 documentation for OpenBSD
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/server-start.html Description: OpenBSD 6.1 i386 PostgreSQL 9.6 In Section 18.3. Starting the Database Server, I did something different from the documentation. Instead of editing the file /etc/rc.local, as the documentation indicated, I ran the command doas rcctl set postgresql status on That command changed the file /etc/rc.conf.local. When I rebooted, the PostgreSQL server was running.
pg_hba.conf requires a list of supported subnets that can be used
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html Description: pg_hba.conf requires a list of supported subnets that can be usedsuch as 0, 12, 16, 20, 24, 32, 64. note that 32 doesn't work as our CIO/CTO/SYSADMIN stated today.
Error with pg_hba.conf, trust flags error
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html Description: setting pg_hba.conf with any of the example connection securty types, its states that the connection couldn't be use when setting "trust" (without quotes) as the auth-method, any other value besides "md5"
coder
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/cube.html Description: student
Wrong 'Special local variables PG_' prefix in 'Trigger procedures' section
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/plpgsql-trigger.html Description: The documentation (10 and 9.6) contains a typo when mentioning 'Special local variables PG_something'. Instead it should be 'Special local variables TG_something' as it is for trigger local variables. Examples just below all start with TG_ so it should be obvious.
doc
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/tutorial-start.html Description: document
json_populate_* functions have extra undocumented parameter
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/functions-json.html Description: The json_populate_record and json_populate_recordset functions have an extra optional undocumented boolean parameter. According to https://w3resource.com/PostgreSQL/postgresql-json-functions-and-operators.php the extra parameter is called "use_json_as_text".
prog
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/tutorial-install.html Description: prog
Extra indentation in first line
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/textsearch-features.html Description: Hello, In some queries there is an extra indentation. For example: SELECT to_tsquery('fat') <-> to_tsquery('cat | rat'); ?column? --- 'fat' <-> 'cat' | 'fat' <-> 'rat' But there are no indentation (it's correct) here: SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases WHERE ''a & b''::tsquery @> t'); ts_rewrite 'b' & 'c' It seems that there are no such indentations within .sgml files.
PostgreSQL
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/tutorial-install.html Description: PostgreSQL Ok
type point
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.4/static/datatype-geometric.html Description: I think that the information on points should contain a sentence like this: "Order is Longitude, Latitude - so if you plot it as the map, it is (x, y)." [1] https://www.postgresql.org/docs/current/static/datatype-geometric.html [2] https://stackoverflow.com/a/47396542/1602492
CURSOR WITH HOLD does not state the memory/disk implications
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-declare.html Description: CURSOR WITH HOLD will consume memory and or disk
The DROP TABLE instruction should have a TEMP option for when a temporary table
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.3/static/sql-createtable.html Description: Hi, There is an obvious "issue" with temporary tables. Case description: We have a public table public."myTooImportantTable" Then you have a temporary table: ;CREATE TEMP TABLE "myTooImportantTable" The issue is related to the intention of drop the temporary table: ;DROP TABLE "myTooImportantTable" -- <--- this drop the "myTooImportantTable" ;DROP TABLE "myTooImportantTable" -- <--- this drop the public."myTooImportantTable"
Documentation for create unique index is insuficient and (because of that) incorrect
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/static/indexes-unique.html Description: In https://www.postgresql.org/docs/11/static/indexes-unique.html there are omited clausules For example WHERE clausule is omited. In https://www.postgresql.org/docs/11/static/sql-createindex.html you can see the right sintax with all the clausules.
COPY on partitioned table
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/static/ddl-partitioning.html Description: "If you want to use COPY to insert data, you'll need to copy into the correct child table rather than directly into the master." What the reasons for this requirement? It requires clarification. COPY to master table will fail? May be performance reasons? May be this sentence already obsolete?
ALTER SERVER SYNTAX ISSUE
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-alterserver.html Description: I found that the alter server statement below does not work: ALTER SERVER foo OPTIONS (host 'foo', dbname 'foodb'); but this does: ALTER SERVER foo OPTIONS (set host 'foo', set dbname 'foodb');
Tipos de datos enteros
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/static/datatype.html Description: Por favor, seria posible incluir el tipo de datos TINYINT, Es el tipo de datos numérico más chico dentro del lenguage SQL, y su utilización siendo escasa para la mayoría de las operaciones, se debe a que no puede almacenar números negativos, solamente del 0 al 255, por lo tanto obliga a tener bien conocido los límites cuando realicemos operaciones que sus resultados se guarden en este tipo de datos. A mi entender es muy importante este tipo de datos, ya que, ocupa la mitad de la memoria que usa SMALLINT, este tipo de dato es muy usado para las tablas que no requieren mas de 255 registros, tales como; Categoría, Departamento, Característica, Clasificación, etc. Saludos
Phrasing to consider (non-technical)
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/static/datatype-datetime.html Description: On the page for Data Types - DateTime, there is this part of a sentence: "so the same time zone names are also recognized by much other software." The phrase of "much other software" is what caught me. An optional replacement would be: "many other software systems." or "multiple software packages." "much" has a connotation that doesn't fit here as well as "many" or "multiple" would. Thanks for the documentation. It has been very helpful in clarifying some things. --Kevin.
The 'replication' keyword
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/auth-pg-hba-conf.html Description: I've been setting up logical replication today and I had a lot of trouble getting it to work. The main issue I ran into was that I mistakenly had the database set to "replication," thinking that logical replication required that keyword the same way physical replication does. When you do this, and then try to create a subscription, you get an error like: ERROR: could not connect to the publisher: FATAL: no pg_hba.conf entry for replication connection from host "73.71.60.29", user "testreplicator", SSL on FATAL: no pg_hba.conf entry for replication connection from host "73.71.60.29", user "testreplicator", SSL off Hm. Somehow you can't connect. Well, it's because you are trying to do logical replication and the only connection config you have is for physical replication. The error message isn't great, and so I burned a good bit of time trying to figure out what the heck was going on. Anyway, I suggest a quick change to add emphasis about how the replication keyword should not be used for logical replication. That'd have saved me. In a really wonderful world, we could also: - Update the pg_hba.conf file to make a note about this, and - Improve the error message above somehow to indicate that it only sees physical replication slots or something like that (I'm not sure what's possible here.) I hope this helps, and thank you for all the rest of the wonderful documentation.
Create Table documentation, version 10 - Examples
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/sql-createtable.html Description: I feel there should be an example of creating a table with a foreign key, since it is pretty common. Here is a suggestion which follows the established "theme" in the present documentation : Define a foreign key constraint on the `did` column of the `distributors` table, deletion of a record in the `distributors` table will fail if a record in the `films` table is still referring it. ```SQL CREATE TABLE films ( codechar(5) CONSTRAINT firstkey PRIMARY KEY, title varchar(40) NOT NULL, did integer NOT NULL REFERENCES distributors (did) ON DELETE RESTRICT, date_prod date, kindvarchar(10), len interval hour to minute ); CREATE TABLE distributors ( didinteger PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name varchar(40) NOT NULL CHECK (name <> '') ); ```
Add NOTICE about non-NFC-characters and clues for solution
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/unaccent.html Description: Seems a bug, because the `select unaccent('Iglésias')` result in accented "iglésias" again... It is correct because length('Iglésias') is 9 instead 8. The problem is not rare as you can check by pageviews of https://stackoverflow.com/questions/24863716 The solution is to feed database with good UTF8 (NFC characteres). **SUGGESTION**: add a notice for reders, about the aparent bug with non-NFC input, showing examples and clues about solutions. REF: https://en.wikipedia.org/wiki/Unicode_equivalence#Example
tables (and other objects) are automatically put into a schema named "public"
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/ddl-schemas.html Description: This statement is not true and is contradicted in https://www.postgresql.org/docs/9.6/sql-createtable.html which states correctly that: If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema. It is also worth mentioning that if current_schema is empty (i.e. ,search_path is empty or contains the names of non-existent schemas) you get the error: ERROR: no schema has been selected to create in
Alter table says rewrite is required for default values on 11.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/ddl-alter.html Description: https://www.postgresql.org/account/comments/new/11/ddl-alter.html/ indicates that the table needs a rewrite when a column with a default value is added, my understanding of the 11 changes meant that is not true any longer. The blog post below supports that unless the feature was disabled in future. https://blog.dbi-services.com/postgresql-11-instant-add-column-with-a-non-null-default-value/ The specific bit of "Tip" text is; --- Adding a column with a default requires updating each row of the table (to store the new column value). However, if no default is specified, PostgreSQL is able to avoid the physical update. So if you intend to fill the column with mostly nondefault values, it's best to add the column with no default, insert the correct values using UPDATE, and then add any desired default as described below.
Incorrect description of autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/runtime-config-autovacuum.html Description: This page lists the different autovacuum options. Describing autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor independently adds confusion unless you are also reading https://www.postgresql.org/docs/10/static/routine-vacuuming.html#AUTOVACUUM where we have a formula for the threshold and scale factor. I suggest adding a reference for each one of the two parameters with a link to the "Routine Vacuuming" section.
confusing terms
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/role-membership.html Description: https://www.postgresql.org/docs/10/static/role-membership.html it says "Once the group role exists, you can add and remove members using the GRANT and REVOKE commands: GRANT group_role TO role1, ... ; REVOKE group_role FROM role1, ... ;" how can you add members to a newly created group role using the above command? shouldn't it be GRANT group_role TO user1 ??
K.I.S.S.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/index.html Description: for a beginner, your documentation is hard to follow. nowhere does it discuss how to create a user that have "createdb" and "createrole" privileges. you should provide a complete chapter for noobs. also, don't discuss or intermix the discussion of recommended standard way of doing things. if required, place it in the appendix. your documentation should just keep discussing working with PostgreSQL, from beginning to advanced.