[SQL] Permission and users
Hi, PostgreSQL superuser (named A) create two different users: B: able to create db C: able to create db B creates db B_DB C creates db C_DB C is able to add tables at C_DB as well as at B_DB. B is able to add tables at B_DB as well ad at C_DB. How can I limit C to operate only to C_DB and B only to operate on B_DB? I can modify permission on C_DB to avoid read,modify for B user, but I'm not know how to exclude B to access to C_DB at all. Thank you in advance for any reply. \fer
[SQL] Unable to identify an ordering operator
Hello, select persons.name,firm.name,persons.tel from persons,work,firm where (persons.table_owner=0) and (work.id_firm=firm.id and work.id_person=persons.id) union select persons.name,firm.name,persons.tel from persons,work,firm where (persons.table_owner=1) and (work.id_firm=firm.id and work.id_person=persons.id); ERROR: Unable to identify an ordering operator '<' for type '_text' Use an explicit ordering operator or modify the query I know that this query can be rewrite to eliminate the UNION using an OR operator on first condition but my question is: How can I specify an ordering operator? On which field have I to put it? Best wishes for the new YEAR, \fer
[SQL] resetting serials and sequences
Hi,
#create temp table a (
id serial primary key,
name text not null);
#insert into a (name) values ('Tom');
#insert into a (name) values ('Fer');
#insert into a (name) values ('Mario');
#select * from a;
id | name
+---
1 | Tom
2 | Fer
3 | Mario
(3 rows)
OK. Now for some reason I need to reset everything without drop tables:
#delete from a;
#select setval ('a_id_seq', 1);
vacuum;
And now reinsert items:
#insert into a (name) values ('Tom');
#insert into a (name) values ('Fer');
#insert into a (name) values ('Mario');
#select * from a;
id | name
+---
2 | Tom
3 | Fer
4 | Mario
(3 rows)
We have missed the id "1"!!!
Otherway:
#select setval('a_id_seq', 0);
ERROR: a_id_seq.setval: value 0 is of of bounds (1,2147483647)
Is this a bug?
Best wishes for the brand new year \fer
[SQL] date infinity
Hi, I've not found, as reported into postgresql docs, 'infinity': create temp table subscriptions ( id int references people, expire date default 'infinity'); ERROR: Unrecognized date external representation 'infinity' Is there someone that knows the new costant name? >From PostgreSQL docs: http://localhost/pg7.0.3/postgres/datatype1134.htm Table 3-14. Postgres Special Date/Time Constants infinityLater than other valid times now and today costants are working. Bye,\fer
[SQL] Multicolumn primary keys and multicolumn foreign keys
Hi,
I've found a trouble and I've tried to avoid it without success:
---
create table companies (
id serial not null primary key,
firm_name text not null,
activity text
);
create table customers (
id int not null references companies,
seller_id int not null references companies,
note text,
primary key (id,seller_id)
);
create table invoices (
seller_id int4 not null references companies, /* who send invoice
*/
customer_id int4 not null, /* who receive the invoice and pay
for it */
invoice_no int4 not null unique,
invoice_date date,
primary key (seller_id,invoice_no,invoice_date),
foreign key (seller_id, customer_id) references customers
);
INSERT INTO "companies" ("firm_name","activity") VALUES
('NonSoLoSoft','ASP');
INSERT INTO "companies" ("firm_name","activity") VALUES
('MyFavouriteCustomer','Buy and pay');
INSERT INTO "customers" ("id","seller_id","note") VALUES (2,1,'customer
since 1966');
INSERT INTO "invoices" (seller_id,customer_id,invoice_no,invoice_date)
values (1,2,1,'now');
ERROR: referential integrity violation - key referenced from
invoices not found in customers
select * from customers;
id | seller_id |note
+---+-
2 | 1 | customer since 1816
(1 row)
---
Why have I found this ERROR about referential integrity violation, if
the record to reference is
in the customer table?
Thank you in advance,\fer
[SQL] PL/SQL trouble
Hi,
I really don't understand following PostgreSQL 7.2.3 behaviour:
$ psql mydb
mydb=> CREATE FUNCTION MONDAY(timestamp) RETURNS DATE AS '
DECLARE
var1 date;
BEGIN
select into var1 to_date($1::date-(case when extract(DOW from
timestamp $1) = 0 then 6 else (extract(DOW from timestamp $1)-1) end));
RETURN var1;
END'
language 'plpgsql';
CREATE
mydb=> select MONDAY('now'::timestamp);
NOTICE: Error occurred while executing PL/pgSQL function MONDAY
NOTICE: line 4 at select into variables
ERROR: parser: parse error at or near "$2"
mydb=> \q
But I've not inserted any $2 there.
I've rewritten the same function in other ways but I've got the same error.
I thank you in advance for any hints.
Bye, \fer
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] PostgreSQL array, recursion and more
MESH Data Tree:
example:
Hallux;A01.378.610.250.300.792.380
where:
A01 is Body Regions
A01.378 is Extremities
A01.378.610 is Lower Extremity
A01.378.610.250 is Foot
A01.378.610.250.300 is Forefoot, Human
A01.378.610.250.300.792 is Toes
CREATE OR REPLACE FUNCTION mesh_split(text) RETURNS text[]
AS $$
return [split('\.',$_[0])];
$$ LANGUAGE plperlu;
arancia=# select mesh_split('A01.378.610.250.300.792.380');
mesh_split
---
{A01,378,610,250,300,792,380}
(1 row)
/*
Is it a real array?
If it is, why can I not use index to access its items?
*/
arancia=# select mesh_split('A01.378.610.250.300.792.380')[1];
ERROR: syntax error at or near "["
LINE 1: select mesh_split('A01.378.610.250.300.792.380')[1];
^
/*
but it is an array, it behaves as it is.
*/
arancia=> select array_length(mesh_split('A01.378.610.250.300.792.380'),1);
array_length
--
7
(1 row)
/* How to get access to its items then?
*/
Another problem related:
arancia=> select * from meshtree where code = ANY
mesh_split('A01.378.610.250.300.792.380');
ERROR: syntax error at or near "mesh_split"
LINE 1: select * from meshtree where code = ANY mesh_split('A01.378
^
select * from meshtree,
unnest(mesh_split('A01.378.610.250.300.792.380')) as c where
c=meshtree.code;
parent | id | code |description
+---+--+---
10 |11 | 300 | Dehydroepiandrosterone Sulfate
33 |34 | 250 | Cymarine
48 |49 | 250 | Cymarine
61 |62 | 250 | Dihydrotachysterol
66 |68 | 300 | Calcitriol
65 |69 | 250 | Calcifediol
92 |93 | 380 | Glycodeoxycholic Acid
98 |99 | 250 | Finasteride
111 | 117 | 300 | Chenodeoxycholic Acid
145 | 146 | 300 | Dehydroepiandrosterone Sulfate
180 | 182 | 250 | Ethinyl Estradiol-Norgestrel Combination
190 | 191 | 250 | Desoximetasone
[..]
| 18638 | A01 | Body Regions
[..]
190 | 192 | 300 | Dexamethasone Isonicotinate
195 | 196 | 250 | Clobetasol
199 | 200 | 300 | Fluocinonide
206 | 207 | 250 | Diflucortolone
266 | 267 | 300 | Dexamethasone Isonicotinate
281 | 282 | 250 | Diflucortolone
290 | 293 | 250 | Dehydrocholesterols
305 | 306 | 250 | Dihydrotachysterol
312 | 314 | 300 | Calcitriol
311 | 315 | 250 | Calcifediol
320 | 321 | 250 | Cholestanol
328 | 330 | 300 | Calcitriol
[..]
52135 | 52136 | 250 | Eye Injuries
52136 | 52137 | 250 | Eye Burns
52149 | 52155 | 300 | Hematoma, Epidural, Cranial
52181 | 52196 | 300 | Gallbladder Emptying
52269 | 52277 | 300 | Caplan Syndrome
52360 | 52368 | 300 | Caplan Syndrome
52428 | 52442 | 380 | Hemothorax
52476 | 52491 | 610 | Pneumonia
52534 | 52535 | 380 | Legionnaires' Disease
(2204 rows)
I really want to write better similar query:
arancia=> with recursive t(id,parent,codeparts,idx,last,descriptions) as (
SELECT
id, parent, mesh_split('A01.378.610.250.300.792.380'), 1,
array_length(mesh_split('A01.378.610.250.300.792.380'),1),
ARRAY[description]
FROM meshtree WHERE code='A01'
UNION ALL
SELECT m.id, m.parent, t.codeparts, idx+1, last, descriptions ||
ARRAY[description]
FROM meshtree AS m JOIN t ON (t.id=m.parent)
WHERE idx<=last AND m.code=t.codeparts[idx+1])
SELECT t.* FROM t;
id | parent | codeparts | idx | last |
descriptions
---++---+-+--+...
18638 || {A01,378,610,250,300,792,380} | 1 |7 | {"Body
Regions"}
18675 | 18638 | {A01,378,610,250,300,792,380} | 2 |7 | {"Body
Regions",Extremities}
18676 | 18675 | {A01,378,610,250,300,792,380} | 3 |7 | {"Body
Regions",Extremities,"Lower Extremity"}
18679 | 18676 | {A01,378,610,250,300,792,380} | 4 |7 | {"Body
Regions",Extremities,"Lower Extremity",Foot}
18682 | 18679 | {A01,378,610,250,300,792,380} | 5 |7 | {"Body
Regions",Extremities,"Lower Extremity",Foot,"Forefoot, Human"}
18683 | 18682 | {A01,378,610,250,300,792,380} | 6 |7 | {"Body
Regions",Extremities,"Lower Extremity",Foot,"Forefoot, Human",Toes}
18684 | 18683 | {A01,378,610,250,300,792,380} | 7 |7 | {"Body
Regions",Extremities,"Lower Extremity",Foot,"Forefoot, Human",Toes,Hallux}
(7 rows)
explain analyze with recursive
t(id,parent,codeparts,idx,last,descriptions) as (
select
id,parent,mesh_split('A01.378.610.250.300.792.380'),1,array_length(mesh_split('A01.378.610.250.300.792.380'),1),ARRAY[description]
from meshtree where code='A01'
union all
select m.id,m.parent,t.codeparts,idx+1,last,descriptions ||
ARRAY[description] from meshtree as m join t on (t.id=m.parent) where
id
[SQL] To having or not to having?
Hello,
I'm rusty with SQL and I've started to practice it again but I'm falling
on this issue.
The problem:
Extracting rows from 'b' table trapping min() of a calculated value
"days" on 'a' table and a parameter.
SELECT b.*,
$1::date-a.sincedate AS "days"
FROM b, a
WHERE pintime BETWEEN $2 AND $2::interval+'00:01:00'::interval
AND b.a_id=a.id AND a.genre='F' AND description ~*'35$'
ORDER BY $1::date-a.sincedate ASC;
attached there is the full example, data, creates and inserts for it.
$1 can by any date (now() for example is good enough)
$2 is a time interval (10:00 in the example).
I thank you in advance for any answer.
Bye, \ferz
The problem:
SELECT pintime,a_id,c_id,value,id,sincedate,todate,description, genre,
now()::date-a.sincedate AS "days"
FROM b, a
WHERE pintime BETWEEN '10:00:00' AND '10:00:00'::interval+'00:01:00'::interval
AND b.a_id=a.id AND a.genre='F' AND description ~*'35$'
ORDER BY now()::date-a.sincedate ASC;
pintime | a_id | c_id | value | id | sincedate | todate | description |
genre | days
--+--+--+---++++-+---+--
10:00:00 |2 |1 | 1100 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |2 |2 | 1200 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |2 |3 | 1300 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |2 |4 | 1400 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |2 |5 | 1500 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |4 |1 | 4100 | 4 | 2011-03-11 | 2011-03-23 | a 35|
F | 226
10:00:00 |4 |2 | 4200 | 4 | 2011-03-11 | 2011-03-23 | a 35|
F | 226
10:00:00 |4 |3 | 4300 | 4 | 2011-03-11 | 2011-03-23 | a 35|
F | 226
10:00:00 |4 |4 | 4400 | 4 | 2011-03-11 | 2011-03-23 | a 35|
F | 226
10:00:00 |4 |5 | 4500 | 4 | 2011-03-11 | 2011-03-23 | a 35|
F | 226
10:00:00 |5 |1 | 5100 | 5 | 2010-09-02 | 2010-10-10 | a 35|
F | 416
10:00:00 |5 |4 | 5400 | 5 | 2010-09-02 | 2010-10-10 | a 35|
F | 416
10:00:00 |5 |6 | 10600 | 5 | 2010-09-02 | 2010-10-10 | a 35|
F | 416
(13 rows)
I need to extract rows having the lower number of days for each a_id,c_id pair.
The wanted result of the new query has to be:
pintime | a_id | c_id | value | id | sincedate | todate | description |
genre | days
--+--+--+---++++-+---+--
10:00:00 |2 |1 | 1100 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |2 |2 | 1200 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |2 |3 | 1300 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |2 |4 | 1400 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |2 |5 | 1500 | 2 | 2011-05-02 | 2011-05-27 | a 35|
F | 174
10:00:00 |6 |6 | 10600 | 6 | 2010-09-02 | 2010-10-10 | a 35|
F | 416
(13 rows)
Since c_id=6 has not any result for days<416;
So which are ways to write such SQL query?
Data:
create table a (id integer primary key, sincedate date not null, todate date
not null, description text, genre char(1) default 'M');
create table c (id integer primary key, name text not null, someotherdata
text);
create table b (pintime interval not null, a_id integer not null references a,
c_id integer not null references c, value integer, primary key(a_id,c_id));
insert into a (id,sincedate, todate, description, genre)
values (1,'20110502','20110527','a 15','F'),
(2,'20110502','20110527','a 35','F'),
(3,'20110502','20110527','b 35','M'),
(4,'20110311','20110323','a 35','F'),
(5,'20100902','20101010','a 35','F');
insert into c (id,name) values
(1,'c1'),(2,'c2'),(3,'c3'),(4,'c4'),(5,'c5'),(6,'c6');
insert into b (pintime,a_id,c_id,value) values
('10:00',1,1,100),('10:00',1,2,200),('10:00',1,3,300),('10:00',1,4,400),('10:00',1,5,500);
insert into b (pintime,a_id,c_id,value) values
('10:00',2,1,1100),('10:00',2,2,1200),('10:00',2,3,1300),('10:00',2,4,1400),('10:00',2,5,1500);
insert into b (pintime,a_id,c_id,value) values
('10:00',3,1,3100),('10:00',3,2,3200),('10:00',3,3,3300),('10:00',3,4,3400),('10:00',3,5,3500);
insert into b (pintime,a_id,c_id,value) values
('10:00',4,1,4100),('10:00',4,2,4200),('10:00',4,3,4300),('10:00',4,4,4400),('10:00',4,5,4500);
insert into b (pintime,a_id,c_id,value) values
('10:00',5,1,5100),('10:00',5,4,5400),('10:00',5,6,10600);
select * from a;
id | sincedate | todate | description | genre
+++-+---
1 | 2011-05-02 | 2011-05-27
Re: [SQL] To having or not to having?
On 10/23/11 23:12, I wrote: Hello, I'm rusty with SQL and I've started to practice it again but I'm falling on this issue. I've found first solution using WINDOWING: SELECT d.pintime, d.a_id, d.c_id, d.value, d.id, d.sincedate, d.todate, d.description, d.genre FROM (SELECT pintime,a_id,c_id,value,id,sincedate,todate,description, genre, $1::date-a.sincedate as days, min($1::date-a.sincedate) over w AS "days 2" FROM b, a WHERE pintime BETWEEN $2 AND $2::interval+'00:01:00'::interval AND b.a_id=a.id AND a.genre='F' AND description ~*'35$' WINDOW W AS (partition by c_id) ORDER BY $1::date-a.sincedate ASC) AS d WHERE d.days=d."days 2" ORDER BY d.c_id; I'm sure that there are many other solutions and probably mine could be not the best. So I'm looking for more hint and suggestions. Thank you again. \ferz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] sub query and AS
Hi,
I like PostgreSQL for many reasons, one of them is the possibility to
use sub query everywhere. Now I've found where it doesn't support them.
I would like to use a AS (sub query) form.
This is an example:
First the subquery:
select substr(descr, 7, length(descr)-8)
from (select string_agg('" int,"',freephone) as descr
from (select distinct freephone
from calendario order by 1
) as a
) as b;
substr
-
"800900420" int,"800900450" int,"800900480" int,"800900570"
int,"800900590" int,"800900622" int,"800900630" int,"800900644"
int,"800900688" int,"800900950" int
(1 row)
Then the wishing one:
itv2=#
select *
FROM crosstab('select uscita,freephone,id from calendario order by
1','select distinct freephone from calendario order by 1')
-- following AS fails
AS (select 'uscita int, ' || substr(descr, 7, length(descr)-8)
from (select string_agg('" int,"',freephone) as descr
from (select distinct freephone
from calendario order by 1) as a
) as b;
);
ERROR: syntax error at or near "select"
LINE 4: ...stinct freephone from calendario order by 1') as (select 'us...
More is on http://paste.scsys.co.uk/198877
I think that AS must evaluate the sub query in advance.
It could be possible to have such behavior?
Best regards, \ferz
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
