Обсуждение: Re: Conditional SQL query

On Sun, 5 Nov 2000, Marten Feldtmann wrote:

> Indraneel Majumdar schrieb:
> > 
> > Hi,
> > 
> > how can I insert a record after checking whether one of the fields has a
> > certain value or not? eg:
> > 
> > if table(col1)='value' insert into table values (x,x,x)
> > 
> > I am using perl interface. Is there any way to define a function to take
> > table name as an argument? (eg: select * from $table) I tried but this
> > gave an error (so I'm using perl to pass the real tablename for each
> > query)
> > 
> 
> insert into table select ... where col1 = 'value'
> 

this is working but I am inserting values directly and not by select. eg:

insert into table values(x,x,x)
and not
insert into table select * from table2

the former is not working.

\Indraneel

> 
> MF
> 

/************************************************************************.
# Indraneel Majumdar                  ¡  E-mail: indraneel@123india.com  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics,                         #
# Hyderabad, India - 500076                                              #
`************************************************************************/

> > insert into table select ... where col1 = 'value'
> > 
> 
> this is working but I am inserting values directly and not by select. eg:
> 
> insert into table values(x,x,x)
> and not
> insert into table select * from table2
> 

insert into table select (x, x, x) where col1 = 'value'

I finally got it to work. you can't use the parentheses. eg:

insert into table select x,x,x where col1 = 'value';
and not
insert into table select (x,x,x) where col1 = 'value';

the latter gives a
ERROR:  parser: parse error at or near "where"
I am using PostgreSQL-7.0.2

Now I have one more problem. How do I insert only once if value does not
exist? eg:

insert into table once select x,x,x where not col1='value'

Thanks,
Indraneel

On Sun, 5 Nov 2000, Cristóvão B. B. Dalla Costa wrote:

> > > insert into table select ... where col1 = 'value'
> > > 
> > 
> > this is working but I am inserting values directly and not by select. eg:
> > 
> > insert into table values(x,x,x)
> > and not
> > insert into table select * from table2
> > 
> 
> insert into table select (x, x, x) where col1 = 'value'
> 

/************************************************************************.
# Indraneel Majumdar                  ¡  E-mail: indraneel@123india.com  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics,                         #
# Hyderabad, India - 500076                                              #
`************************************************************************/

>
> Now I have one more problem. How do I insert only once if value does not
> exist? eg:
>

You should create a unique index on the appropriate fields. For example:

CREATE UNIQUE INDEX indexname ON table (field1, field2)

Then the insert will fail if this particular combination of the values of
(field1, field2) is already in the table.

Be careful if the insert is inside a transaction, tough. If it fails due to
non-uniqueness your transaction will be aborted (all further queries ignored
untill a COMMIT or ROLLBACK).

OK, I solved my own problems (Congrats ;-) Sorry for bothering the list.
This is what I'm doing:

INSERT INTO table SELECT x,x,x where (select count(*) from table where
col1='value')=0;

any suggestions?

thanks,
Indraneel

On Mon, 6 Nov 2000, Indraneel Majumdar wrote:

> Now I have one more problem. How do I insert only once if value does not
> exist? eg:
> 
> insert into table once select x,x,x where not col1='value'
> 

/************************************************************************.
# Indraneel Majumdar                  ¡  E-mail: indraneel@123india.com  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics,                         #
# Hyderabad, India - 500076                                              #
`************************************************************************/