Re: [GENERAL] if exists...does it exists for insert statments?
--- On Tue, 12/11/07, Pau Marc Munoz Torres <[EMAIL PROTECTED]> wrote: > Thanks, i'll test it tomorrow OOPS, I just noticed a mistake. INSERT INTO SP SELECT a, b FROM ( VALUES ( 'cesp', 'sp' )) AS tmp( a, b ) LEFT JOIN Sp ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b) WHERE (Sp.col1,Sp.col2) IS NULL; I forgot the parentheses that the FROM clause requires when using the VALUES predicate. Also remember that this only works in PostgreSQL versions >= 8.2. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] if exists...does it exists for insert statments?
Thanks, i'll test it tomorrow
pau
2007/12/11, Richard Broersma Jr <[EMAIL PROTECTED]>:
>
> --- On Tue, 12/11/07, Pau Marc Munoz Torres <[EMAIL PROTECTED]> wrote:
> > could i use a sentence similar to the mysql sentence
> >
> > insert if not exist into SP values
> > ('cesp','sp');
> >
> > in postgresql?
>
> Using standard ANSI-SQL the statement could be re-written:
>
> INSERT INTO SP
> SELECT a, b
>FROM VALUES ( 'cesp', 'sp' ) AS tmp( a, b )
> LEFT JOIN Sp
> ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b)
> WHERE (Sp.col1,Sp.col2) IS NULL;
>
> Regards,
> Richard Broersma Jr.
>
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
Re: [GENERAL] if exists...does it exists for insert statments?
--- On Tue, 12/11/07, Pau Marc Munoz Torres <[EMAIL PROTECTED]> wrote:
> could i use a sentence similar to the mysql sentence
>
> insert if not exist into SP values
> ('cesp','sp');
>
> in postgresql?
Using standard ANSI-SQL the statement could be re-written:
INSERT INTO SP
SELECT a, b
FROM VALUES ( 'cesp', 'sp' ) AS tmp( a, b )
LEFT JOIN Sp
ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b)
WHERE (Sp.col1,Sp.col2) IS NULL;
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 1: 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
Re: [GENERAL] if exists...does it exists for insert statments?
Hello
On 11/12/2007, Pau Marc Munoz Torres <[EMAIL PROTECTED]> wrote:
> could i use a sentence similar to the mysql sentence
>
> insert if not exist into SP values ('cesp','sp');
>
insert into sp
select 'cesp','sp'
except
select * from SP;
Regards
Pavel Stehule
> in postgresql?
>
> pau
>
>
> --
> Pau Marc Muñoz Torres
>
> Laboratori de Biologia Computacional
> Institut de Biotecnologia i Biomedicina Vicent Villar
>
> Universitat Autonoma de Barcelona
> E-08193 Bellaterra (Barcelona)
>
> telèfon: 93 5812807
> Email : [EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] if exists...does it exists for insert statments?
could i use a sentence similar to the mysql sentence
insert if not exist into SP values ('cesp','sp');
in postgresql?
pau
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
Re: [GENERAL] IF EXISTS
On 9/21/06, stevethames <[EMAIL PROTECTED]> wrote: Ok, thanks, Jeff. This is not a critical problem. Just annoying. I'll wait for 8.2. BTW, while I can see the reason for adding the IF EXISTS clause to the language for checking the existence of objects, wouldn't it be easier to simply provide the PL/PgSQL language for script loading? Then it would be possible to create scripts for database maintainance that could be run periodically without having to make them functions stored in the database. Just a thought. Or, more importantly, be able to commit within a procedure, i.e. make a LOOP which will COMMIT every 1000th iteration. Would be great for scripting maintenance activities. Regards, Dawid ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] IF EXISTS
Ok, thanks, Jeff. This is not a critical problem. Just annoying. I'll wait for 8.2. BTW, while I can see the reason for adding the IF EXISTS clause to the language for checking the existence of objects, wouldn't it be easier to simply provide the PL/PgSQL language for script loading? Then it would be possible to create scripts for database maintainance that could be run periodically without having to make them functions stored in the database. Just a thought. Cheers! Jeff Davis-8 wrote: > > On Wed, 2006-09-20 at 14:18 -0700, stevethames wrote: >> I have a script that sets up my databases which I run whenever I change >> any >> of the functions. It has a number of things it does that are unnecessary >> and cause errors. I create some types, sequences, etc. The error >> messages >> are irritating. I'd like to do something like this: >> >> IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname='seqid') >> CREATE SEQUENCE SeqID; >> >> This works in other SQL languages like SQLServer and MySQL. Is there >> anything like this in PostgreSQL? >> > > In 8.2, which is currently still in production, they have added the > feature where you can do things like: > > DROP SEQUENCE IF EXISTS mysequence; > CREATE SEQUENCE mysequence; > > Which makes writing SQL scripts much easier. They also made it work for > other objects, like DROP TABLE IF EXISTS, etc. > > This might not help you, because 8.2 is still months away from being > "production quality". However, a beta isn't too far off and you may be > interested to check it out. At least you know the code you want is > already written :) > > I think the DROP IF EXISTS syntax makes more sense than CREATE IF NOT > EXISTS, because normally the purpose of this type of thing is to reset > your tables or sequences to the starting state. > > Regards, > Jeff Davis > > > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > > -- View this message in context: http://www.nabble.com/IF-EXISTS-tf2308139.html#a6418758 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] IF EXISTS
On Wed, Sep 20, 2006 at 04:12:16PM -0700, Jeff Davis wrote: > In 8.2, which is currently still in production, they have added the > feature where you can do things like: > > DROP SEQUENCE IF EXISTS mysequence; > CREATE SEQUENCE mysequence; > > Which makes writing SQL scripts much easier. They also made it work for > other objects, like DROP TABLE IF EXISTS, etc. Hurra ! Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] IF EXISTS
On Wed, 2006-09-20 at 14:18 -0700, stevethames wrote: > I have a script that sets up my databases which I run whenever I change any > of the functions. It has a number of things it does that are unnecessary > and cause errors. I create some types, sequences, etc. The error messages > are irritating. I'd like to do something like this: > > IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname='seqid') > CREATE SEQUENCE SeqID; > > This works in other SQL languages like SQLServer and MySQL. Is there > anything like this in PostgreSQL? > In 8.2, which is currently still in production, they have added the feature where you can do things like: DROP SEQUENCE IF EXISTS mysequence; CREATE SEQUENCE mysequence; Which makes writing SQL scripts much easier. They also made it work for other objects, like DROP TABLE IF EXISTS, etc. This might not help you, because 8.2 is still months away from being "production quality". However, a beta isn't too far off and you may be interested to check it out. At least you know the code you want is already written :) I think the DROP IF EXISTS syntax makes more sense than CREATE IF NOT EXISTS, because normally the purpose of this type of thing is to reset your tables or sequences to the starting state. Regards, Jeff Davis ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] IF EXISTS
I have a script that sets up my databases which I run whenever I change any of the functions. It has a number of things it does that are unnecessary and cause errors. I create some types, sequences, etc. The error messages are irritating. I'd like to do something like this: IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname='seqid') CREATE SEQUENCE SeqID; This works in other SQL languages like SQLServer and MySQL. Is there anything like this in PostgreSQL? -- View this message in context: http://www.nabble.com/IF-EXISTS-tf2308139.html#a6416894 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] IF EXISTS
How about if exists (select .. from ..) On 11/14/05, Jaime Casanova <[EMAIL PROTECTED]> wrote: > On 11/14/05, P.M <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I would like to know if "IF EXISTS" exists under > > postgresql ? > > because i did not find it. > > > > before to create users or database, i would like to be > > sure that they do not exist already. > > > > so how can i test it and do something like : > > > > IF EXISTS database "test" DROP database "test"; > > > > thanks a lot, > > Maileen > > > > > > something in shell is fine? > > -SCRIPT BEGIN HERE > #!/bin/sh > > psql template1 -A -t -c "SELECT datname FROM pg_database WHERE > datname = 'test'" | while read D ; do > dropdb test > # or you can do > # psql template1 -c "DROP DATABASE test" > # whatever you feel comfortable with > done > > createdb test > -SCRIPT END HERE > > > -- > regards, > Jaime Casanova > (DBA: DataBase Aniquilator ;) > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] IF EXISTS
On 11/14/05, P.M <[EMAIL PROTECTED]> wrote: > Hi, > > I would like to know if "IF EXISTS" exists under > postgresql ? > because i did not find it. > > before to create users or database, i would like to be > sure that they do not exist already. > > so how can i test it and do something like : > > IF EXISTS database "test" DROP database "test"; > > thanks a lot, > Maileen > > something in shell is fine? -SCRIPT BEGIN HERE #!/bin/sh psql template1 -A -t -c "SELECT datname FROM pg_database WHERE datname = 'test'" | while read D ; do dropdb test # or you can do # psql template1 -c "DROP DATABASE test" # whatever you feel comfortable with done createdb test -SCRIPT END HERE -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] IF EXISTS
On Mon, Nov 14, 2005 at 13:20:59 -0800, "P.M" <[EMAIL PROTECTED]> wrote: > Hi, > > I would like to know if "IF EXISTS" exists under > postgresql ? > because i did not find it. > > before to create users or database, i would like to be > sure that they do not exist already. > > so how can i test it and do something like : > > IF EXISTS database "test" DROP database "test"; I don't believe there is a feature like that for users (roles) or databases. The normal thing to do would be to have the application try to delete the object and ignore the problem if things fail. If this is happening in a larger transaction that you don't want rolled back, then you can use savepoints. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] IF EXISTS
Hi, I would like to know if "IF EXISTS" exists under postgresql ? because i did not find it. before to create users or database, i would like to be sure that they do not exist already. so how can i test it and do something like : IF EXISTS database "test" DROP database "test"; thanks a lot, Maileen __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] IF EXISTS?
Hi all Can I make something linke: IF EXISTS(company_id_seq) DROP SEQUENCE company_id_seq; in a DDL where i creates all my tables? /Henrik ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
