Re: \dt shows table but \d says the table doesn't exist ?

On Sat, May 4, 2024 at 5:15 AM Tom Lane wrote: > > Adrian Klaver writes: > > On 5/3/24 14:06, Magnus Hagander wrote: > >> Looks like you might need a \d "some_idIds" (include the quotes) since > >> it has an uppercase characters? > > > This: > > "Did not find any relation named

Re: Documentation diff

On Mon, Feb 26, 2024 at 1:49 AM Daniel Gustafsson wrote: > > > On 25 Feb 2024, at 17:38, Marcos Pegoraro wrote: > > > So I was thinking of a way to get differences between this and that > > versions, and for all doc pages. > > Something like we already have on [2], it explicits, this feature

Re: postgresql custom variable in pg_settings table

On Tue, Jan 9, 2024 at 3:38 PM Yi Sun wrote: > > Hello, > > > > But can not select it from pg_settings, as we use pgwatch2 to monitor, to > avoid pgwatch2 script change prefer to use pg_setting not current_setting() > function, is it possible to get the custom variable from pg_setting please?

procedure string constant is parsed at procedure create time.

hi. table "test" does not exist. ---1.this will work. CREATE or replace PROCEDURE template_create() LANGUAGE SQL AS $proc$ DROP TABLE if exists test cascade; CREATE TABLE test(id int4range,valid_at tsrange,name text);$proc$; 2.this will have errors. CREATE or replace PROCEDURE

Re: Local postgres manual

i think Ben Hancock want something like https://en.cppreference.com/w/Cppreference:Archives

Re: Read only user permission

On Thu, Aug 24, 2023 at 2:49 AM Marcos Pegoraro wrote: > > Have you tried grant pg_read_all_data to readonly ? > I tried. seems pg_read_all_data really means "read all data no matter what". so you cannot grant pg_read_all_data then revoke certain schema privileges. begin; create user

EXPLAIN statement can also apply to SELECT INTO.

hi. https://www.postgresql.org/docs/current/sql-explain.html >>> Any SELECT, INSERT, UPDATE, DELETE, MERGE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, or CREATE MATERIALIZED VIEW AS statement, whose execution plan you wish to see. >>> seems you can use it with SELECT INTO. explain (ANALYZE,

Re: Loops and Case Statements Involving Dates

On Mon, Aug 21, 2023 at 3:07 PM Anthony Apollis wrote: > > Please review my code and make recommendations where needed. I have this code: > ``` > NUMBER OF LOOPS FOR POSTGRESQL ETL: > SELECT > CASE > WHEN (((EXTRACT(DAY FROM > ((CASE > WHEN > (SELECT DISTINCT "CALDAY" FROM

Re: Dynamically accessing columns from a row type in a trigger

On Sun, Aug 13, 2023 at 11:27 AM Rhys A.D. Stewart wrote: > > Hey Adrian, > > Thanks for your response. I don't think I explained myself clearly. > pk_col is not the column name. pk_col is a variable that holds the > name of a column. This is one trigger for several tables with varying >

jsonb on-disk size calculation

hi. drop table x; create table x(js jsonb); insert into x select '{"Hello world":1}'::jsonb; select pg_column_size(js) from x; -- return 33. based on src/include/utils/jsonb.h The key and value part is 20 bytes (is it correct?), Jsonb->vl_len_ is 4 byte, JsonbContainer->header is 4 bytes.

Re: [Beginner Question]Is there way to test the postgres's kernel function?

On Mon, Jul 24, 2023 at 5:43 PM Wen Yi wrote: > > Hi community, > I am learning the kernel of the postgres, and I want to test the > make_relative_path function to know what it's operational principle.(I can > read it's code, and I want to actually run it) > But I really don't know how to do

Re: How to improve the performance of my SQL query？

On Mon, Jul 24, 2023 at 5:54 PM gzh wrote: > > >Did you change any parameters that have an impact on query planning? > > >You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS). > > I added some parameters and re-executed the Execution Plan. > > Except for the index not taking

pageinspect bt_page_items doc

hi. https://www.postgresql.org/docs/current/pageinspect.html#id-1.11.7.34.6 > This is a B-tree leaf page. All tuples that point to the table happen to be > posting list tuples (all of which store a total of 100 6 byte TIDs). > There is also a “high key” tuple at itemoffset number 1. ctid is

Re: Re: How to improve the performance of my SQL query？

On Thu, Jul 20, 2023 at 7:36 PM gzh wrote: > > > Thank you very much for taking the time to reply to my question. > > > Sorry, I provided incorrect information. > > The index also does not work in the following query statement. > > > > select COUNT(ET_CD) > > > from TBL_SHA > > > WHERE MS_CD =

Re: error in the example given for numeric data types

> Page: https://www.postgresql.org/docs/15/datatype-numeric.html "docs/15" means this url pointer to pg version 15. > practice=# create table t1(height numeric(3,5)); > ERROR: NUMERIC scale 5 must be between 0 and precision 3 > LINE 1: create table t1(height numeric(3,5)); > Please look into

Re: psql -af > out, is possible to also have raise notice, raise info part.

On Wed, Jul 12, 2023 at 12:06 PM David G. Johnston wrote: > > On Tue, Jul 11, 2023 at 8:52 PM jian he wrote: >> >> hi. >> >> test.sql content: >> -- >> d

psql -af > out, is possible to also have raise notice, raise info part.

hi. test.sql content: -- do $$ begin raise info 'information message %', now() ; raise debug 'debug message %', now(); raise notice 'notice message %', now(); end $$;

now() literal in pg_input_is_valid

Hi should I expect select pg_input_is_valid($$[now(), infinity)$$,'tstzrange'); returns true?

psql -c command parse "select $$hello$$" failed

Hi. not sure this is the expected result. /home/jian/postgres/pg16_test/bin/psql -d test_dev -p 5455 -c "select $$hello$$" 2023-07-04 20:15:51.066 CST [1562050] ERROR: trailing junk after numeric literal at or near "884070h" at character 8 2023-07-04 20:15:51.066 CST [1562050] STATEMENT: select

Re: analyze partition

On Thu, Jun 29, 2023 at 7:55 PM Marc Millas wrote: > > Hi, > > the documentation, on chapter 5.11.3.3 caveat says that a manual vacuum or > analyze on the root table does it only for that said root table. To my > understanding, the root table when used with declarative partitioning, does >

timetz need more bytes than timestamptz

Hi, https://www.postgresql.org/docs/current/datatype-datetime.html timetz, timestamptz: same resolution. fractional digits in the seconds field are also the same. > > All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the

Re: Where I can find the achieve of the 'generate_series' ?

On Tue, May 30, 2023 at 8:35 AM Wen Yi <896634...@qq.com> wrote: > Hi team, > I want to figure out the principle of the function 'generate_series', but > I don't know it contains in which source file.(I download the pgsql 15.3 > source code) > Can someone provide some advice? > Thanks in advance!

explicit-locking.html "key values" reference

hi. https://www.postgresql.org/docs/current/explicit-locking.html FOR KEY SHARE > Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT > FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared > lock blocks other transactions from performing DELETE or any UPDATE

confused by int2vector typdelim

hi. select pt.typtype , pt.typcategory , pt.typdelim , pt.typelem , pt1.typname as elem_type ,pt.typsubscript ,pt.typname frompg_type pt join pg_type pt1 on pt.typelem = pt1.oid where pt.typname = 'int2vector'; returns typtype |

Re: function signature allow "default" keyword

On Mon, May 15, 2023 at 5:00 PM Thomas Kellerer wrote: > jian he schrieb am 15.05.2023 um 10:33: > > > > function idea. > > allow function calling using the default keyword for any of the input > arguments. > > > > example: https://dbfiddle.uk/FQwnfdmm >

function signature allow "default" keyword

function idea. allow function calling using the default keyword for any of the input arguments. example: https://dbfiddle.uk/FQwnfdmm So something like this "SELECT * FROM customer_orders(2579927, 'order_placed_on DESC', default, 2);" should work.

customized function to Reconstructs the creating command for a role.

Hi, similar to pg_get_functiondef ( *func* oid ) → text is there any function to get the role definition?

pg_class char type should be "char"?

Hi, https://www.postgresql.org/docs/current/catalog-pg-class.html relkind char > relpersistence char > relreplident char these 3 columns type should be "char" ?

wiki.postgres ​ Tighten trigger permission checks already resolved

Hi. The following Todo item seems already resolved in pg15. https://wiki.postgresql.org/wiki/Todo#Triggers > Tighten trigger permission checks > >- Security leak with trigger functions? > > > But it seems to not appear

Re: alter table rename column can event trigger capture new column name

On Mon, Apr 24, 2023 at 10:46 AM jian he wrote: > > hi. > alter table t1 rename col1 to id. > > Is it possible to use an event trigger to capture the new column name > "id"? > > > sorry for the noise. I figured it out. substring(_object_identity FROM (

alter table rename column can event trigger capture new column name

hi. alter table t1 rename col1 to id. Is it possible to use an event trigger to capture the new column name "id"?

doc sql-grant.html Synopsis error?

Hi. --work as intended. grant ALL PRIVILEGES on FUNCTION pg_catalog.pg_reload_conf() to test; grant ALL PRIVILEGES on FUNCTION pg_reload_conf() to test; -errors. it should be work, or I interpret the doc the wrong way? GRANT ALL PRIVILEGES ON FUNCTION pg_reload_conf() IN SCHEMA

Re: ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account

On Tue, Apr 4, 2023 at 12:22 AM Erik Wienhold wrote: > > On 03/04/2023 17:36 CEST Adrian Klaver > wrote: > > > > On 4/3/23 08:11, Erik Wienhold wrote: > > >> On 02/04/2023 17:40 CEST Adrian Klaver > wrote: > > >> > > >> That is a long way from: > > >> > > >> jsonb @@ jsonpath → boolean > > >>

​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account

Hi, https://www.postgresql.org/docs/current/functions-json.html > jsonb @@ jsonpath → boolean > > Returns the result of a JSON path predicate check for the specified JSON > value. Only the first item of the result is taken into account. If the > result is not Boolean, then NULL is returned. > >

Re: src/test/examples/testlibpq2.c where the HAVE_SYS_SELECT_H is defined.

Yes, you are right. my mistake. I should choose the manual dev version. On Fri, Mar 17, 2023 at 4:12 PM Thomas Munro wrote: > On Fri, Mar 17, 2023 at 7:48 PM jian he > wrote: > > Hi, > > playing around with $[0] testlibpq2.c example. I wondered where > HAVE_SYS_SELECT_H

src/test/examples/testlibpq2.c where the HAVE_SYS_SELECT_H is defined.

Hi, playing around with $[0] testlibpq2.c example. I wondered where HAVE_SYS_SELECT_H is defined? I searched on the internet, founded that people also asked the same question in $[1]. In my machine, I do have . system version: Ubuntu 22.04.1 LTS gcc version: gcc (Ubuntu 11.3.0-1ubuntu1~22.04)

Re: How to pass table column values to function

On Sat, Feb 11, 2023 at 3:55 PM Andrus wrote: > Hi! > > Table source contains integer column. Its values should be passed to > function for selecting data from other table. > > I tried > > CREATE OR REPLACE FUNCTION > public.TestAddAssetTransactions(dokumnrs int[]) > RETURNS int

38.10.6. Composite-Type Arguments C-language function code demo works for int, but not for numeric.

Hi. source: https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-C-BASETYPE (38.10.6. Composite-Type Arguments) column "salary" int data type works fine. But it does not work if the "salary" column data type is numeric. /* rm funcs.so && rm funcs.o gcc

Re: does refreshing materialized view make the database bloat?

On Mon, Jan 16, 2023 at 10:28 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sunday, January 15, 2023, jian he wrote: > >> >> >> Hi, >> why the materialized view itself bloats. If no refresh then no bloat >> right? If fresh then set bas

Re: does refreshing materialized view make the database bloat?

On Sat, Jan 14, 2023 at 11:49 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Friday, January 13, 2023, jian he wrote: > >> >> Once we have the diff table, we perform set-based DELETE and INSERT >>> operations against the materialized view, and

does refreshing materialized view make the database bloat?

Hi, src_backend_commands_matview.c 547: /* 548: * refresh_by_match_merge 549: * 550: * Refresh a materialized view with transactional semantics, while allowing 551: * concurrent reads. 552: * 553: * This is called after a new version of the data has been created in a 554: * temporary

https://wiki.postgresql.org/wiki/Working_with_Git link one link cannot open, another link is unrelated.

Hi. It's more like a git question less like a postgres question.. I executed the following commands rm /home/jian/Downloads/001_psql_parse_only_test.patch git checkout master git branch --delete --force feature_to_review git checkout -b feature_to_review patch -p1 --batch <

Re: ON CONFLICT and WHERE

On Mon, Nov 14, 2022 at 2:55 AM Adrian Klaver wrote: > On 11/13/22 13:07, Tom Lane wrote: > > Adrian Klaver writes: > >> INSERT INTO books VALUES (12, 0, CURRENT_TIMESTAMP) > >> ON CONFLICT (id) > >> WHERE updated IS NULL OR updated + INTERVAL '2min' < CURRENT_TIMESTAMP > >> DO UPDATE > >> SET

for integer/bigint type while format to scientific notation, automatically get the correct number of precision

Hi, looking for the reverse of select '1.2923904e+07'::numeric::int; every time, I have to select to_char(12923904,'9.999'); if I do select to_char(12923904,'9.99'); then the result is different. For integer or bigint can I automatically have something like: select

Re: Delete a table automatic?

On Tue, Nov 1, 2022 at 2:33 PM 黄宁 wrote: > I now have two tables named A and B. Table B is calculated based on the > data of table A. I wonder if table B can be automatically deleted when > table A is deleted？ > Your question seems not that specific. You can use https://dbfiddle.uk/btGcOH30 to

Re: How to know how much CPU, RAM is used by existing 1 database

On Wed, Oct 26, 2022 at 11:07 AM Yi Sun wrote: > Hi Guys, > > Who can help me with this please? I researched but still no result yet, > thank you > > On Tue, 25 Oct 2022 at 16:30, Yi Sun wrote: > >> Hi, >> >> There are many databases in our production patroni cluster and it seems >> it is

understand pg_ndistinct type && Why with(autovacuum_enabled=off) some query estimate 100, some is 200.

source: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/test/regress/expected/stats_ext.out;h=431b3fa3de1f4f87205e7e27a99ef1cf337f1590;hb=676887a3b0b8e3c0348ac3f82ab0d16e9a24bd43 -- n-distinct tests CREATE TABLE ndistinct ( filler1 TEXT, filler2 NUMERIC, a INT,

In a partition why 1st time encounter NULL then call minvfunc

same question: https://stackoverflow.com/questions/73476732/postgresql-in-a-partition-1st-time-null-then-call-minvfunc dbfilddle source

Re: Creating constraint dynamically

On Mon, Aug 22, 2022 at 12:59 PM sivapostg...@yahoo.com < sivapostg...@yahoo.com> wrote: > Hello, > Using PG 11.4 > > We populate constraint string dynamically and add it to the table with > alter table command. It gets added, but without the required brackets. > What we build is > ALTER TABLE

lateral join with union all

select * from ( (select 2 as v ) union all (select 3 as v) ) as q1 cross join lateral ( (select * from ((select 4 as v) union all (select 5 as v)) as q3 ) union all (select q1.v) ) as q2; I thought q1 will be materialized as a constant set and will be equivalent

Re: ICU is not supported in this build. install from source code.

utils/adt/varlena.o: in function `varstr_cmp': > /home/jian/Desktop/pg16_source/postgresql/src/backend/utils/adt/varlena.c:1674: > undefined reference to `u_errorName_70' > collect2: error: ld returned 1 exit status > make[2]: *** [Makefile:66: postgres] Error 1 > make[2]: Leaving dir

ICU is not supported in this build. install from source code.

Install from source code(follow the manual) system version: Ubuntu 22.04 LTS pg version: PostgreSQL 15beta2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit install step: > ./configure --with-perl --with-python --with-icu > ICU_CFLAGS='-I/usr/include/unicode'

Re: postgis

Can you try compiling from source: https://postgis.net/source/ postgis 3.2.1 is OK. postgresql & postgis version info: https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS if you already installed check postgis version: https://postgis.net/docs/PostGIS_Version.html On Wed, Jul 20,

Re: About limitation of using postgresql in china

On Tue, Jul 19, 2022 at 2:07 PM pig lee wrote: > Dear postgresql member > > > > I will use postgresql in china for a project. > > But I’m not sure Whether there are some limitations when using > postgresql in china(limitation for China only). > > For example，license limitation in china or other

first order by then partition by x < a fixed value.

This question (https://stackoverflow.com/q/72975669/15603477) is fun. DB fiddle link: https://dbfiddle.uk/?rdbms=postgres_14=36d685ad463831877ae70361be2cfa3b account size idname 1001 John 2002 Mary 3003 Jane 4004 Anne1005 Mike 6006 Joanne Then

range with infinity bound data type disallow extra empty white space.

Hi, there. Not sure this is a bug, or intentional. select '[1, )'::int8range; --fail. select '[1,)'::int8range; --ok. tested on pg14.4, pg15.1 -- I recommend David Deutsch's <> Jian

GIN index operator ?(jsonb,text) not working?

Hi, dbfiddle: https://dbfiddle.uk/?rdbms=postgres_14=060af497bbb75ecddad9fd2744f8022b --- create table test101 ( doc_id bigserial, document jsonb); insert into test101(document) values ('{"user_removed" :false}') returning *; insert into test101(document) select '{"user_removed"

Source code test data folder don't have CSV files. How to get the CSV file.

trying to understand the source code test part. https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/copy.sql;h=d72d226f341f42c69ffcb773c4faf53d9e586894;hb=072132f04e55c1c3b0f1a582318da78de7334379 First I can just ignore all the abs_srcdir, abs_builddir. Since I can

Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

On Sat, Jun 18, 2022 at 5:44 AM Bryn Llewellyn wrote: > > */* — START OF SPEC —— */* > > > *The document's top-level object may use only these keys:* > > *"isbn" — string* > *values must be unique across the entire set of documents (in other words, > it defines

Re: GIN theory

theory/explanation about GIN index: https://github.com/postgres/postgres/blob/master/src/backend/access/gin/README https://postgrespro.com/blog/pgsql/4261647 https://pgpedia.info/g/gin.html On Fri, Jun 3, 2022 at 2:34 PM huangning...@yahoo.com < huangning...@yahoo.com> wrote: > Hi: > > I want

How to display complicated Chinese character: Biang.

Inspired by this thread: https://www.postgresql.org/message-id/011f01d8757e%24f5d69700%24e183c500%24%40ndensan.co.jp Trying to display some special Chinese characters in Postgresql. For now I am using postgresql 15 beta1. The OS is Ubuntu 20. localhost:5433 admin@test=# show LC_COLLATE;

Re: link that explain ICU_LOCALE locale concept about local variant code and keywords.

I found answer on https://www.unicode.org/reports/tr35/tr35-collation.html and https://cldr.unicode.org/index/bcp47-extension On Wed, May 25, 2022 at 1:52 PM jian he wrote: > > code from > https://www.postgresql.org/message-id/flat/5e756dd6-0e91-d778-96fd-b1bcb06c161a%402ndqua

link that explain ICU_LOCALE locale concept about local variant code and keywords.

code from https://www.postgresql.org/message-id/flat/5e756dd6-0e91-d778-96fd-b1bcb06c161a%402ndquadrant.com > DROP DATABASE IF EXISTS dbicu; > CREATE DATABASE dbicu LOCALE_PROVIDER icu LOCALE 'en_US' ICU_LOCALE > 'en-u-kf-upper' template 'template0'; > \c dbicu > CREATE COLLATION upperfirst

Re: cast to domain with default collation issue.

at need to know the collation will fail. > On Wed, May 25, 2022 at 12:08 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > Please don’t top-post. > > On Tuesday, May 24, 2022, jian he wrote: > >> >> Otherwise, all input expressions must have t

Re: cast to domain with default collation issue.

it does not mention anything, then the default value is null. So * create table test111( a tex*t) The default collation for column a is the same as the output of *show lc_collate*. so there is no *non-default? * On Tue, May 24, 2022 at 10:43 AM David G. Johnston < david.g.johns...@gmail.com&

cast to domain with default collation issue.

CREATE DOMAIN testdomain AS text; --asume the default collation is as per show LC_COLLATE; – on my pc, it is C.UTF-8. --So the testdomain will be collation "C.UTF-8" => \d collate_test1 Table "test.collate_test1" Column | Type | Collation | Nullable | Default

range of composite types!

range of composite types. I found this would be a great idea!!! Question on stackoverflow DB Fiddle

Re: Regular Expression For Duplicate Words

It's an interesting question. But I also don't know how to do it in PostgreSQL. But I figured out alternative solutions. GNU Grep:grep -E '(hello)[[:blank:]]+\1' <<<'one hello hello world' ripgrep: rg '(hello)[[:blank:]]+\1' --pcre2 <<<'one hello hello world' On Wed, Feb 2, 2022 at

Re: Counting the number of repeated phrases in a column

you may be interested > https://dba.stackexchange.com/q/166762/238839 On Wed, Jan 26, 2022 at 3:03 AM Ivan Panchenko wrote: > > On 26.01.2022 00:21, benj@laposte.net wrote: > > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : > >> There is a short of a function in the standard Postgres to do

Chain Hashing

Been following YouTube to study about Database Hash Join. https://www.youtube.com/watch?v=J0nbgXIarhc * HASH TABLE* *Design Decision* * #1: Hash Function *→ How to map a large key space into a smaller domain. → Trade-off between being fast vs. collision rate. Design Decision *#2: Hashing Scheme* →

Re: database sorting algorithms.

Thanks a lot. I found out about this Youtube video (https://youtu.be/alJswNJ4P3U?t=1852), in case you guys are interested. This video really clarify about the time complixty of MergeSort. On Sat, May 1, 2021 at 3:19 PM Gavan Schneider wrote: > On 1 May 2021, at 17:06, Jian He wrote: >

database sorting algorithms.

Been self study Database, from database I deep dived into sorting algorithms. Databases can do in-memory QuickSort. It also has an on-disk MergeSort. For MergeSort: I follow this tutorial https://youtu.be/6pV2IF0fgKY?t=1108 (around 1 minutes only) But I am still not fully understanding about