[SQL] Problem with extract(epoch from interval ...

[Kaloyan Iliev]

Hi,
I have a little problem with extract epoch from interval. It seems that 
the query works if the interval is in a string but not from DB field.

Could someone provide support.
Thanks in advance.
 Best regards,
 Kaloyan Iliev

db=# begin;
BEGIN
db=# SELECT version();
version
-
 PostgreSQL 9.1.6 on amd64-portbld-freebsd9.1, compiled by cc (GCC) 
4.2.1 20070831 patched [FreeBSD], 64-bit

(1 row)

db=# SELECT EXTRACT(EPOCH FROM INTERVAL '3 months');
 date_part
---
   7776000
(1 row)

db=# CREATE TABLE a( b interval);
CREATE TABLE
db=# INSERT INTO  a  VALUES ('1 month');
INSERT 0 1
db=# INSERT INTO  a  VALUES ('3 month');
INSERT 0 1
db=#  INSERT INTO  a  VALUES ('6 month');
INSERT 0 1
dbr=#  SELECT extract(EPOCH FROM INTERVAL b) FROM a;
ERROR:  syntax error at or near b
LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a;



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Problem with extract(epoch from interval ...

[Kaloyan Iliev]

Thanks,
It works perfectly.

Regards,
 Kaloyan Iliev
On 23.01.13 13:00, Ian Lawrence Barwick wrote:

Hi

2013/1/23 Kaloyan Iliev kalo...@digsys.bg:

Hi,
I have a little problem with extract epoch from interval. It seems that the
query works if the interval is in a string but not from DB field.
Could someone provide support.

(...)

You have a casting error; instead of:


dbr=#  SELECT extract(EPOCH FROM INTERVAL b) FROM a;
ERROR:  syntax error at or near b
LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a;

use

SELECT extract(EPOCH FROM b::INTERVAL) FROM a;

or


SELECT extract(EPOCH FROM CAST (b AS INTERVAL)) FROM a;


Regards

Ian Barwick




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] unique rows

[Kaloyan Iliev]

HI,

1. You can try to create a trigger before insert to check if the new row 
exists in the table and if it does to cancel the insert.
2. Or create a UNIQUE INDEX on all the columns which will throw error if 
you try to insert duplicate rows in the table.
Then in the apllication software just catch the error and move on with 
the next insert.


I hope I helped.

Regards,

 Kaloyan Iliev


TJ O'Donnell wrote:


I want to make a table that has unique rows - no problem.
Create Table x (smarts Varchar Unique);

I have input data that has many duplicates and until now
I was creating a temp table allowing duplicates, and then
Insert into x (smarts) select distinct smarts from tmpx;
This is now failing when I have hundreds of millions of
rows, running out of temp space (while trying to sort to
accomplish the distinct?)

So, is there a way (a constraint, a check?) that will simply
REJECT a duplicate when I insert the raw data directly into x
rather than FAILING on an error as it does
with the unique constraint above?

Thanks,
TJ O'Donnell

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster





---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] Problem with FOR UPDATE

[Kaloyan Iliev]

Hi All,
I have a query in which I want to SELECT FOR UPDATE same rows but only 
from one table.

Firs I try just with SELECT FOR UPDATE but I receive an error
because of the LEFT JOIN - ERROR:  SELECT FOR UPDATE/SHARE cannot be 
applied to the nullable side of an outer join.
So I decide to use SELECT FOR UPDATE OF table name but I then receive 
the error you can see.

Can anyone help me with this query?

Thanks in advance.

 Regards,
   Kaloyan Iliev

rsr=# SELECT
rsr-#DD.*
rsr-# ( SELECT sum(-amount * 
saldo_sign(credit))

rsr(#   FROM acc_debts ACD1
rsr(#   WHERE 
ACD1.debtid = DD.debtid ) AS saldo,

rsr-# C.custid,
rsr-# S.descr_bg
rsr-#FROM debts_desc DD LEFT JOIN config 
C ON (DD.conf_id = C.id),

rsr-# acc_debts AD,
rsr-# acc_clients AC,
rsr-# services S
rsr-#WHERE DD.debtid = AD.debtid
rsr-#   AND DD.closed AND NOT 
DD.paid AND DD.has_invoice AND DD.active AND DD.has_proform AND NOT 
DD.storned
rsr-#  AND AD.transact_no = 
AC.transact_no
rsr-#  
AND AC.ino = 45
rsr-#FOR UPDATE OF 
debts_desc;
ERROR:  relation debts_desc in FOR UPDATE/SHARE clause not found in 
FROM clause

rsr=# select version();
   version

PostgreSQL 8.1.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 
3.4.4 [FreeBSD] 20050518

(1 row)


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Substitute a Character

[Kaloyan Iliev]

Hi,

Try:
UPDATE foe SET field = regexp_replace(field, '^.', '0');
OR
UPDATE foe SET field = regexp_replace(field, 'A', '0');

This will replace in table foe in the column field 'A' with '0';

Regards,
 Kaloyan Iliev

Judith wrote:

   Hello everybody!!   I have a field type text with folios like 
this: A98526


   but I want to change de A for a 0 like this: 098526, exists a way 
to do this in a query???


   Thanks in advanced!!!



---(end of broadcast)---
TIP 6: explain analyze is your friend





---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] Thanks

[Kaloyan Iliev]

Thanks a lot!
It works!

Regards,

  Kaloyan Iliev

Tom Lane wrote:

  Kaloyan Iliev [EMAIL PROTECTED] writes:
  
  
rsr=# SELECT
...
rsr-#FROM debts_desc DD LEFT JOIN config 
C ON (DD.conf_id = C.id),
...
rsr-#FOR UPDATE OF 
debts_desc;
ERROR:  relation "debts_desc" in FOR UPDATE/SHARE clause not found in 
FROM clause

  
  
Use the alias, ie, DD.  Remember that an alias hides the real name of
that table for all purposes in the current query.

			regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


  

Re: [SQL] Triggers

[Kaloyan Iliev]

Hi,
You can use one procedure(function in Postgres) but you have to use 26 
triggers.
Every trigger can call this one procedure to record the information you 
want.


Regards,
  Kaloyan Iliev

Ezequias Rodrigues da Rocha wrote:


Hi list,
 
I am planning to generate a trigger to each [update or delete or 
insert] on my database to register all moviment on my base 
(current_user, when, action, user).
 
I notice that the trigger is able put a procedure in action but as I 
have more than 20 tables I would like to use only one trigger to solve 
my problem. Is that possible ?
 
Now my trigger is working well but I do not want to make 26 triggers 
and 26 functions.
 
Is there any people that can help me with this question ?



=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
  Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda й melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[SQL] Problem with SET CONSTRAINTS ALL DEFERRED;

[Kaloyan Iliev]

Hi Friends,

I am trying to postpone the foreign key constraint check till the end of 
transaction but it doesn't work.

Can anyone help me with a tip what I am doing wrong.

Thanks in advance.

Kaloyan Iliev

test=#  BEGIN;
BEGIN
test=#
test=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
test=# DELETE FROM picture
test-#  USING 
element_picture
test-#  WHERE 
picture.id = element_picture.picture_id
test-#
AND element_picture.element_id = 8;
ERROR:  update or delete on picture violates foreign key constraint 
picture_id on element_picture

DETAIL:  Key (id)=(223) is still referenced from table element_picture.


select version();
   version

PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 
3.4.4 [FreeBSD] 20050518

(1 row)


---(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

[SQL] Problems with HAVING

[Kaloyan Iliev Iliev]

Hello,

My problem is that I want to select the row with max(date) but also
limited with where clauses.

Select 
test.name
from 
test
where
test.name = foo.name
having max(test.date)

This is a subquery and is part bigger query. How I can select the row
with the max query.
ERROR:  argument of HAVING must be type boolean, not type date


This is the error I receive. 
As far as I know I can't use agregate functions in where clause.

Thank you in advance.

Kaloyan Iliev

Re: [SQL] Problems with HAVING

[Kaloyan Iliev Iliev]

Thanks for the replay first.
Yes I use having like I have written. I can't use your query because 
in subquery I must write again the whole WHERE clause.
But in the other mail in the tread there is the solution:)

Thanks again
Kaloyan
Sam Mason wrote:
Kaloyan Iliev Iliev wrote:
 

select test.name
   

from test
 

where test.name = foo.name
having max(test.date)
   

I don't think you use the having clause like you've done there.  I
think you want to be doing something more like:
 select test.name
 from test
 where test.name = foo.name
   and test.date in (select max(date) from test);
But I may have misinterpreted you. . .
Cheers,
 Sam
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Inheriting text[] field

[Kaloyan Iliev Iliev]

10x
I suppose you are right:)
Regard Kaloyan Iliev
Tom Lane wrote:
Kaloyan Iliev Iliev [EMAIL PROTECTED] writes:
 

I am useing PostgreSQL 7.2.3.
   

 

test_libvar=# create table temp_a(
test_libvar(# name text[]
test_libvar(# );
CREATE
test_libvar=# create table temp( name text[] ) inherits (temp_a);
NOTICE:  CREATE TABLE: merging attribute name with inherited definition
ERROR:  CREATE TABLE: attribute name type conflict (_text and text)
   

Works fine for me in 7.3 and later.  Time to upgrade ...
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match
 

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Inheriting text[] field

[Kaloyan Iliev Iliev]

Dear Friend,
I have the following problem when I try to inherits one table with 
text[] field into another.
I am useing PostgreSQL 7.2.3.
I suppose that this  is a BUG but I am not sure.
Any ides.
10x in advance.

 Kaloyan
test_libvar=# create table temp_a(
test_libvar(# name text[]
test_libvar(# );
CREATE
test_libvar=# create table temp( name text[] ) inherits (temp_a);
NOTICE:  CREATE TABLE: merging attribute name with inherited definition
ERROR:  CREATE TABLE: attribute name type conflict (_text and text)
P.S. I know that I can avoid this by scipping the 'name' field into the 
second table but what if I need to set some CONSTRAINTS to it.

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[SQL] Problems with UNION ALL and ORDER BY

[Kaloyan Iliev Iliev]

Dear friends...,
I have the following problem:
select
.
from

where

UNION ALL
select
...
from

where

ORDER BY field1
But the the order by doesn't work properly. It returns the rows of the 
first query ordered and then appends the rows of the second query 
ordered. But this is not what i expect. I expect the result of both 
queries to be orderd. So I try.

SELECT TEMP.*
FROM
(
select
.
from

where

UNION ALL
select
...
from

where

) TEMP
ORDER BY TEMP.field1
But this also doesn't work. Any ideas.p
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Problems with UNION ALL and ORDER BY

[Kaloyan Iliev Iliev]

Hello again,
I am using Postgres 7.2.3. If any other details are necessary I will 
provide them:)))
10x again

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Problems with UNION ALL and ORDER BY

[Kaloyan Iliev Iliev]

Dear Tom,
As I say in my previous letter I am using 7.2.3. If you wish I can show 
you the query and the result to see for yourself, that there is 
something wrong. It just don't order the overall result but the separate 
results of the both subqueries.

Tom Lane wrote:
Kaloyan Iliev Iliev [EMAIL PROTECTED] writes:
 

I have the following problem:
   

 

select
.
from

where

UNION ALL
   

 

select
...
from

where

ORDER BY field1
   

 

But the the order by doesn't work properly. It returns the rows of the 
first query ordered and then appends the rows of the second query 
ordered.
   

Pray tell, what Postgres release are you using?
AFAICT this will result in an overall sort in all PG releases since 7.0.
I don't have anything older to test...
regards, tom lane
 

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Problems with UNION ALL and ORDER BY

[Kaloyan Iliev Iliev]

10x for the replies.
We find the problem. It is in our scripts. 
We use to_char over a date field and then order by this field. So it sort it as text and not as date. 
I appologize for loosing your time.
10x again.

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [SQL] Start-up problems

[Kaloyan Iliev Iliev]

Look at /etc/

beyaRecords - The home Urban music wrote:

Kaloyan,
thanks for reply. Still finding my way around the unix environment so 
could you tell me where the file you mention is situated?
On 8 Jan 2004, at 13:24, Kaloyan Iliev Iliev wrote:

This is what I have in my rc.local on FreeBSD:

su pgsql -c /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -o -i -l 
/usr/local/pgsql/logfile start


beyaRecords - The home Urban music wrote:

I am running postgresql 7.4.1 on OS X 10.3 and am having to manually 
start-up postgresql using /usr/local/pgsql/bin/postmaster -i -D 
/usr/local/pgsql/data.

Is there a script available which will enable me to automate this 
process, so that postgres loads up at startup?

Uzo

---(end of 
broadcast)---
TIP 1: subscribe and unsubscribe commands go to 
[EMAIL PROTECTED]







---(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

[SQL] Unsigned numbers

[Kaloyan Iliev Iliev]

Hello Friends,

I have the following questions.
I have a lot of fields in my database that are int or float.
The problem is that I need them to be only positive (unsigned) and the 
users try to insert sometimes negative.
I know that I can make this with CHECK CONSTRAINT but for some reasons 
this is not a good solution for me. The reason is that when I have a 
check constraint on a certain field and someone try to insert negative 
number the error message contains the name of the constraint and the 
name of the table but not the name of the field. So on higher level I 
can't send a proper message to the user.

Are there any unsigned data types like unsigned int or unsigned float.
Can I make a domain or something like that (How exactly to add 
constraint to domain).
I am using v7.3.2.
Thanks!

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Unsigned numbers

[Kaloyan Iliev Iliev]

10x you all



---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Unsigned numbers

[Kaloyan Iliev Iliev]

Hello Friends,

I have the following questions.
I have a lot of fields in my database that are int or float.
The problem is that I need them to be only positive (unsigned) and the 
users try to insert sometimes negative.
I know that I can make this with CHECK CONSTRAINT but for some reasons 
this is not a good solution for me. The reason is that when I have a 
check constraint on a certain field and someone try to insert negative 
number the error message contains the name of the constraint and the 
name of the table but not the name of the field. So on higher level I 
can't send a proper message to the user.

Are there any unsigned data types like unsigned int or unsigned float.
Can I make a domain or something like that (How exactly to add 
constraint to domain).
I am using v7.3.2.
Thanks!

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org