pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBulk_insert in postgresql like Microsoft SQL server
9+ messages / 5 participants
[nested] [flat]
* Bulk_insert in postgresql like Microsoft SQL server
@ 2018-12-19 17:28 Devendra Yadav <devendra.857@gmail.com>
2018-12-19 17:40 ` Re: Bulk_insert in postgresql like Microsoft SQL server Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-12-19 17:47 ` Re: Bulk_insert in postgresql like Microsoft SQL server David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Devendra Yadav @ 2018-12-19 17:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Dear all,
We need to provide copy rights to our developer's for *bulk* *inserts*. But
the case is that, for it to happen it needs *superuser* permission which is
very expensive thing a DBA can grant.
Can anybody please suggest if there are such *tools* or utility through
which this can be achieved and with minimal rights.
Thanks and Regards,
Devendra Yadav
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Bulk_insert in postgresql like Microsoft SQL server
2018-12-19 17:28 Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
@ 2018-12-19 17:40 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-12-19 17:45 ` Re: Bulk_insert in postgresql like Microsoft SQL server SBob <sbob@quadratum-braccas.com>
2018-12-19 17:45 ` Re: Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
1 sibling, 2 replies; 9+ messages in thread
From: Shreeyansh Dba @ 2018-12-19 17:40 UTC (permalink / raw)
To: Devendra Yadav <devendra.857@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Devendra,
You can use copy command for Bulk Insert in PostgreSQL.
On Wed, Dec 19, 2018 at 10:59 PM Devendra Yadav <devendra.857@gmail.com>
wrote:
> Dear all,
>
> We need to provide copy rights to our developer's for *bulk* *inserts*.
> But the case is that, for it to happen it needs *superuser* permission
> which is very expensive thing a DBA can grant.
> Can anybody please suggest if there are such *tools* or utility through
> which this can be achieved and with minimal rights.
>
> Thanks and Regards,
> Devendra Yadav
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Bulk_insert in postgresql like Microsoft SQL server
2018-12-19 17:28 Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
2018-12-19 17:40 ` Re: Bulk_insert in postgresql like Microsoft SQL server Shreeyansh Dba <shreeyansh2014@gmail.com>
@ 2018-12-19 17:45 ` SBob <sbob@quadratum-braccas.com>
1 sibling, 0 replies; 9+ messages in thread
From: SBob @ 2018-12-19 17:45 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Use the \copy command instead of the COPY command
https://www.postgresql.org/docs/11/app-psql.html
On 12/19/18 10:40 AM, Shreeyansh Dba wrote:
>
> Hi Devendra,
>
> You can use copy command for Bulk Insert in PostgreSQL.
>
> On Wed, Dec 19, 2018 at 10:59 PM Devendra Yadav
> <devendra.857@gmail.com <mailto:devendra.857@gmail.com>> wrote:
>
> Dear all,
>
> We need to provide copy rights to our developer's for *bulk*
> *inserts*. But the case is that, for it to happen it needs
> *superuser* permission which is very expensive thing a DBA can grant.
> Can anybody please suggest if there are such *tools* or utility
> through which this can be achieved and with minimal rights.
>
> Thanks and Regards,
> Devendra Yadav
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Bulk_insert in postgresql like Microsoft SQL server
2018-12-19 17:28 Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
2018-12-19 17:40 ` Re: Bulk_insert in postgresql like Microsoft SQL server Shreeyansh Dba <shreeyansh2014@gmail.com>
@ 2018-12-19 17:45 ` Devendra Yadav <devendra.857@gmail.com>
2018-12-20 12:17 ` Re: Bulk_insert in postgresql like Microsoft SQL server Shreeyansh Dba <shreeyansh2014@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Devendra Yadav @ 2018-12-19 17:45 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hello Shreeyansh,
You're talking Copy command in psql utility or in pgadmin. In pgadmin it
needs superuser rights. Have not tried it from windows cmd, as the
application server is on windows.
Thanks,
Devendra Yadav
On Wed, Dec 19, 2018, 11:12 PM Shreeyansh Dba <shreeyansh2014@gmail.com
wrote:
>
> Hi Devendra,
>
> You can use copy command for Bulk Insert in PostgreSQL.
>
> On Wed, Dec 19, 2018 at 10:59 PM Devendra Yadav <devendra.857@gmail.com>
> wrote:
>
>> Dear all,
>>
>> We need to provide copy rights to our developer's for *bulk* *inserts*.
>> But the case is that, for it to happen it needs *superuser* permission
>> which is very expensive thing a DBA can grant.
>> Can anybody please suggest if there are such *tools* or utility through
>> which this can be achieved and with minimal rights.
>>
>> Thanks and Regards,
>> Devendra Yadav
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Bulk_insert in postgresql like Microsoft SQL server
2018-12-19 17:28 Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
2018-12-19 17:40 ` Re: Bulk_insert in postgresql like Microsoft SQL server Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-12-19 17:45 ` Re: Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
@ 2018-12-20 12:17 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Shreeyansh Dba @ 2018-12-20 12:17 UTC (permalink / raw)
To: Devendra Yadav <devendra.857@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Yes, copy command required superuser privileges. In pgadmin, you can use
\copy (use psql console pgadminIII) or Import/Export option for bulk insert.
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Wed, Dec 19, 2018 at 11:16 PM Devendra Yadav <devendra.857@gmail.com>
wrote:
> Hello Shreeyansh,
>
> You're talking Copy command in psql utility or in pgadmin. In pgadmin it
> needs superuser rights. Have not tried it from windows cmd, as the
> application server is on windows.
>
> Thanks,
> Devendra Yadav
>
> On Wed, Dec 19, 2018, 11:12 PM Shreeyansh Dba <shreeyansh2014@gmail.com
> wrote:
>
>>
>> Hi Devendra,
>>
>> You can use copy command for Bulk Insert in PostgreSQL.
>>
>> On Wed, Dec 19, 2018 at 10:59 PM Devendra Yadav <devendra.857@gmail.com>
>> wrote:
>>
>>> Dear all,
>>>
>>> We need to provide copy rights to our developer's for *bulk* *inserts*.
>>> But the case is that, for it to happen it needs *superuser* permission
>>> which is very expensive thing a DBA can grant.
>>> Can anybody please suggest if there are such *tools* or utility through
>>> which this can be achieved and with minimal rights.
>>>
>>> Thanks and Regards,
>>> Devendra Yadav
>>>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Bulk_insert in postgresql like Microsoft SQL server
2018-12-19 17:28 Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
@ 2018-12-19 17:47 ` David G. Johnston <david.g.johnston@gmail.com>
2018-12-19 17:58 ` Re: Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2018-12-19 17:47 UTC (permalink / raw)
To: Devendra Yadav <devendra.857@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Dec 19, 2018 at 10:29 AM Devendra Yadav <devendra.857@gmail.com> wrote:
> But the case is that, for it to happen it needs superuser permission which
It only requires superuser permission if you ask the server to COPY a
file from the local filesystem. Developers and programs should be
using client libraries and feeding the data to be copied (in or out)
to the server via the connection (which, for the server, is a
stdin/stdout stream). psql provides this via \copy - other libraries
have their own methods.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Bulk_insert in postgresql like Microsoft SQL server
2018-12-19 17:28 Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
2018-12-19 17:47 ` Re: Bulk_insert in postgresql like Microsoft SQL server David G. Johnston <david.g.johnston@gmail.com>
@ 2018-12-19 17:58 ` Devendra Yadav <devendra.857@gmail.com>
2018-12-20 13:25 ` Re: Bulk_insert in postgresql like Microsoft SQL server Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 9+ messages in thread
From: Devendra Yadav @ 2018-12-19 17:58 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi David,
Thanks for the information David. I'll check that. We have most of our
applications in Java and .net, if you can please highlight on these ?
Thanks,
Devendra Yadav
On Wed, Dec 19, 2018, 11:17 PM David G. Johnston <david.g.johnston@gmail.com
wrote:
> On Wed, Dec 19, 2018 at 10:29 AM Devendra Yadav <devendra.857@gmail.com>
> wrote:
> > But the case is that, for it to happen it needs superuser permission
> which
>
> It only requires superuser permission if you ask the server to COPY a
> file from the local filesystem. Developers and programs should be
> using client libraries and feeding the data to be copied (in or out)
> to the server via the connection (which, for the server, is a
> stdin/stdout stream). psql provides this via \copy - other libraries
> have their own methods.
>
> David J.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Bulk_insert in postgresql like Microsoft SQL server
2018-12-19 17:28 Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
2018-12-19 17:47 ` Re: Bulk_insert in postgresql like Microsoft SQL server David G. Johnston <david.g.johnston@gmail.com>
2018-12-19 17:58 ` Re: Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
@ 2018-12-20 13:25 ` Thomas Kellerer <spam_eater@gmx.net>
2018-12-20 13:50 ` Re: Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Thomas Kellerer @ 2018-12-20 13:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Devendra Yadav schrieb am 19.12.2018 um 18:58:
>> On Wed, Dec 19, 2018 at 10:29 AM Devendra Yadav <devendra.857@gmail.com <mailto:devendra.857@gmail.com>> wrote:
>> > But the case is that, for it to happen it needs superuser permission which
>>
>> It only requires superuser permission if you ask the server to COPY a
>> file from the local filesystem. Developers and programs should be
>> using client libraries and feeding the data to be copied (in or out)
>> to the server via the connection (which, for the server, is a
>> stdin/stdout stream). psql provides this via \copy - other libraries
>> have their own methods.
> Thanks for the information David. I'll check that. We have most of
> our applications in Java and .net, if you can please highlight on
> these ?
With JDBC you can use the CopyManager API together with a "COPY FROM STDIN" statement.
https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html
There are some examples on Stackoverflow:
https://stackoverflow.com/questions/6958965
https://stackoverflow.com/questions/46988855
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Bulk_insert in postgresql like Microsoft SQL server
2018-12-19 17:28 Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
2018-12-19 17:47 ` Re: Bulk_insert in postgresql like Microsoft SQL server David G. Johnston <david.g.johnston@gmail.com>
2018-12-19 17:58 ` Re: Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
2018-12-20 13:25 ` Re: Bulk_insert in postgresql like Microsoft SQL server Thomas Kellerer <spam_eater@gmx.net>
@ 2018-12-20 13:50 ` Devendra Yadav <devendra.857@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Devendra Yadav @ 2018-12-20 13:50 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org
Dear All,
Thank you all for your help. I am able to achieve the same.
Thanks and Regards,
Devendra Yadav
On Thu, Dec 20, 2018, 6:56 PM Thomas Kellerer <spam_eater@gmx.net wrote:
> Devendra Yadav schrieb am 19.12.2018 um 18:58:
> >> On Wed, Dec 19, 2018 at 10:29 AM Devendra Yadav <
> devendra.857@gmail.com <mailto:devendra.857@gmail.com>> wrote:
> >> > But the case is that, for it to happen it needs superuser
> permission which
> >>
> >> It only requires superuser permission if you ask the server to COPY
> a
> >> file from the local filesystem. Developers and programs should be
> >> using client libraries and feeding the data to be copied (in or out)
> >> to the server via the connection (which, for the server, is a
> >> stdin/stdout stream). psql provides this via \copy - other
> libraries
> >> have their own methods.
>
> > Thanks for the information David. I'll check that. We have most of
> > our applications in Java and .net, if you can please highlight on
> > these ?
> With JDBC you can use the CopyManager API together with a "COPY FROM
> STDIN" statement.
>
>
> https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html
>
> There are some examples on Stackoverflow:
>
> https://stackoverflow.com/questions/6958965
> https://stackoverflow.com/questions/46988855
>
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2018-12-20 13:50 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-19 17:28 Bulk_insert in postgresql like Microsoft SQL server Devendra Yadav <devendra.857@gmail.com>
2018-12-19 17:40 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-12-19 17:45 ` SBob <sbob@quadratum-braccas.com>
2018-12-19 17:45 ` Devendra Yadav <devendra.857@gmail.com>
2018-12-20 12:17 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-12-19 17:47 ` David G. Johnston <david.g.johnston@gmail.com>
2018-12-19 17:58 ` Devendra Yadav <devendra.857@gmail.com>
2018-12-20 13:25 ` Thomas Kellerer <spam_eater@gmx.net>
2018-12-20 13:50 ` Devendra Yadav <devendra.857@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox