Re: [SQL] backup
On Wed, Jun 10, 2009 at 10:38 AM, Jyoti Seth wrote: > I tried using /copy command. It is not giving any error but also not > creating any file. > Thanks > Jyoti Use copy command as follows if the OS is Linux, postgres=# copy (select * from test where setid>20) to '/var/tmp/t2.dmp'; COPY 3 postgres=# \q -bash-3.1$ cat /var/tmp/t2.dmp 123422 1 2009-01-08 00:00:00 dup1-2 987621 1 2009-01-10 00:00:00 dup2-1 1 21 1 2009-01-10 00:00:00 dup2-1 If it is Windows, use can use as, postgres=# copy (select *from test where a<6) to 'C:/Program Files/PostgreSQL/8.3/data/samp.sql'; COPY 9 Thanks, Abbas EnterpriseDB > > -Original Message- > From: [email protected] [mailto: > [email protected]] > On Behalf Of Jasen Betts > Sent: Tuesday, June 09, 2009 6:03 PM > To: [email protected] > Subject: Re: [SQL] backup > > On 2009-06-09, Jyoti Seth wrote: > > This is a multipart message in MIME format. > > > > --=_NextPart_000_003C_01C9E916.43A8D460 > > Content-Type: text/plain; > > charset="us-ascii" > > Content-Transfer-Encoding: 7bit > > > > Hi all, > > > > > > > > Can we take backup of specific data of a table (using where clause)? > > in psql: > > /copy (select * from table WHERE condition ) to 'FILENAME' > > requires postgres version >= 8.2 IIRC. > > you can use any query. > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] How can this INSERT fail?
Which version of postgresql it is ?
Best Regards,
Abbas
On Wed, Apr 25, 2012 at 5:15 PM, Peter Harris wrote:
> I'm trying to INSERT into a table, avoiding duplicates, using this sort of
> thing:
> -- --
> insert into buddyinvite (bi_user, bi_invited)
> select 'a', 'b'
> except
> select bi_user, bi_invited from buddyinvite where bi_user = 'a' and
> bi_invited = 'b'
> -- --
> I thought this should work, because if such a row already exists, the
> EXCEPT should remove the ('a','b') row from the select, and insert 0 rows.
>
> When I do this from psql it acts as I expect.
>
> But in my webserver logs, I get this error sometimes:
> -- --
> [23/Apr/2012:15:31:16] ERROR: error 'ERROR: duplicate key value violates
> unique constraint "buddyinvite_pkey"
> DETAIL: Key (bi_user, bi_invited)=(a, b) already exists.
> ' in 'insert into buddyinvite (bi_user, bi_invited) select 'a', 'b' except
> select bi_user, bi_invited from buddyinvite where bi_user = 'a' and
> bi_invited = 'b''
> -- --
> (verbatim except for anonymised user IDs)
>
> I can only assume it is possible for multiple transactions to overlap and
> one of them to miss the row so it doesn't appear in the EXCEPT SELECT but
> the row appears before the transaction commits and so an error occurs. My
> first thought was SELECT ... FOR UPDATE, but I can't do that within an
> EXCEPT.
>
> Can someone confirm whether I could avoid these errors by some form of SET
> TRANSACTION ISOLATION LEVEL, or let me know if I am just Doing It Wrong?
>
> To be honest, if there's no good solution I'm happy to simply swallow the
> exceptions, because I don't care (in this case) which of two competing
> transactions gets to insert the row. However, if I am doing something
> stupid I'd like to be put right!
>
> Peter Harris
> Software Engineer
> HuzuTech Ltd.
>
[SQL] working with multidimensional arrays in plpgsql
hello everyone i am having problem with multidimensional arrays in plpgsql following is the source code of the function which i am trying to run CREATE OR REPLACE FUNCTION test() RETURNS VOID AS $$ DECLARE x INTEGER[10][10]; tmp VARCHAR(40); BEGIN x[3][1] := '20'; --i have even tried x[3][1] = 20 tmp := x[3][1]; RAISE NOTICE '%', tmp; RETURN; END; $$LANGUAGE 'plpgsql'; As you might have observed here, the actual problem is how to do assignment to multidimensional array locations using the subscript operater. Thank you ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] working with multidimensional arrays in plpgsql
thank you very much for your reply
I did as you specified and it worked fine
thankz :)
On Wed, 26 Jan 2005 09:29:53 +, Richard Huxton wrote:
> Sibtay Abbas wrote:
> > hello everyone
> >
> > i am having problem with multidimensional arrays in plpgsql following
> > is the source code of the function which i am trying to run
>
>
> > DECLARE
> > x INTEGER[10][10];
>
> > x[3][1] := '20'; --i have even tried x[3][1] = 20
>
> > As you might have observed here, the actual problem is
> > how to do assignment to multidimensional array locations using the
> > subscript operater.
>
> Actually, the problem is that the array "x" has been initialised to
> NULL. Therefore, you can't set individual values. Try a line like:
>x := '{}';
>
> --
>Richard Huxton
>Archonet Ltd
>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
