Re: Seems to be impossible to set a NULL search_path

[Peter J. Holzer]

On 2022-07-06 20:47:19 -0700, Bryn Llewellyn wrote:
> But never mind. If you'd like a diverting read on this topic, go here:
> 
> https://blogs.oracle.com/sql/post/a-collection-of-plsql-essays
> 
> look for this, and download the PDF:
> 
> «
> Names vs identifiers
> 
> Databases are full of things: tables, sequences, columns, views, PL/SQL units,
> what have you. Things have names and are manipulated by mentioning the names.
> The programming languages SQL and PL/SQL use identifiers, not names.
[...]

If there's one thing I've learned in 39 years of programming it's that
technical terms rarely have a fixed, universally accepted meaning.
There's a reason standards include a "terms and definition" section
which often define seemingly trivial terms like "byte" and why project
management coaches often stress the importance of a glossary in the
documentation.

So I think how a single Oracle employee uses those terms has little
bearing on the PostgreSQL documentation (I haven't read it yet - maybe I
agree with him, but I'm also only a single PostgreSQL user). If you want
to argue that the usage is wrong you would have to point at something
within the PostgreSQL documentation (ideally an entry in the glossary)
or some really wide-spread convention and the absence of a local
definition.

> Questions show many programmers are confused about the difference.

Which might be a hint that no wide-spread convention on how to use those
terms exists.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: About revoking large number of privileges; And the PUBLIC role.

[Peter J. Holzer]

On 2022-07-07 09:47:38 +0200, Dominique Devienne wrote:
> Hi. I'm struggling to delete databases because of grants to roles on
> objects of those DBs.

I don't understand this. You can drop objects (and databases containing
objects) with grants to existing roles. It would be very inconvenient if
that wasn't possible.

Do you have an example on where a grant prevents dropping an object?

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

- operator overloading not giving expected result

[Rajesh S]

Hi,

We are migrating our database from Oracle to Postgresql.  In oracle we 
have used this syntax "SELECT ('1999-12-30'::DATE) - 
('1999-12-11'::DATE)" to get difference between two dates as a integer 
output (ex: 19).  But in Postgres the same query returns result as "19 
days".  Because of this we are getting errors while assigning this query 
output to a numeric variable saying "ERROR: invalid input syntax for 
type numeric: "1825 days"" and "ERROR: operator does not exist: interval 
+ integer".  To avoid changing the application code in many places to 
extract the number of days alone, we tried operator overloading concept 
as below.


CREATE OR REPLACE FUNCTION public.dt_minus_dt(
    dt1 timestamp without time zone,
    dt2 timestamp without time zone)
    RETURNS integer
    LANGUAGE 'edbspl'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
    days INTEGER;
BEGIN
    SELECT DATE_PART('day', dt1::timestamp - dt2::timestamp)::integer 
INTO days;

    RETURN days;
END
$BODY$;

CREATE OPERATOR public.- (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

When we execute "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)", we 
are still getting "19 days" as result and not "19" as we expect.  The 
above same function works as expected for the operator + or ===.


CREATE OPERATOR public.+ (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

SELECT ('1999-12-30'::DATE) + ('1999-12-11'::DATE)

CREATE OPERATOR public.=== (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

SELECT ('1999-12-30'::DATE) === ('1999-12-11'::DATE)


I really appreciate anyone's help in resolving this case.  Thanks in 
advance.



Rajesh S

RE: Postgresql error : PANIC: could not locate a valid checkpoint record

[Mahendrakar, Prabhakar - Dell Team]

Hi Team,
Hi Team,

We are using the below command to perform the PG Upgrade.

'/opt///services/datastore/engine-new/bin/pg_upgrade'
-b '/opt///services/datastore/engine/bin' 
-B '/opt///services/datastore/engine-new/bin' 
-d '/opt///db/data' 
-D '/opt///db/data-new' 
-p 9003
-P 9003
-U apollosuperuser
-k -j 4 -v"

This is not a cluster environment and is standalone one.
Could you please let us know how to restore the database to a state before 
proceeding to upgrade or explicitly issue a checkpoint before we move on to the 
pg_upgrade command.

Thanks,
Prabhakar


Internal Use - Confidential

-Original Message-
From: Michael Paquier 
Sent: Monday, June 27, 2022 5:34 AM
To: Mahendrakar, Prabhakar - Dell Team
Cc: Mateusz Henicz; pgsql-general@lists.postgresql.org
Subject: Re: Postgresql error : PANIC: could not locate a valid checkpoint 
record

On Fri, Jun 24, 2022 at 01:03:57PM +, Mahendrakar, Prabhakar - Dell Team 
wrote:
> Is it possible to explicitly issue a checkpoint before we move on to 
> the pg_upgrade command?
> so that in the circumstances of the Upgrade issues (like PANIC:
> could not locate a valid checkpoint record), we still  have this last 
> explicit checkpoint available.
>
> Please let us know your thoughts on this.

Well, you have mentioned the use of pg_upgrade, but you are giving zero details 
about what kind of command you used, how you handled the clusters before and 
after that were upgraded, or what kind of environment is getting used.  With 
this little amount of details, nobody will be able to guess what's happening.  
This issue could also be caused by the environment.  For example, it is 
possible in some carelessly-setup enviromnents that a flush is issued and 
recognized as completed by the OS, and thought as completed by Postgres, but an 
application layer between the OS and the actual hardware did not issue the 
flush (be it an OS, FS, disk or a VM-related thing), which would make this 
issue reachable.
--
Michael

postgresql generate ddl returns FK with `()` in it

[Roman Gavrilov]

I have a schema with two tables, where the first table's composite
2-columns FK points to the second table's 2-columns composite PK. But the
order of columns is reversed.

When I use `Generate DDL` on the first table, FK definition comes back as:

ALTER TABLE eval.rubric_questions ADD CONSTRAINT rubric_questions_fk
FOREIGN KEY (rubric_id,rubric_version_id) REFERENCES ();

How can I fix that `();` issue? Is this documented behavior?

(I realize that I need to fix the schema, but in time-being I need to
generate code using entity framework, and it chokes in that).

Full details on SO:

https://stackoverflow.com/questions/72898296/what-is-in-postgresql-ddl

Regards,
Roman

Error when pasting function blocks into psql

[Ludwig Isaac Lim]

Hello:
I noticed that I'm getting errors when copy and pasting code for stored 
procedure from Windows directly into psql running in putty.
To reproduce create a procedure that has multiple statements with each 
statements having multiple lines, similar to the ones below:
CREATE OR REPLACE PROCEDURE test()AS$$     UPDATE                     table1    
  SET                   field1 = ?;

       -- more SQL statements$$LANGUAGE SQL;
Copy and paste from Windows editor (or IDE) to a psql running in a Putty. 
Chances are it will complain of syntax error. The error doesn't appear when I 
copy and paste it to an empty file in vim, and the run:
psql -f 
Is there a way of prevent this kind of error when pasting directly into psql? 
I'm using the latest version of PostgreSQL (server and client. Version 14.4)
Thank you in advance,Ludwig Lim

Why does pg_statio_user_tables report heap_blks_hit after index only scan?

[Aleksander Łukasz]

Hey pgsql-general,

while debugging some performance issues, I've noticed that index only 
scan (with number of heap fetches reported as 0) can still cause 
heap_blks_hit as reported by pg_statio_user_tables to increment (in my 
case by 1).


For example, on 14.4:

  create table test (a int);
  CREATE TABLE
  insert into test (a) select i from generate_series(1, 10) i;
  INSERT 0 10
  create index ix_test on test (a);
  CREATE INDEX
  vacuum analyze test;
  VACUUM
  select pg_stat_reset();
  pg_stat_reset
  ---

  (1 row)

  select pg_sleep(1);
  pg_sleep
  --

  (1 row)

  Expanded display is on.
  select * from pg_statio_user_tables;
  -[ RECORD 1 ]---+---
  relid   | 16384
  schemaname  | public
  relname | test
  heap_blks_read  | 447
  heap_blks_hit   | 102657
  idx_blks_read   | 1
  idx_blks_hit    | 0
  toast_blks_read |
  toast_blks_hit  |
  tidx_blks_read  |
  tidx_blks_hit   |

  Expanded display is off.
  explain analyze
  select a from test where a = 1;
  QUERY PLAN
---
  Index Only Scan using ix_test on test  (cost=0.29..4.31 rows=1 
width=4) (actual time=0.043..0.045 rows=1 loops=1)

    Index Cond: (a = 1)
    Heap Fetches: 0
  Planning Time: 0.146 ms
  Execution Time: 0.063 ms
  (5 rows)

  select pg_sleep(1);
  pg_sleep
  --

  (1 row)

  Expanded display is on.
  select * from pg_statio_user_tables;
  -[ RECORD 1 ]---+---
  relid   | 16384
  schemaname  | public
  relname | test
  heap_blks_read  | 447
  heap_blks_hit   | 102658
  idx_blks_read   | 3
  idx_blks_hit    | 1
  toast_blks_read |
  toast_blks_hit  |
  tidx_blks_read  |
  tidx_blks_hit   |

That is heap_blks_hit incremented by one after index only scan that 
supposedly did not fetch anything from the table.


Am I right assuming this is due to some non data related read, like 
checking visibility map, and this will always happen? Or maybe my 
methodology or other assumptions are wrong?


Thanks for your feedback.

PS: I'm doing those pg_sleeps because I've noticed those are (usually) 
needed for stats to refresh. Also, not sure why first pg_reset is not 
clearing pg_statio table (as this works for me in many different 
contexts)... Apparently I'm not fully understanding when those stats are 
affected and maybe this is the reason I've trouble interpreting this 
increment?

CPU is 100% azure rds postgreSQL-11

[Pavan Kumar S]

Hi Team,

One of our RDS postgresql -11 version CPU is drastically high continuously.
we are unable to find what is causing. the server is 4Vcore 100 GB
storage.  is there any way what is causing the issue.? we have datadog
integration with custom metrics.

we have done all vacuum and analyze operations, couple of restarts. as well.

any Help is appreciated.

thanks
Pavan

postgresql bug

[ml]

Hallo, 

there is a postgresql bug

/usr/local/bin/postgres -V
ld-elf.so.1: Shared object "libicui18n.so.70" not found, required by
"postgres"

Installed Version:
postgresql14-server-14.1

find / -name "*libicui*"
/usr/local/lib/libicuio.a
/usr/local/lib/libicui18n.so.71
/usr/local/lib/libicuio.so.71.1
/usr/local/lib/libicui18n.so.71.1
/usr/local/lib/compat/pkg/libicui18n.so.68.2
/usr/local/lib/compat/pkg/libicuio.so.68
/usr/local/lib/compat/pkg/libicuio.so.68.2
/usr/local/lib/compat/pkg/libicui18n.so.68
/usr/local/lib/libicui18n.so
/usr/local/lib/libicuio.so.71
/usr/local/lib/libicuio.so
/usr/local/lib/libicui18n.a
/compat/linux/usr/lib64/libicuio.so.50
/compat/linux/usr/lib64/libicui18n.so.50
/compat/linux/usr/lib64/libicui18n.so.50.2
/compat/linux/usr/lib64/libicuio.so.50.2
/compat/linux/usr/lib/libicui18n.so.50.2
/compat/linux/usr/lib/libicuio.so.50.2
/compat/linux/usr/lib/libicui18n.so.50
/compat/linux/usr/lib/libicuio.so.50

What should I do?

Franz

Re: - operator overloading not giving expected result

[Peter J. Holzer]

On 2022-07-05 13:52:32 +0530, Rajesh S wrote:
> We are migrating our database from Oracle to Postgresql.  In oracle we have
> used this syntax "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)" to get
> difference between two dates as a integer output (ex: 19).  But in Postgres 
> the
> same query returns result as "19 days".

Which PostgreSQL version is this?

I get 19 with PostgreSQL 11.16 and 14.0.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: postgresql bug

[Laurenz Albe]

On Fri, 2022-07-08 at 12:47 +0200, m...@ft-c.de wrote:
> there is a postgresql bug

An error need not be a bug.

> /usr/local/bin/postgres -V
> ld-elf.so.1: Shared object "libicui18n.so.70" not found, required by
> "postgres"
> 
> Installed Version:
> postgresql14-server-14.1
> 
> find / -name "*libicui*"
> /usr/local/lib/libicuio.a
> /usr/local/lib/libicui18n.so.71
> /usr/local/lib/libicuio.so.71.1
> /usr/local/lib/libicui18n.so.71.
> /usr/local/lib/compat/pkg/libicui18n.so.68.2
> /usr/local/lib/compat/pkg/libicuio.so.68
> /usr/local/lib/compat/pkg/libicuio.so.68.2
> /usr/local/lib/compat/pkg/libicui18n.so.68
> /usr/local/lib/libicui18n.so
> /usr/local/lib/libicuio.so.71
> /usr/local/lib/libicuio.so
> /usr/local/lib/libicui18n.a
> /compat/linux/usr/lib64/libicuio.so.50
> /compat/linux/usr/lib64/libicui18n.so.50
> /compat/linux/usr/lib64/libicui18n.so.50.2
> /compat/linux/usr/lib64/libicuio.so.50.2
> /compat/linux/usr/lib/libicui18n.so.50.2
> /compat/linux/usr/lib/libicuio.so.50.2
> /compat/linux/usr/lib/libicui18n.so.50
> /compat/linux/usr/lib/libicuio.so.50
> 
> What should I do?

Either install the older version if the ICU library, or build PostgreSQL
against the newer version.

Yours,
Laurenz Albe

Re: postgresql generate ddl returns FK with `()` in it

[Peter J. Holzer]

On 2022-07-07 07:05:53 -0600, Roman Gavrilov wrote:
> I have a schema with two tables, where the first table's composite 2-columns 
> FK
> points to the second table's 2-columns composite PK. But the order of columns
> is reversed.
> 
> When I use `Generate DDL` on the first table, FK definition comes back as:

PostgreSQL doesn't have a `Generate DDL` command. So the question is,
what program are you using to generate the ddl.

> Full details on SO:
> 
> https://stackoverflow.com/questions/72898296/what-is-in-postgresql-ddl

Ah, it's "DBeaver". (Please include relevant details when you ask a
question here - don't expect people to look at a stack overflow
question).

> How can I fix that `();` issue? Is this documented behavior?

My guess is that's a bug in DBeaver.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: postgresql generate ddl returns FK with `()` in it

[Francisco Olarte]

On Fri, 8 Jul 2022 at 12:37, Roman Gavrilov
 wrote:
> I have a schema with two tables, where the first table's composite 2-columns 
> FK points to the second table's 2-columns composite PK. But the order of 
> columns is reversed.

Note sql does not look at the NAMES of the foreign key columns or the
primary key columns, it matches them by position.

Also note you will have a higher success probability in this kind of
question if you provide the table definitions postgres is using,
pg_dump with the
-s, --schema-onlydump only the schema, no data
-t, --table=PATTERN  dump the specified table(s) only
options is a simple way to get those.

You have not even cited which programs has this "generate ddl" command.

> When I use `Generate DDL` on the first table, FK definition comes back as:
> ALTER TABLE eval.rubric_questions ADD CONSTRAINT rubric_questions_fk FOREIGN 
> KEY (rubric_id,rubric_version_id) REFERENCES ();
> How can I fix that `();` issue? Is this documented behavior?
> (I realize that I need to fix the schema, but in time-being I need to 
> generate code using entity framework, and it chokes in that).

Ah, you expect people to hunt your link to SO:
> Full details on SO:
> https://stackoverflow.com/questions/72898296/what-is-in-postgresql-ddl

This is frowned upon and lowers your success rate, AAMOF I've only
replied to this because I'm waiting for someone to get out to lunch
and burning some minutes, normally I would have just bitbucketed your
mail.

>From the SO one can see you are using DBbeaver, If I copied it right.
This seems to be a bug in it, not in postgres. You can have a pk on
columns (a,b,c,d) and have an fk on columns (w,x,y,z), in fact it is
common, like in the classical tree (pk=id, fk=parent_id references
same table).

But anyway, be careful, postgres does not match columns in keys by
name, but by position, and from the little detail you provided in SO
it appears that aside from trigering a bug in dbbeaver your schema is
totally wrong and will malfunction. Post more details and someone may
be able to help you.

Regards.
   Francisco Olarte.

Re: postgresql bug

[Christoph Moench-Tegeder]

## m...@ft-c.de (m...@ft-c.de):

> /usr/local/bin/postgres -V
> ld-elf.so.1: Shared object "libicui18n.so.70" not found, required by
> "postgres"

You screwd up your upgrades: that postgres binary was built against
ICU 70, but as hou have shown you have ICU 71 installed:

> find / -name "*libicui*"
> /usr/local/lib/libicuio.a
> /usr/local/lib/libicui18n.so.71
> /usr/local/lib/libicuio.so.71.1
> /usr/local/lib/libicui18n.so.71.1


> /usr/local/lib/compat/pkg/libicui18n.so.68.2
> /usr/local/lib/compat/pkg/libicuio.so.68
> /usr/local/lib/compat/pkg/libicuio.so.68.2
> /usr/local/lib/compat/pkg/libicui18n.so.68

And that looks like you're using portupgrade - in which case you need
to chase a lot of shared library dependencies yourself. pkg_libchk
from ports-mgmt/bsdadminscripts2 may help a little with that, but
it's still an error-prone process and a lot of toil. For that reason
I'd recommend either using packages (from the official builds) or
setting up your own poudriere; all other options will cost you a lot
of time and nerves and require a lot more knowledge and manual
intervention than "simple updates" are supposed to in 2022.

Regards,
Christoph

-- 
Spare Space

Re: Error when pasting function blocks into psql

[Francisco Olarte]

Hi Ludwig:

On Fri, 8 Jul 2022 at 12:37, Ludwig Isaac Lim  wrote:
> I noticed that I'm getting errors when copy and pasting code for stored 
> procedure from Windows directly into psql running in putty.
> To reproduce create a procedure that has multiple statements with each 
> statements having multiple lines, similar to the ones below:
...
> Copy and paste from Windows editor (or IDE) to a psql running in a Putty. 
> Chances are it will complain of syntax error. The error doesn't appear when I 
> copy and paste it to an empty file in vim, and the run:
> psql -f 
> Is there a way of prevent this kind of error when pasting directly into psql? 
> I'm using the latest version of PostgreSQL (server and client. Version 14.4)

You omitted the OS version which is running psql/vim, I assume it is
some king of *ix, probably linux, and which kind of paste you are
using in putty, IIRC it has a couple.

Probably you are sending extra spaces or \015 or something like that
which vim filters for you on save. You can probably confirm these by
pasting your lines into "cat > file" ( remember EOF ing after paste,
enter+^D is the usual way ) and trying to feed that file to psql.

Not having used windows or vim in 20 years I can only think on one
thing to help you with these kind of problems. You'll probably have od
(octal dump) installed. Try pasting the code to 'od -tx1 -tc' which
will give you a nice dump from where you can see if your
IDE/EDITOR/putty is plain nasty tricks ( if you tried pasting into cat
you can cat that file and the one from vim ).

Francisco Olarte.

Re: CPU is 100% azure rds postgreSQL-11

[Sunil Thakur]

Hi Pavan,

Could you please check the CPU utilization graph and the queries running
during the same timeframe.
I would be able to help more on this issue if I could have more information.

*Thanks and Regards,*
*Sunil M. K. Thakur*
*Database Architect*


On Fri, 8 Jul 2022 at 16:08, Pavan Kumar S  wrote:

> Hi Team,
>
> One of our RDS postgresql -11 version CPU is drastically high
> continuously. we are unable to find what is causing. the server is 4Vcore
> 100 GB storage.  is there any way what is causing the issue.? we have
> datadog integration with custom metrics.
>
> we have done all vacuum and analyze operations, couple of restarts. as
> well.
>
> any Help is appreciated.
>
> thanks
> Pavan
>

Re: - operator overloading not giving expected result

[Francisco Olarte]

Hi Rajesh:

On Fri, 8 Jul 2022 at 12:36, Rajesh S  wrote:
> We are migrating our database from Oracle to Postgresql.  In oracle we have 
> used this syntax "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)" to get 
> difference between two dates as a integer output (ex: 19).  But in Postgres 
> the same query returns result as "19 days".  Because of this we are getting 
> errors while assigning this query output to a numeric variable saying "ERROR: 
> invalid input syntax for type numeric: "1825 days"" and "ERROR: operator does 
> not exist: interval + integer".  To avoid changing the application code in 
> many places to extract the number of days alone, we tried operator 
> overloading concept as below.

Are you posting exact code? Because current doc states:

date - date → integer
Subtract dates, producing the number of days elapsed
date '2001-10-01' - date '2001-09-28' → 3

I remember it always working that way and 8.0 docs document it that
way too ( and I've used a lot of date arithmetic even in 7.x )

Maybe your EXACT code is not what you have pasted, but rather you
inserting dates in timestamp columns and then substracting said
columns? Because even your operations are defined in terms of
timestamptz, not dates.

Francisco Olarte.

Re: - operator overloading not giving expected result

[Christoph Moench-Tegeder]

## Rajesh S (rajes...@fincuro.com):

> We are migrating our database from Oracle to Postgresql.  In oracle we 
> have used this syntax "SELECT ('1999-12-30'::DATE) - 
> ('1999-12-11'::DATE)" to get difference between two dates as a integer 
> output (ex: 19).  But in Postgres the same query returns result as "19 
> days".

There's something fishy going on, as (date) - (date) returns integer
since a very long time (even the version 8.0 docs have that).
On the other hand, (timestamp) - (timestamp) gives an interval, so
first make sure you really got the data types right.

> CREATE OR REPLACE FUNCTION public.dt_minus_dt(
>      dt1 timestamp without time zone,
>      dt2 timestamp without time zone)

See? There's TIMESTAMP, not DATE.

>      LANGUAGE 'edbspl'

Is this really PostgreSQL or is it that fork - and if it's the fork,
does it behave the same way as stock PostgreSQL does? (I would be
surprised if that deviates in this place, but...).

>      SELECT DATE_PART('day', dt1::timestamp - dt2::timestamp)::integer 

And TIMESTAMP again.

Regards,
Christoph

-- 
Spare Space

Re: - operator overloading not giving expected result

[Tom Lane]

Christoph Moench-Tegeder  writes:
> ## Rajesh S (rajes...@fincuro.com):
>> We are migrating our database from Oracle to Postgresql.  In oracle we 
>> have used this syntax "SELECT ('1999-12-30'::DATE) - 
>> ('1999-12-11'::DATE)" to get difference between two dates as a integer 
>> output (ex: 19).  But in Postgres the same query returns result as "19 
>> days".

> There's something fishy going on, as (date) - (date) returns integer
> since a very long time (even the version 8.0 docs have that).
> On the other hand, (timestamp) - (timestamp) gives an interval, so
> first make sure you really got the data types right.

It's easy to demonstrate that that query does indeed return "19"
in stock Postgres.  Therefore, this is not stock Postgres.

>>     LANGUAGE 'edbspl'

> Is this really PostgreSQL or is it that fork - and if it's the fork,
> does it behave the same way as stock PostgreSQL does?

I'm no expert in this area, but my recollection is that Oracle's
type DATE is more nearly what we'd call TIMESTAMP (i.e., it includes
sub-day fields), and that EDB's fork follows Oracle's datatype naming.
So I'd not be at all surprised by this result if it's on EDB.

BTW, Oracle doesn't really accept "::" casting notation does it?
I always thought that was a pure Postgres-ism.

regards, tom lane

Re: Error when pasting function blocks into psql

[Tom Lane]

Ludwig Isaac Lim  writes:
> Hello:
> I noticed that I'm getting errors when copy and pasting code for stored 
> procedure from Windows directly into psql running in putty.
> To reproduce create a procedure that has multiple statements with each 
> statements having multiple lines, similar to the ones below:
> CREATE OR REPLACE PROCEDURE test()AS$$     UPDATE                     table1  
>     SET                   field1 = ?;

>        -- more SQL statements$$LANGUAGE SQL;
> Copy and paste from Windows editor (or IDE) to a psql running in a Putty. 
> Chances are it will complain of syntax error. The error doesn't appear when I 
> copy and paste it to an empty file in vim, and the run:
> psql -f 
> Is there a way of prevent this kind of error when pasting directly into psql?

A gotcha I frequently run into is that if there are any literal tab
characters in the text, pasting it into psql fails because the tabs
invoke tab-completion, producing either no text or unwanted text.
I don't know if that's an issue on Windows (does your psql build
do tab completion?), but if so, the workaround is to disable tab
completion by starting psql with the '-n' switch.

Another fairly likely possibility is that your paste contains
invisible characters that psql does not think are whitespace,
like non-break space ( ).  However, pasting into a file
wouldn't make that problem go away, so the tab idea seems
a shade more likely.

regards, tom lane

Re: - operator overloading not giving expected result

[Ravi Krishna]

LANGUAGE 'edbspl'

This is the root cause of your issue.


You are not using PGSQL, but EDB version of it which is compatible with Oracle 
PL/SQL.

Database designs

[Zahid Rahman]

Hi,
This link with hundreds of database designs is  expired.

http://databaseanswers.org/data_models/index_all_models.htm

Is there a similar  website with database designs  ?



https://www.backbutton.org

¯\_(ツ)_/¯
♡۶♡ ۶♡۶

Re: Database designs

[Pete Yunker]

Zahid,

This appears to be the most recently archived version.  All of the links on the 
page work.

https://web.archive.org/web/2021070100*/http://databaseanswers.org/data_models/index_all_models.htm
 


-Pete

> On Jul 8, 2022, at 6:06 PM, Zahid Rahman  wrote:
> 
> Hi,
> This link with hundreds of database designs is  expired.
> 
> http://databaseanswers.org/data_models/index_all_models.htm 
> 
> 
> Is there a similar  website with database designs  ?
> 
> 
> 
> https://www.backbutton.org 
> 
> ¯\_(ツ)_/¯
> ♡۶♡ ۶♡۶
>

Re: Database designs

[Josef Šimánek]

so 9. 7. 2022 v 0:53 odesílatel Pete Yunker
 napsal:
>
> Zahid,
>
> This appears to be the most recently archived version.  All of the links on 
> the page work.
>
> https://web.archive.org/web/2021070100*/http://databaseanswers.org/data_models/index_all_models.htm

I have checked that as well, but images are missing.

> -Pete
>
> On Jul 8, 2022, at 6:06 PM, Zahid Rahman  wrote:
>
> Hi,
> This link with hundreds of database designs is  expired.
>
> http://databaseanswers.org/data_models/index_all_models.htm
>
> Is there a similar  website with database designs  ?
>
>
>
> https://www.backbutton.org
>
> ¯\_(ツ)_/¯
> ♡۶♡ ۶♡۶
>
>
>

Re: Database designs

[Pete Yunker]

I’m seeing the ERD images on the 10 links I’ve checked.  What is an example of 
a detail page where you aren’t seeing them?

> On Jul 8, 2022, at 6:55 PM, Josef Šimánek  wrote:
> 
> so 9. 7. 2022 v 0:53 odesílatel Pete Yunker
>  napsal:
>> 
>> Zahid,
>> 
>> This appears to be the most recently archived version.  All of the links on 
>> the page work.
>> 
>> https://web.archive.org/web/2021070100*/http://databaseanswers.org/data_models/index_all_models.htm
> 
> I have checked that as well, but images are missing.
> 
>> -Pete
>> 
>> On Jul 8, 2022, at 6:06 PM, Zahid Rahman  wrote:
>> 
>> Hi,
>> This link with hundreds of database designs is  expired.
>> 
>> http://databaseanswers.org/data_models/index_all_models.htm
>> 
>> Is there a similar  website with database designs  ?
>> 
>> 
>> 
>> https://www.backbutton.org
>> 
>> ¯\_(ツ)_/¯
>> ♡۶♡ ۶♡۶
>> 
>> 
>> 

Re: Database designs

[Josef Šimánek]

so 9. 7. 2022 v 1:02 odesílatel Pete Yunker
 napsal:
>
> I’m seeing the ERD images on the 10 links I’ve checked.  What is an example 
> of a detail page where you aren’t seeing them?

For example here -
https://web.archive.org/web/20191227184610/http://www.databaseanswers.org/data_models/martin/index.htm.

> > On Jul 8, 2022, at 6:55 PM, Josef Šimánek  wrote:
> >
> > so 9. 7. 2022 v 0:53 odesílatel Pete Yunker
> >  napsal:
> >>
> >> Zahid,
> >>
> >> This appears to be the most recently archived version.  All of the links 
> >> on the page work.
> >>
> >> https://web.archive.org/web/2021070100*/http://databaseanswers.org/data_models/index_all_models.htm
> >
> > I have checked that as well, but images are missing.
> >
> >> -Pete
> >>
> >> On Jul 8, 2022, at 6:06 PM, Zahid Rahman  wrote:
> >>
> >> Hi,
> >> This link with hundreds of database designs is  expired.
> >>
> >> http://databaseanswers.org/data_models/index_all_models.htm
> >>
> >> Is there a similar  website with database designs  ?
> >>
> >>
> >>
> >> https://www.backbutton.org
> >>
> >> ¯\_(ツ)_/¯
> >> ♡۶♡ ۶♡۶
> >>
> >>
> >>
>

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

[jian he]

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