Postgresql从PHP脚本中复制批量数据 - 理想情况下使用ADODB

Question

I have custom software which includes a function to copy data between tables on postgresql servers. It does this 1 row at a time, which is fine when the servers are close together, but as I've started deploying servers where the latency is > 300ms this does not work well at all.

I believe the solution is to use the "COPY" statement, but I am having difficulty implementing it. I am using the ADODB php library

When I attempt a copy from a file I get the error "must be superuser to COPY to or from a file". The problem is that I don't know how to copy "from STDIN" where stdin is not actually piped to the PHP script. Is there any way to provide the stdin input as part of the sql command using ADODB, or is there an equivalent command which will allow me to do a batch insert without waiting for each individual insert ?

Answer 1

I solved the problem by using an insert command which had all the inserts in a single statement using "union all" - ie

$sql="INSERT INTO tablename (name,payload)
    select 'hello', 'world' union all
    select 'this', 'is a test' union all
    select 'and it', 'works'";
$q=$conn->Execute($sql);

One limitation of this solution was that strings need to be enquoted while integers for example, must not be. I thus needed to write some additional code to make sure some fields were enquoted but not others.

To find out which columns needed to be enquoted I used

$coltypes=$todb->GetAll("select column_name,data_type 
                        from information_schema.columns 
                        where table_name='".$totable."'");