tablesample clause doesnt support to be applied to subquery.
hi~ I test "tablesample" feature on pg9.5 and found that tablesample doesnt support to be applied to subquery. for example this sql doesnt work on pg9.5: ``` create table t1( c1 int, c2 int ); select c2 from (select * from t1) as t2 tablesample system (50); ``` while i read sql2003 standard about tablesample and think it should allow this usage according to standard. is it a deviation to sql standard?
Re: tablesample clause doesnt support to be applied to subquery.
Hello, On Mon, 2022-08-08 at 15:52 +0800, Wind wrote: > hi~ > I test "tablesample" feature on pg9.5 and found that tablesample > doesnt support to be applied to subquery. > for example this sql doesnt work on pg9.5: > ``` > create table t1( > c1 int, > c2 int > ); > select c2 from (select * from t1) as t2 tablesample system (50); > ``` > while i read sql2003 standard about tablesample and think it should > allow this usage according to standard. > is it a deviation to sql standard? Version 9.5 is not supported. The oldest supported version is 10, the current version is 14. Please read chapter 58 in the doco. HTH,
RE: 20220722-pg_dump: error: invalid number of parents 0 for table
Hi to all, We are using PostgreSQL 12.3 and it is running under Windows environment. In my server multiple databases are available. When we try to Back up one of the database, it gives the following error message, we can able to connect and read & write to the database. But unable to back up the database , We faced this issue on one database only, remaining databases are working good and able to backup. Suggest me, How to resolve this issue. pg_dump: last built-in OID is 16383 pg_dump: reading extensions pg_dump: identifying extension members pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading user-defined functions pg_dump: reading user-defined types pg_dump: error: schema with OID 1020797 does not exist Thanks, Karthick Ramu
pg_restore remap schema
Hello, I can't find an option with pg_restore to rename an exported schema schema1 -> schema2 Is a development in progress to add this option Regards, Fabrice
Re: pg_restore remap schema
Hi, Le lun. 8 août 2022 à 18:28, Fabrice Chapuis a écrit : > Hello, > I can't find an option with pg_restore to rename an exported schema > schema1 -> schema2 > That's because it doesn't exist :) > Is a development in progress to add this option > > Nope, never heard of someone working on this. Regards. -- Guillaume.
Re: pg_restore remap schema
Guillaume Lelarge writes: > Le lun. 8 août 2022 à 18:28, Fabrice Chapuis a >> Is a development in progress to add this option > Nope, never heard of someone working on this. People have asked for such a thing before, but it'd be quite difficult to do reliably --- particularly inside function bodies, which aren't normally parsed at all during a dump/restore. If you're willing to accept a 95% solution, running the pg_restore output through "sed" would likely work fairly well. I'd personally want to diff the before-and-after scripts before applying though :-) regards, tom lane
Re: pg_restore remap schema
> > People have asked for such a thing before, but it'd be quite difficult > to do reliably --- particularly inside function bodies, which aren't > normally parsed at all during a dump/restore. If you're willing to > accept a 95% solution, running the pg_restore output through "sed" > would likely work fairly well. I'd personally want to diff the > before-and-after scripts before applying though :-) > Another 95% solution, if both schemas are on the same server, google for function clone schema, you´ll find some of them. They create tables, functions, sequences, views, ...
Re: 20220722-pg_dump: error: invalid number of parents 0 for table
hey Karthick, if you've made triple sure that it's not a permissions issue, take a look at some of the suggestions in this old thread: https://dba.stackexchange.com/questions/17546/why-is-pg-dumpall-throwing-an-oid-does-not-exist-error On Mon, Aug 8, 2022 at 5:30 AM Techsupport wrote: > *Hi to all,* > > > > We are using PostgreSQL 12.3 and it is running under Windows environment. > In my server multiple databases are available. > > > > When we try to Back up one of the database, it gives the following error > message, we can able to connect and read & write to the database. > > But unable to back up the database , We faced this issue on one database > only, remaining databases are working good and able to backup. Suggest me, > How to resolve this issue. > > > > *pg_dump**: last built-in OID is 16383* > > *pg_dump**: reading extensions* > > *pg_dump**: identifying extension members* > > *pg_dump**: reading schemas* > > *pg_dump**: reading user-defined tables* > > *pg_dump**: reading user-defined functions* > > *pg_dump**: reading user-defined types* > > *pg_dump**: error: schema with OID 1020797 does not exist* > > > > *Thanks,* > > *Karthick Ramu* >
Re: pg_restore remap schema
Thank you for your reply. sed is a solution for making substitutions on plain text. But if we work with directory mode to use parallelism, I don't see how to proceed to make a schema remap. Editing the toc file in text mode does not work neither. Regards, Fabrice On Mon, Aug 8, 2022 at 9:27 PM Marcos Pegoraro wrote: > People have asked for such a thing before, but it'd be quite difficult >> to do reliably --- particularly inside function bodies, which aren't >> normally parsed at all during a dump/restore. If you're willing to >> accept a 95% solution, running the pg_restore output through "sed" >> would likely work fairly well. I'd personally want to diff the >> before-and-after scripts before applying though :-) >> > > Another 95% solution, if both schemas are on the same server, google for > function clone schema, you´ll find some of them. They create tables, > functions, sequences, views, ... > >
