Re: PostgreSQL general set of Questions.

On Mon, 9 Aug 2021 at 12:14, A Z <poweruserm(at)live(dot)com(dot)au> 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?
>
> PostgreSQL: Documentation: 13: Chapter 8. Data Types
<https://www.postgresql.org/docs/current/datatype.html>
PostgreSQL: Documentation: 13: CREATE TYPE
<https://www.postgresql.org/docs/current/sql-createtype.html>
postgres/create_type.sql at master · postgres/postgres (github.com)
<https://github.com/postgres/postgres/blob/master/src/test/regress/sql/create_type.sql>

> 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?
> If not possible, what can you do at the command line, outside
> of PostgreSQL?
>

postgres/describe.c at f68b609230689f9886a46e5d9ab8d6cdd947e0dc ·
postgres/postgres (github.com)
<https://github.com/postgres/postgres/blob/f68b609230689f9886a46e5d9ab8d6cdd947e0dc/src/bin/psql/describe.c#L1604>
what is your goal? you can clone a table schema using *create table*
create table y (like x INCLUDING ALL);
PostgreSQL: Documentation: 13: CREATE TABLE
<https://www.postgresql.org/docs/current/sql-createtable.html>
<https://www.postgresql.org/docs/current/sql-createtable.html>

>
> 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?
>

it should be fairly easy to script it though,

do $$
declare tbl text; stmt text;
begin
for tbl in select table_name from information_schema.tables where
table_name in ('aa', 'bb', 'cc') loop
stmt = format($_$ copy %I TO '/tmp/%s'; $_$, tbl, tbl);
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE: copy aa TO '/tmp/aa';
NOTICE: copy bb TO '/tmp/bb';
NOTICE: copy cc TO '/tmp/cc';
DO
postgres=# \! cat /tmp/aa
1
postgres=# \! cat /tmp/bb
1
postgres=# \! cat /tmp/cc
1

> 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 objects as well, in one hit?
> Or is this ill-advised?
>
>
i saw some tools doing schema sync, but they too used pg_dump cmd line
ankane/pgsync: Sync data from one Postgres database to another (github.com)
<https://github.com/ankane/pgsync>

also, its all there in the source though,
postgres/pg_dump.c at c30f54ad732ca5c8762bb68bbe0f51de9137dd72 ·
postgres/postgres (github.com)
<https://github.com/postgres/postgres/blob/c30f54ad732ca5c8762bb68bbe0f51de9137dd72/src/bin/pg_dump/pg_dump.c#L15830>
If you really want that bad, you can enable log all statements,
and run a pg_dump or psql \d+ and see what sql is generated for that pid,
and start building up on it.

> 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?
>
I think this is wrt fdw. What is your goal here?
I am not sure what you want would make sense. there can be 100s of schema
on remote server. there might be same table on diff schemas.
how would your import know, which one to use etc.
postgres/postgres_fdw.sql at c30f54ad732ca5c8762bb68bbe0f51de9137dd72 ·
postgres/postgres (github.com)
<https://github.com/postgres/postgres/blob/c30f54ad732ca5c8762bb68bbe0f51de9137dd72/contrib/postgres_fdw/sql/postgres_fdw.sql>

> 6) How may I access, via a query, the log for the details of a normal
> table, or similar?
>
What log? an example of what you want would be more helpful. even if it
does not work or exists, you can hypothetically run some command and say
when i `run this command|query`
i should `get this output`
but i `get this output`

> 7) 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 calls on DECIMAL and not on
> DOUBLE PRECISION, that do not produce any error values or amounts around
> key inputs? Could someone refer me to a website that has a script that is
> such?
>

imho, your questions where you suggest you have found something not as
expected. It would be best if you could simulate the same on
DB Fiddle - SQL Database Playground (db-fiddle.com)
<https://www.db-fiddle.com/> and put out the comment, what was expected,
what you got.

--
Thanks,
Vijay
Mumbai, India