Responses in-line:
On 8/9/21 10:01 PM, A Z wrote:
1) Are there free scripts for CREATE TYPE (native type), more
advanced, <date> or <datetime> sorts of types out there, online, free
for commercial use? With function support, too? Can someone reply with
a link or a suggestion?
ORAFCE? That extension has Oracle date/time functions.
2) How may I get PostgreSQL to output the create table statement(s)
for one or more tables inside one database, without issuing
instructions via the command line, but only inside a database login,
as a query or pl/sql?
Postgres doesn't store DDL into the catalog, DDL has to be created from
the catalog data. I would advise pg_tables, pg_attribute and pg_indexes.
That shouldn't be too hard of an exercise with PlPgSQL, If you want, you
can play with the ddlx extension: https://pgxn.org/dist/ddlx/
3) I know that I can use COPY to import or export one database table
between it and a *.csv file. Can I use it to do this with multiple
TABLES and *.csv files specified in one COPY COMMAND, or not?
You can not. Copy does only one table at one time. Of course, you can
use "screen" and do multiple tables from multiple windows or something
similar.
4) In the absence of OS command line instructions, is there an
internal PostgreSQL way, via COPY or another function for example, to
backup an entire database, with all it's create table statements and
all insert statements, and any other associated object, in one hit? Or
is this ill-advised?
Postgres pg_dump utility will do that. The results should be interesting
for a muti-TB database. BTW, any backup utility, even the plain and
simple pg_basebackup with --format=tar will be able to restore the
database and recover the database. You can always reconstruct the DDL
with pg_dump --schema-only. PgAdmin4 will also provide the DDL on request.
5) When setting up communication to remote databases on remote
machines, I need to use the OPTIONS() function. It seems to require as
its first function parameter, the schema of the table (the second
parameter) that it wants to access. Can I supply a null schema, and
still be able to reference the remote table, or must I also make use
of IMPORT FOREIGN SCHEMA?
6) I have found that the native trigonometry functions, namely the
radians versions, do produce error results around key trigonometry
input values. I have discovered that these errors persist, even if I
cast the input parameter away from DOUBLE PRECISION and into DECIMAL.
I would like to know if there are any freely available scripts out
there that include Arbitrary Precision mathematical functions support
that work on DECIMAL and not on DOUBLE PRECISION, that do not produce
any error values around key inputs? Could someone refer me to a
website that has a script that is such?
PlPgPython can use numpy: https://numpy.org
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
