Postgres Pro
Downloads
Home   >   mailing lists

Thread: Series of 10 questions about the use of postgresql, generally.

Series of 10 questions about the use of postgresql, generally.

From
A Z
Date:
I have been going through the free online book LEARNING postgresql book, that has been compiled by Stack Overflow contributors. I have gotten to the point where I have the following series of unanswered questions:

  1. Are there free script for CREATE TYPE (native type) or types out there, online, free for commercial use? With function support, too? Can someone reply with a link or a suggestion?
  1. If I am using the COPY command for input and output around *.csv files, how may I specify internal tuple delimiters of ',', but at the same time also specify an end of line delimeter of ';' ?
  1. How may I get postgresql to output the create table statement(s) for one or more tables of one database?
  1. 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? How may I use COPY, or another default native postgresql function, to export or import create table commands to and from a *.csv file?
  1. 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 its create table statements and all insert statements, and any other associated object, in one hit? Or is this ill advised?
  1. -How exactly do Intervals work, via themselves and in relation to the other provided native datatypes? What are Intervals used for?
  1. 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?
  1. When I am using INHERITS to setup inheritance between two tables, will this mean that all data inserted into the Parent table is automatically updated into the Child table(s), or not?
  1. What does the VARYING keyword do to an applicable type in a create table statement? CHARACTER VARYING becomes entirely equivalent to VARCHAR. Static, limited types become more dynamic and are unlimited.
  1. How may I access the log for the details of a normal table, or similar?

Re: Series of 10 questions about the use of postgresql, generally.

From
Pavel Stehule
Date:
Hi

pá 6. 8. 2021 v 6:46 odesílatel A Z <poweruserm@live.com.au> napsal:
I have been going through the free online book LEARNING postgresql book, that has been compiled by Stack Overflow contributors. I have gotten to the point where I have the following series of unanswered questions:

  1. Are there free script for CREATE TYPE (native type) or types out there, online, free for commercial use? With function support, too? Can someone reply with a link or a suggestion?
????
 
  1. If I am using the COPY command for input and output around *.csv files, how may I specify internal tuple delimiters of ',', but at the same time also specify an end of line delimeter of ';' ?
you cannot to specify end of line delimiter in Postgres

  1. How may I get postgresql to output the create table statement(s) for one or more tables of one database?
????
  1. 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? How may I use COPY, or another default native postgresql function, to export or import create table commands to and from a *.csv file?
COPY can be used just for one table in one time

if you want to make DDL command (CREATE TABLE), then you should to run pg_dump -t tablename -a
 
  1. 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 its create table statements and all insert statements, and any other associated object, in one hit? Or is this ill advised?
pg_dump
 
  1. -How exactly do Intervals work, via themselves and in relation to the other provided native datatypes? What are Intervals used for?
Interval is Postgres native type, that helps with work with date operations that are not possible to map to some basic unit. Internally it is a structure with numeric fields - years, months, days, hours, seconds. For example - I can work with a value '3 months', but without knowledge of the beginning, I cannot say how many days this interval has. You can create interval value by constructor function make_interval, or directly from literal, or you can divide two timestamp values.

  1. 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?
????
  1. When I am using INHERITS to setup inheritance between two tables, will this mean that all data inserted into the Parent table is automatically updated into the Child table(s), or not?
No. Data is always inserted to the child table. When you look at the parent table, Postgres creates an ad-hoc union of this table and all child tables.
 
  1. What does the VARYING keyword do to an applicable type in a create table statement? CHARACTER VARYING becomes entirely equivalent to VARCHAR. Static, limited types become more dynamic and are unlimited.
char type has fixed size - the content is filled by spaces. character varying or varchar (these names are synonyms) has dynamic size. 
 
  1. How may I access the log for the details of a normal table, or similar?

????

Maybe you find some reply for your questions in FAQ


Regards

Pavel

Re: Series of 10 questions about the use of postgresql, generally.

From
"David G. Johnston"
Date:
On Thu, Aug 5, 2021 at 9:46 PM A Z <poweruserm@live.com.au> wrote:
  1. Are there free script for CREATE TYPE (native type) or types out there, online, free for commercial use? With function support, too? Can someone reply with a link or a suggestion?
Look at the PostgreSQL source code, contrib directory, if you want an idea of how extension types can be added to the system.
 
  1. If I am using the COPY command for input and output around *.csv files, how may I specify internal tuple delimiters of ',', but at the same time also specify an end of line delimeter of ';' ?
The COPY command references tells you what is possible.  It is not intended to be particularly flexible, instead focusing on doing one thing extremely well.  The data ecosystem has plenty of tools to convert files from one format to another.

>> 3. How may I get postgresql to output the create table statement(s) for one or more tables of one database?

Easiest way, pg_dump: https://www.postgresql.org/docs/current/app-pgdump.html

psql, and many other "admin" style database client programs also provide a facility to "show creation command" for a selected object, table or otherwise.  This proves it is possible to also just dive into the catalogs and system functions to write something yourself - but why re-invent the wheel.

I'm unaware of an in-server function that provides this, a quick skim in the obvious place turns up nothing for table creation.
https://www.postgresql.org/docs/current/functions-info.html

  1. 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? How may I use COPY, or another default native postgresql function, to export or import create table commands to and from a *.csv file?
You should probably extend your consideration for what tooling you will use beyond native PostgreSQL.  Data manipulation technology for import/export, particularly via CSV, tends to get more attention from the community than the main project.

  1. 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 its create table statements and all insert statements, and any other associated object, in one hit? Or is this ill advised?
If you don't have OS command line capability then you aren't responsible for the backups - or whatever platform you are using should be supplying you some alternative.

  1. -How exactly do Intervals work, via themselves and in relation to the other provided native datatypes? What are Intervals used for?
Almost exactly: https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT

You can find "exactly" in the source code, but I would suggest that you don't really want that level of detail.

As intervals are durations of time they naturally have operators that allow them to interact with the various datetime types.

https://www.postgresql.org/docs/current/functions-datetime.html

  1. 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?
OPTIONS isn't a function, is a clause label, like "SELECT" or "FROM", that also requires the stuff pertaining to it be surrounding by parentheses, like "FROM (SELECT 1 FROM tbl) AS tbl1" (FROM is not a function...).

schema_name
This option, which can be specified for a foreign table, gives the schema name to use for the foreign table on the remote server. If this option is omitted, the name of the foreign table's schema is used.
CREATE FOREIGN TABLE and IMPORT FOREIGN SCHEMA are mutually exclusive ways to gets foreign table linked into the local database.

If you have additional questions on this topic I would suggest supplying actual code if possible - your description here omit too much context.

  1. When I am using INHERITS to setup inheritance between two tables, will this mean that all data inserted into the Parent table is automatically updated into the Child table(s), or not?
When you are using table inheritance stop and go read this:  https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_table_inheritance

  1. What does the VARYING keyword do to an applicable type in a create table statement? CHARACTER VARYING becomes entirely equivalent to VARCHAR. Static, limited types become more dynamic and are unlimited.
"character varying" is the SQL standard data type - it is an atomic label.  PostgreSQL provides "varchar" as an alias.  The "varying" is a keyword but it isn't a thing - it only has to be a keyword because the SQL Standard makes implementer's lives difficult by allowing spaces in data type names (I've never actually seen a complaint of this nature, and I don't do grammar/parser writing, but I imagine this choice isn't popular).

  1. How may I access the log for the details of a normal table, or similar?
The "log" doesn't contain details about permanent objects usually.  Maybe you mis-heard the word "catalog"?  https://www.postgresql.org/docs/current/catalogs.html

David J.

Re: Series of 10 questions about the use of postgresql, generally.

From
Gavin Flower
Date:
On 6/08/21 4:45 pm, A Z wrote:
> I have been going through the free online book LEARNING postgresql 
> book, that has been compiled by Stack Overflow contributors. I have 
> gotten to the point where I have the following series of unanswered 
> questions:
[...]
>
>  9. What does the VARYING keyword do to an applicable type in a create
>     table statement? CHARACTER VARYING becomes entirely equivalent to
>     VARCHAR. Static, limited types become more dynamic and are unlimited.
>
I would suggest that you consider using the type 'text' instead.

see:  https://www.postgresql.org/docs/13/datatype-character.html


[...]

Re: Series of 10 questions about the use of postgresql, generally.

From
Mladen Gogala
Date:


On 8/6/21 1:11 AM, Pavel Stehule wrote:
 
  1. If I am using the COPY command for input and output around *.csv files, how may I specify internal tuple delimiters of ',', but at the same time also specify an end of line delimeter of ';' ?
you cannot to specify end of line delimiter in Postgres

You can specify end of line delimiter in Perl, Python or PHP. It is highly advisable to learn a scripting language or two when working with databases. With all databases, not just Postgres.



  1. How may I get postgresql to output the create table statement(s) for one or more tables of one database?
????
  1. 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? How may I use COPY, or another default native postgresql function, to export or import create table commands to and from a *.csv file?
COPY can be used just for one table in one time

if you want to make DDL command (CREATE TABLE), then you should to run pg_dump -t tablename -a
 
  1. 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 its create table statements and all insert statements, and any other associated object, in one hit? Or is this ill advised?
pg_dump
 
  1. -How exactly do Intervals work, via themselves and in relation to the other provided native datatypes? What are Intervals used for?
Interval is Postgres native type, that helps with work with date operations that are not possible to map to some basic unit. Internally it is a structure with numeric fields - years, months, days, hours, seconds. For example - I can work with a value '3 months', but without knowledge of the beginning, I cannot say how many days this interval has. You can create interval value by constructor function make_interval, or directly from literal, or you can divide two timestamp values.

Interval is a SQL standard data type for adding and subtracting dates. It is supported by a gazillion of other databases:

https://www.ibm.com/docs/en/informix-servers/12.10?topic=types-sql-datetime-interval-data

https://www.mysqltutorial.org/mysql-interval/

https://www.oracletutorial.com/oracle-basics/oracle-interval/

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

The only major database that does not support the interval type is SQL Server. SQL Server has a major problem with SQL standards. It also doesn't support FETCH  FIRST <n> ROWS ONLY OFFSET <n> syntax. Of course, this group is not about SQL Server so I'll leave it at that.


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com