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

On 8/6/21 1:11 AM, Pavel Stehule wrote:
>
> 2. 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.

>
> 3. How may I get postgresql to output the create table
> statement(s) for one or more tables of one database?
>
> ????
>
> 4. 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
>
> 5. 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
>
> 6. -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