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

On Tue, 2023-05-30 at 08:34 +0800, Wen Yi wrote: > 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? https://git.postgresql.org/gitweb/?p=postgresq

syntax pb

Hi, I always have had difficulties to understand syntax. So... If I have: create table t1 (t text); create table t2 (a text, b text, c test, d numeric); insert into t1('azerty'); INSERT 0 1 fine ! so, now, if I write: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d >From t1 test1;

Re: syntax pb

On Tuesday, May 30, 2023, Marc Millas wrote: > > I get: > ERROR: column "d" is of type numeric but expression is of type text > LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d > > HINT: You will need to rewrite or cast the expression. > > Can someone give a short SQL syntax hi

Re: syntax pb

On 30/05/2023 14:45, Marc Millas wrote: and , now, if I want to insert that: Insert into t2 (a, b, c, d) Select distinct test1.t, 'abc' as b, NULL as c, NULL as d From t1 test1; I get: ERROR:  column "d" is of type numeric but expression is of type text LINE 2: Select distinct test1.t, 'abc' a

Re: syntax pb

On 5/30/23 06:45, Marc Millas wrote: Hi, I always have had difficulties to understand syntax. So... If I have: create table t1 (t text); create table t2 (a text, b text, c test, d numeric); Is c supposed to be text? Or are you indeed referring to some unspecified type? insert into t1('azert

Re: syntax pb

On 5/30/23 07:38, Adrian Klaver wrote: On 5/30/23 06:45, Marc Millas wrote: Hi, I always have had difficulties to understand syntax. So... If I have: create table t1 (t text); create table t2 (a text, b text, c test, d numeric); Is c supposed to be text? Or are you indeed referring to some u

Re: syntax pb

The above should have been: > > Insert into t2 (a, b, c, d) > Select distinct test1.t, 'abc' as b, NULL::text, NULL::numeric > From t1 test1; > > > > > which results in: > > > > select * from t2; > > a| b | c | d > > +-+--+-- > > azerty | abc | NULL | NULL > > >

Re: syntax pb

On Tue, May 30, 2023 at 05:53:30PM +0200, Marc Millas wrote: > Thanks Adrian, but if the query becomes more complex, for example with a > few joins more, then even casting doesn't work. > This comes from a prod environment and even casting NULLs (which is more > than strange, BTW) generates absurd

File-Access functions by default not executable by predefined role "pg_read_server_files"

Hi there, in PG 14, you've removed explicit 'superuser()' checks in file-access functions (like pg_ls_dir, pg_read_file, etc.) and moved to an ACL based approach to restrict access to these functions. In turn, you've also removed EXECUTE permission from role "public", leaving these functions

Re: syntax pb

On Tue, May 30, 2023 at 8:53 AM Marc Millas wrote > This comes from a prod environment and even casting NULLs (which is more > than strange, BTW) generates absurd errors. > If you want an input to be anything other than plain text (numbers partially exempted) you need to cast it. Sure, some li

Re: syntax pb

"David G. Johnston" writes: > On Tue, May 30, 2023 at 8:53 AM Marc Millas wrote >> Too my understanding it looks like the parser did not parse the select >> distinct as we think he does. > The DISTINCT clause doesn't really come into play here at all, so if you > think it does you indeed have a

Re: syntax pb

On 5/30/23 10:31 AM, Tom Lane wrote: "David G. Johnston" writes: On Tue, May 30, 2023 at 8:53 AM Marc Millas wrote Too my understanding it looks like the parser did not parse the select distinct as we think he does. The DISTINCT clause doesn't really come into play here at all, so if you t

Re: syntax pb

On 5/30/23 8:53 AM, Marc Millas wrote: Thanks Adrian, but if the query becomes more complex, for example with a few joins more, then even casting doesn't work. This comes from a prod environment and even casting NULLs (which is more than strange, BTW) generates absurd errors. Too my underst

Re: syntax pb

Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, May 30, 2023 at 3:51 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, May 30, 2023, Marc Millas wrote: > >> >> I get: >> ERROR: column "d" is of type numeric but expression is of type text >> LINE 2: Selec

Re: syntax pb

Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, May 30, 2023 at 7:12 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, May 30, 2023 at 8:53 AM Marc Millas wrote > > >> This comes from a prod environment and even casting NULLs (which is more >> than strange, B

Re: syntax pb

Thanks for the explanation. Crystal clear, thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, May 30, 2023 at 7:31 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Tue, May 30, 2023 at 8:53 AM Marc Millas > wrote > >> Too my understanding it looks like the parser

event trigger should provide more details

hi, I plan to create an event trigger to detect schema change (e.g. add/remove a column, change column type), and write it into a separate table (e.g. EVENTS). Then a process periodically reads this table to send schema change notification. However, the event trigger

Pg 16: will pg_dump & pg_restore be faster?

https://www.postgresql.org/about/news/postgresql-16-beta-1-released-2643/ says "PostgreSQL 16 can also improve the performance of concurrent bulk loading of data using COPY up to 300%." Since pg_dump & pg_restore use COPY (or something very similar), will the speed increase translate to hig

Re: Pg 16: will pg_dump & pg_restore be faster?

On Wed, 31 May 2023 at 08:54, Ron wrote: > https://www.postgresql.org/about/news/postgresql-16-beta-1-released-2643/ > says "PostgreSQL 16 can also improve the performance of concurrent bulk > loading of data using COPY up to 300%." > > Since pg_dump & pg_restore use COPY (or something very simila

Re: event trigger should provide more details

> On 30/05/2023 22:23 CEST Lian Jiang wrote: > > I plan to create an event trigger to detect schema change (e.g. add/remove > a column, change column type), and write it into a separate table (e.g. > EVENTS). Then a process periodically reads this table to send schema change > notification. Howeve

Re: event trigger should provide more details

Thanks. This is helpful. Below is the result when I add a column to public.accounts. obj.classid, -- 1259 obj.objid, -- 16409 obj.objsubid, -- 0 obj.command_tag, -- ALTER TABLE obj.object_type, -- table obj.schema_name, -- public obj.object_identity, -- public.accounts obj.in_extension; -- f The

Re: event trigger should provide more details

> On 31/05/2023 00:28 CEST Lian Jiang wrote: > > The info useful for me is command_tag, object_type, object_identity. > classid, objid is not useful since object_identity is more explicit. > objsubid is not useful because I don't need comment > (https://www.postgresql.org/message-id/pine.lnx.4.33.

Re: Having issue with SSL.

I was able to find out the issue. The firewall between the server and clients refused to allow SSL traffic over port 5432. Once allowing SSL via port 5432 it started working. From: Peter J. Holzer Sent: Friday, May 26, 2023 3:17 AM To: pgsql-general@lists.post

Re: How to make the generate_series to generate the letter series?

On Mon, May 29, 2023 at 12:51:15PM +0900, Ian Lawrence Barwick wrote: > 2023年5月29日(月) 12:39 Wen Yi <896634...@qq.com>: > > > > Hi team, > > when I study the generate_series function, I found that it can not only > > generate the number series but also can generate the date series.(ref: > > https:

Re: Pg 16: will pg_dump & pg_restore be faster?

On Wed, May 31, 2023 at 09:14:20AM +1200, David Rowley wrote: > On Wed, 31 May 2023 at 08:54, Ron wrote: > > https://www.postgresql.org/about/news/postgresql-16-beta-1-released-2643/ > > says "PostgreSQL 16 can also improve the performance of concurrent bulk > > loading of data using COPY up to 30

Re: Pg 16: will pg_dump & pg_restore be faster?

On Wed, 31 May 2023 at 13:13, Bruce Momjian wrote: > There is no mention of concurrency being a requirement. Is it wrong? I > think there was a question of whether you had to add _multiple_ blocks > ot get a benefit, not if concurrency was needed. This email about the > release notes didn't men

Re: Pg 16: will pg_dump & pg_restore be faster?

On Wed, May 31, 2023 at 02:05:10PM +1200, David Rowley wrote: > On Wed, 31 May 2023 at 13:13, Bruce Momjian wrote: > > There is no mention of concurrency being a requirement. Is it wrong? I > > think there was a question of whether you had to add _multiple_ blocks > > ot get a benefit, not if co

Re: Pg 16: will pg_dump & pg_restore be faster?

On Wed, 31 May 2023 at 14:11, Bruce Momjian wrote: > > On Wed, May 31, 2023 at 02:05:10PM +1200, David Rowley wrote: > > "Relation extensions have been improved allowing faster bulk loading > > of data using COPY. These improvements are more significant when > > multiple processes are concurrently

Re: Pg 16: will pg_dump & pg_restore be faster?

On Wed, May 31, 2023 at 02:18:25PM +1200, David Rowley wrote: > On Wed, 31 May 2023 at 14:11, Bruce Momjian wrote: > > > > On Wed, May 31, 2023 at 02:05:10PM +1200, David Rowley wrote: > > > "Relation extensions have been improved allowing faster bulk loading > > > of data using COPY. These improv

speed up full table scan using psql

hi, I am using psql to periodically dump the postgres tables into json files which are imported into snowflake. For large tables (e.g. 70M rows), it takes hours for psql to complete. Using spark to read the postgres table seems not to work as the postgres read only replication is the bottleneck so

Re: speed up full table scan using psql

On 5/30/23 22:25, Lian Jiang wrote: hi, I am using psql to periodically dump the postgres tables into json files which are imported into snowflake. For large tables (e.g. 70M rows), it takes hours for psql to complete. Using spark to read the postgres table seems not to work as the postgres r

Re: speed up full table scan using psql

On Wed, 31 May 2023 at 16:26, Lian Jiang wrote: > I am using psql to periodically dump the postgres tables into json files > which are imported into snowflake. For large tables (e.g. 70M rows), it takes > hours for psql to complete. Using spark to read the postgres table seems not > to work as

Re: speed up full table scan using psql

Thanks! Will try. Is there a way to speed up Spark ingestion (postgres -> spark -> snowflake) which does not have issues such as alignment or inefficient json file format? I favor spark since it simplifies the ingest path of postgres -> local json.gz -> s3 -> snowflake stage -> snowflake table. Ho