Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux