> This is an approach I also considered, but hoped for a solution without the
> expense (albeit small) of having to create a function.
How about this query?
CREATE TABLE transactions (
item_code text,
_date date,
qty double precision
)
;
INSERT INTO transaction
David Johnston wrote
> Basic idea: use ORDER BY in the window to auto-define a range-preceding
> frame. Create an array of all dates (tags in the example) that match with
> positive amounts. Negative amounts get their matching tag added to the
> array as NULL. The provided function looks into th
gmb wrote
> item_code | _date| qty | max
> -
> ABC | 2013-04-05 | 10.00| 2013-04-05
> ABC | 2013-04-06 | 10.00| 2013-04-06
> ABC | 2013-04-06 | -2.00| 2013-04-06
hello,
( select 'A' as x, id, price from tab where id = 'value'
union all select 'B' as x, id, ...
union all select 'C' as x, id, ...
union all select 'D' as x, id, price from tab where id = 'value' )
generate a list like
X id price
A 1 10
B 2 20
C 3 30
D 4 40
select
JORGE MALDONADO hat am 24. Dezember 2012 um 17:30
geschrieben:
> I have a record with the following fields:
>
> id1, id2, id3, id4, price1, price2, price3, price4
>
> I would like to write a SELECT statement in order to get 4 records:
>
> id, price (record that comes from id1 and price 1)
> id,
> -Original Message-
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-
> ow...@postgresql.org] On Behalf Of mephysto
> Sent: Thursday, September 27, 2012 6:12 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Help in accessing array
>
> Hi to everyone,
> I have a little problem to
On Mar 13, 2012, at 14:29, "M. D." wrote:
> Hi,
>
> I want to do a check on a column if other columns meet certain conditions.
> The program I'm working with allows to create additional columns on every
> 'object' - called extra data, but I have no control over the program. I want
> to enfor
On Mon, Sep 26, 2011 at 09:56:06AM -0400, Brian Sherwood wrote:
> Yep, that did it.
>
> Thanks!
ProTip (for the list archive): since the namespace alias in the query and the
original XML don't need to match (in this common case, the document uses a
default namespace, which isn't available in xpat
Yep, that did it.
Thanks!
2011/9/23 Filip Rembiałkowski :
>
>
> 2011/9/22 Brian Sherwood
>>
>> select (xpath('/chassis-inventory/chassis/serial-number/text()',
>> data_xml,
>> ARRAY[ARRAY['junos',
>> 'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
>> )) from xml_test;
>
2011/9/22 Brian Sherwood
>
> select (xpath('/chassis-inventory/chassis/serial-number/text()',
>data_xml,
>ARRAY[ARRAY['junos',
> 'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
> )) from xml_test;
>
> Can anyone suggest how I would go about getting the serial-number wit
Johann Spies wrote:
> I am struggling a bit to do the following type of update in a table.
> I want the content of a field updated like this:
> Original:
> '0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'
> After update:
> '|0894396e-16bf-4e63-aa52-97fe7031e
select id, regexp_replace(category, (E'\\|{2,}'), E'\|', 'g') as category from
akb_articles limit 100
Backslash in regex doubled. Added global modifier to replace all occurrences.
- Reply message -
From: "Johann Spies"
Date: Thu, Jul 28, 2011 8:20 am
Subject: [SQL] Help with regexp-quer
On 2011-04-20, Saulo Venâncio wrote:
> --bcaec52e65e9b2f22304a15f3840
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> Hi guys,
> I need your help.
> I have a table called medidas, in this table i have some ocurrences that ha=
> s
> id_medida(primary
You might try:
WITH
pontos AS
(
SELECT column1 AS idponto
FROM (VALUES (10), (11), (23), (24) ) AS a
),
subset AS
(
SELECT b.idponto, date_trunc('day', datetime) AS datetime
FROM medidas b
INNER JOIN pontos USING(idponto)
GROUP BY b.idponto, date_trunc('day', da
COPY D_2147483927_2147484848_TAB
(
CP
, CPR
, CHOUSENO
, CSTREET
, CLOCALITY
, CCITY
, CPROVINCE
, CCOUNTRY
, CZIP
)
FROM 'one.txt'
WITH DELIMITER ',' QUOTE '"' csv HEADER;
On Mon, Jan 17, 2011 at 7:59 AM, Amar Dhol
On 01/17/2011 05:59 AM, Amar Dhole wrote:
I have table created as follows
CREATE TABLE D_2147483927_2147484848_TAB(
CP VARCHAR(256) ,
CPR VARCHAR(256) ,
CHOUSENO VARCHAR(256) ,
CSTREET VARCHAR(256) ,
CLOCALITY VARCHAR(256) ,
CCITY VARCHAR(256) ,
CPROVINCE VARCHAR(256) ,
CCOUNTRY VARCHAR(
Thanks this solves my problem..
From: filip.rembialkow...@gmail.com [mailto:filip.rembialkow...@gmail.com] On
Behalf Of Filip Rembialkowski
Sent: Wednesday, January 12, 2011 1:41 AM
To: Amar Dhole
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] help needs in
2011/1/11 Amar Dhole
> Hi,
> I need helping converting following db2 function in postgresql function.
> Any pointer will be great help in proceeding me ahead.
>
> CREATE FUNCTION in_liststring ( string CLOB(64K) )
> RETURNS TABLE ( ordinal INTEGER, index INTEGER )
> LANGUAGE SQL
> DETERMINI
Hello Oliver.
thanks for your reply. Here are my answers. Sorry I shot e-mail in
morning and I was not clear about it.
I am afraid, I am not jamming you with all information.
Thank you very much for your help.
== your questions
==
Howdy, Adrian,
Please see my questions below
snps table
id | sample_id | chromosome | from | to |
1 1chr1 10 11
2 1 chr1 14 15
3 2
In response to Andreas :
> Hi,
> I need to display log events (again).
> The log is simply like this
> log ( log_id serial primary key, create_ts timestamp default
> localtimestamp, object_id, state_id, ... )
>
> It records the state of objects and when and what happend to to change
> this
Or even simpler, or easier to understand:
SELECT trim(foo, '()') FROM foobar
pozdrowienia / regards / salutations
mk
2010/5/5 Nicholas I
> Hi,
>
> I have a table in which the data's are entered like,
>
> Example:
>
> One (1)
> Two (2)
> Three (3)
>
> I want to extract the data which is only
--- On Thu, 27/5/10, James Kitambara wrote:
From: James Kitambara
Subject: Re: [SQL] help
To: "Nicholas I"
Date: Thursday, 27 May, 2010, 14:50
Hello Mr. Nicholas,
You can try the following:
THIS IS WHAT I TRIED TO SOLVE YOUR PROBLEM, BUT IN ORACLE DBMS
(SORRY I
select substring( data from '\((.*)\)' ) from table;
2010/5/5 Nicholas I :
> Hi,
>
> I have a table in which the data's are entered like,
>
> Example:
>
> One (1)
> Two (2)
> Three (3)
>
> I want to extract the data which is only within the parentheses.
>
> that is
> 1
> 2
> 3
>
>
> Thank You
> N
In response to Nilesh Govindarajan :
> Hi,
>
> I want to find out the userid, nodecount and comment count of the userid.
>
> I'm going wrong somewhere.
>
> Check my SQL Code-
Check my example:
test=*# select * from u;
id
1
2
3
(3 rows)
test=*# select * from n;
uid
-
1
1
Dear Ben
thanks for you anwser.
I try to add function quote_literal on my sql statement .
but it raise other error message (quote_literal not support bytea format):
function quote_literal(bytea) does not exist
Ben Morrow 提到:
Quoth dennis :
Hi Ben
here is my function , it's for fi
Postgres : 8.1.4
Dennis
Ben Morrow wrote:
Quoth dennis:
Dear Ben
thanks for you anwser.
I try to add function quote_literal on my sql statement .
but it raise other error message (quote_literal not support bytea format):
function quote_literal(bytea) does not exist
Which P
Hi Ben
here is my function , it's for fix missing chunk problem.
It has same problem ,please take look
thank for you help
-table--
db=# \d usersessiontable;
Table "public.usersessiontable"
Column | Type | Modifiers
---+---
here is example
table name is "mail":
column| type
-
sender|char
subject |char
content |bytea
I want copy some record into new table 'mail_new'.
sql:
create table mail_new as select * from mail sender='dennis'
result has an error:
operator does not exist:
[quoting fixed]
Quoth dennis :
> Ben Morrow wrote:
> > Quoth dennis:
> >> Dear Ben
> >>
> >> thanks for you anwser.
> >> I try to add function quote_literal on my sql statement .
> >>
> >> but it raise other error message (quote_literal not support bytea format):
> >>function quote_li
Quoth dennis :
> Dear Ben
>
> thanks for you anwser.
> I try to add function quote_literal on my sql statement .
>
> but it raise other error message (quote_literal not support bytea format):
> function quote_literal(bytea) does not exist
Which Postgres version are you using?
Ben
--
Quoth dennis :
> Hi Ben
>
> here is my function , it's for fix missing chunk problem.
> It has same problem ,please take look
>
>
> thank for you help
>
> -table--
>
>
> db=# \d usersessiontable;
> Table "public.usersessiontable"
>Column |
Quoth dennis :
> here is example
>
> table name is "mail":
> column| type
> -
> sender|char
> subject |char
I presume you mean 'varchar'?
> content |bytea
>
>
> I want copy some record into new table 'mail_new'.
>
> sql:
> create table mail_new as select
Quoth dennis :
>
> I need to copy some data to new table.
> But I encounter some error message.
> the table structure
> Table A:
> c1 char
> c2 bytea
>
> Table B:
> c1 char
> c2 bytea
>
>
> My sql command:
> insert into B as select * from a where c1=xxx
'AS' isn't valid there.
dateadd() is not sql postgresql standard, it's mssql
in postgresql i only need to use +/- operators and interval for the same
function
dateadd('year', 1, '2008/1/1') ==> date '2008/1/1' + interval '1 year'
rafizeldi wrote:
>
> Dear All
> I need to simplify this query, It takes a lot of time t
Isaac Dover writes:
> thank you _SO_ much. I'm not sure how I overlooked that, but it is far
> easier to manage using connect. I'm assuming that even if tables have public
> privileges granted, that they are not visible to people not granted connect
> privileges to the database?
Right, if you can
thank you _SO_ much. I'm not sure how I overlooked that, but it is far
easier to manage using connect. I'm assuming that even if tables have public
privileges granted, that they are not visible to people not granted connect
privileges to the database?
Thanks,
Isaac
On Fri, May 22, 2009 at 12:31 P
Isaac Dover writes:
>> As far as i can tell,
>> pg_hba.conf is the only manner in which to prevent users from connecting to
>> other users' databases. I've restricted roles to connecting only using
>> sameuser:
>>
>> host sameuser all 192.168.168.0/24 md5
In recent releases (certainly 8.3) the b
possibly this answers my question, and what i am doing is indeed the most
appropriate?
"Databases are physically separated and access control is managed at the
connection level."
from 8.3 docs, section 20.1
thanks,
Isaac
On Fri, May 22, 2009 at 2:37 AM, Isaac Dover wrote:
> Hello, to this poi
Hi Scott,
Thanks for the information. This is very useful for me.
I will be careful when forming the column.
Thanks,
-maria
On Tue, Jun 10, 2008 at 3:16 PM, Scott Marlowe <[EMAIL PROTECTED]>
wrote:
> On Tue, Jun 10, 2008 at 11:51 AM, maria s <[EMAIL PROTECTED]> wrote:
> > Hi Rosario,
> > Thanks
On Tue, Jun 10, 2008 at 11:51 AM, maria s <[EMAIL PROTECTED]> wrote:
> Hi Rosario,
> Thanks for the link. I hope this will solve my problem.
It should be able to. Note that crosstab functions expect "square"
inputs from the select they run. I.e. you can't have empty columns,
you need to replace
Hi Rosario,
Thanks for the link. I hope this will solve my problem.
Thanks,
Maria
On Tue, Jun 10, 2008 at 11:34 AM, Osvaldo Rosario Kussama <
[EMAIL PROTECTED]> wrote:
> maria s escreveu:
>
>>
>> I tried the query and it is returning result as ,
>> for a single entry in sample info in separate r
2008/6/10 maria s <[EMAIL PROTECTED]>:
> Hi Pavel,
> Thank you for your reply.
>
> I tried the query and it is returning result as ,
> for a single entry in sample info in separate rows
>
> The result of the query as
>
> 1, prop1,value1
> 1,prop2,value2
> 2,prop1,value1
> 2 prop2,value2
> 2 prop3,v
maria s escreveu:
I tried the query and it is returning result as ,
for a single entry in sample info in separate rows
The result of the query as
1, prop1,value1
1,prop2,value2
2,prop1,value1
2 prop2,value2
2 prop3,value3
but i want the output as single row per sample id like
1,value1,value2
Hi Pavel,
Thank you for your reply.
I tried the query and it is returning result as ,
for a single entry in sample info in separate rows
The result of the query as
1, prop1,value1
1,prop2,value2
2,prop1,value1
2 prop2,value2
2 prop3,value3
but i want the output as single row per sample id like
Hello
SELECT i.name, p.property_name, p.property_value
FROM sample_info i
JOIN
sample_properties p
ON i.id = p.id
maybe
Pavel
2008/6/10 maria s <[EMAIL PROTECTED]>:
> Hello friends,
> I need help in write a query.
>
> I have 2 tables, one is sample_info
Add something like this to your query:
ORDER BY price_time LIMIT 1
Cheers,
~p
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of roopa perumalraja
Sent: Monday, 19 March 2007 13:56
To: pgsql-sql@postgresql.org
Subject: [SQL] Help with sub query
On Thu, 18 Jan 2007, Barbara Cosentino wrote:
> Then I perform the following selects
>
> SELECT host_id, host_datum_type_id, host_datum_source_id, data
> FROM nc_host_datum INNER JOIN nc_host USING (host_id)
> WHERE audit_id=2041
> ORDER BY host_id
> LIMIT 49 OFFSET 1372;
>
> And
>
> SELECT host_
On 1/8/07, Jeremiah Elliott <[EMAIL PROTECTED]> wrote:
I have a java application that moves data from our erp platform
(progress) into a Postgres database. One of the fields in progress is
a character array. I haven't had any luck getting this field to
transfer. What I would like to do is someth
I'd probably make a small change to make this a little cleaner.
Specifically, change check_version() to take an argument, which is the
needed version, and check this against the current value in
agenda_version, throwing the exception if they don't match. Once you've
written this, you'll never
Hi all,
Thanks for all replies, taking into account all your suggestions and
my google research I arrived to the next script. I'd like to know your
opinion. Hopefully this will be useful for somebody else.
--used to stop the script execution on any error
In GNUmed we have created a function
gm_concat_table_structure()
in
http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/gmSchemaRevisionViews.sql?rev=1.6&root=gnumed&view=log
which returns a reproducable, human-readable TEXT
concatenation of all the relevant parts of t
> I tried to do the following in PostgreSQL:
>
> DECLARE
>v_version VARCHAR;
>
> BEGIN
>SELECT version INTO v_version FROM version WHERE id = 1;
>
>IF v_version <> ''1.0.0.0'' THEN
> RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0,
> detected version %'', v_version;
>
Can you do the whole thing inside a transaction context (both the
version check and the updates)? The exception should cause the
transaction to bail out, and the updates won't proceed.
Thus:
BEGIN;
SELECT check_version();
UPDATE agenda_version set version = '1.0.0.1' where id = 1;
COMMIT;
I t
Arnau wrote:
Tom Lane wrote:
Arnau <[EMAIL PROTECTED]> writes:
I don't want, if it's possible, to create a function.
Unlike Oracle, PG makes a strong distinction between SQL and
programmable languages (including plpgsql). You can't write
plpgsql code without putting it into a function.
Tom Lane wrote:
Arnau <[EMAIL PROTECTED]> writes:
I don't want, if it's possible, to create a function.
Unlike Oracle, PG makes a strong distinction between SQL and
programmable languages (including plpgsql). You can't write
plpgsql code without putting it into a function.
Arnau <[EMAIL PROTECTED]> writes:
>I don't want, if it's possible, to create a function.
Unlike Oracle, PG makes a strong distinction between SQL and
programmable languages (including plpgsql). You can't write
plpgsql code without putting it into a function.
regards,
Hi Daniel,
You should define a PL/PGSQL function such as:
CREATE OR REPLACE FUNCTION check_version()
RETURNS void
AS $$
DECLARE
v_version VARCHAR;
BEGIN
SELECT version INTO v_version FROM version WHERE id = 1;
IF v_version <> '1.0.0.0' THEN
RAISE EXCEPTION 'This script needs Agen
Richard Ray wrote:
It makes sense when ya'll explain it
It never sounds that good when I'm talkin to myself
That solves my problem but not my ignorance
I'm still curious about how would I properly quote
create or replace function test(integer) returns setof text as $$
declare
a record;
begin
It makes sense when ya'll explain it
It never sounds that good when I'm talkin to myself
That solves my problem but not my ignorance
I'm still curious about how would I properly quote
create or replace function test(integer) returns setof text as $$
declare
a record;
begin
select into a now()
"Hector Villarreal" <[EMAIL PROTECTED]> writes:
>select into a now() - ($1::text||'days')::interval;
People keep suggesting variants of that as ways to convert numeric
values to intervals, but it's really extremely bad practice. Much
better is to use number-times-interval multiplication:
::interval;
return next a;
return;
end
$$ language 'plpgsql';
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Milen A. Radev
Sent: Tuesday, December 19, 2006 2:54 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with quotes in plpgsql
Richard Ray написа:
> On Tue, 19 Dec 2006, Milen A. Radev wrote:
>
>> Richard Ray :
>>> How should this be properly quoted
>>>
>>> create or replace function test(integer) returns setof text as $$
>>> declare
>>> a record;
>>> begin
>>> select into a now() - interval '$1 day';
>>>
am Tue, dem 19.12.2006, um 14:09:37 -0600 mailte Richard Ray folgendes:
> How should this be properly quoted
>
> create or replace function test(integer) returns setof text as $$
> declare
> a record;
> begin
> select into a now() - interval '$1 day';
> return next a;
> return;
> end
> $$
On Tue, 19 Dec 2006, Milen A. Radev wrote:
Richard Ray :
How should this be properly quoted
create or replace function test(integer) returns setof text as $$
declare
a record;
begin
select into a now() - interval '$1 day';
return next a;
return;
end
$$ language 'plpgsql';
On Tue, 19 Dec 2006, Hector Villarreal wrote:
Hi Not sure about $1 parms : you may need to use a variable to set $1 to
then cast it as interval :
create or replace function test(integer) returns setof text as $$
declare
a record;
begin
select into a now() - ($1::text||'days')::inter
Hi Not sure about $1 parms : you may need to use a variable to set $1 to
then cast it as interval :
create or replace function test(integer) returns setof text as $$
declare
a record;
begin
select into a now() - ($1::text||'days')::interval;
return next a;
return;
end
$$
Richard Ray написа:
> How should this be properly quoted
>
> create or replace function test(integer) returns setof text as $$
> declare
> a record;
> begin
> select into a now() - interval '$1 day';
> return next a;
> return;
> end
> $$ language 'plpgsql';
>
> I'm not having a lot of luc
Try
select into a now() - interval ($1 || ' day')
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Richard Ray
Sent: Tuesday, December 19, 2006 3:10 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Help with quotes in plpgsql
How should this be properly qu
Curtis Scheer wrote:
> I noticed this one by searching in the archives, as I am working with
some
> "optional" parameters myself and noticed your solution. I just wanted to
> make one improvement suggestion which is instead of checking whether
or not
> a parameter has been used simply start yo
: Thursday, August 17, 2006
8:58 PM
To: MaXX
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with
optional parameters
BEGIN
query_base := 'SELECT * FROM my_table ';
has_param := FALSE;
query_where := '';
IF (a IS NOT NULL) THEN
IF (has_param IS FALSE)THEN
-- t
Hi, Tom,
Tom Lane wrote:
> If film_id is a primary key for film, then it's actually legal per SQL99
> (though not in earlier SQL specs) to just GROUP BY film_id and then
> reference the other columns of film without explicit grouping, because
> clearly there can be only one value of them per film
On Fri, 1 Sep 2006, Walter Cruz wrote:
> Hi all. I'm with a little doubt.
>
> I'm testing the pagila (the postgres port of mysql sakila sample).
>
> Well, I was trying to translate the query:
>
> select
> film.film_id AS FID,
> film.title AS title,
> film.description AS description
Thank you all.I found myself with the same trouble in last week, when I tried to port mambo CMS to PostgreSQL.After some work, In fall i a query like the one that was quoted by Andrew and decided to stop.
In that time, my guess wae that something was wrong with MySQL.(I don't know.. I have seen que
> So I can assume that the MySQL implementation is strange? (It accepts
> that kind of query)
Yes, MySQL behaves strangely in this case (as well as in several other
cases). I wouldn't rely on this as it probably can choose different
values each time (although as far as I remember I haven't seen th
Andrew Sullivan <[EMAIL PROTECTED]> writes:
> On Fri, Sep 01, 2006 at 02:26:39PM -0300, Walter Cruz wrote:
>> So I can assume that the MySQL implementation is strange? (It accepts that
>> kind of query)
> In my experience, it is almost never safe to assume that the MySQL
> approach to SQL bears an
On Fri, 2006-09-01 at 12:26, Walter Cruz wrote:
> So I can assume that the MySQL implementation is strange? (It accepts
> that kind of query)
Yes, according to the SQL spec, you should generally get an error when
you run a query like this:
select field1, field2 from table group by field1
since y
> But, when I add another column on select, like, film_description, I get
> the following error:
>
> "ERROR: column "film.description" must appear in the GROUP BY clause or
> be used in an aggregate function"
>
> If I put that column on GROUP BY everything works ok. But I want
> understant why d
On Fri, Sep 01, 2006 at 02:26:39PM -0300, Walter Cruz wrote:
> So I can assume that the MySQL implementation is strange? (It accepts that
> kind of query)
In my experience, it is almost never safe to assume that the MySQL
approach to SQL bears anything but a passing resemblance to SQL
proper. Thi
So I can assume that the MySQL implementation is strange? (It accepts that kind of query)[]'s- WalterOn 9/1/06, Andrew Sullivan <
[EMAIL PROTECTED]> wrote:On Fri, Sep 01, 2006 at 10:31:48AM -0300, Walter Cruz wrote:
> "ERROR: column "film.description" must appear in the GROUP BY clause or be> used
On Fri, Sep 01, 2006 at 10:31:48AM -0300, Walter Cruz wrote:
> "ERROR: column "film.description" must appear in the GROUP BY clause or be
> used in an aggregate function"
>
> If I put that column on GROUP BY everything works ok. But I want understant
> why do I need to do that. Can someone teach
This seems to be the best solution. At first I was a bit skeptical about the perfomance using execute versus using pre-planned queries. However, I found that this model actually executes faster than the paramterized queries that I was trying (even using the if-then-endif model).
Thanks for this
Rob Tester wrote:
I have the need to have optional values for a query in a stored procedure
that I am building. (using postgres 8.1.4). This particular query
executes
against a huge table (several million rows) and has six optional
parameters
that can be sent to the function. If one of the para
On Wed, Aug 16, 2006 at 08:39:49PM -0700, Rob Tester wrote:
> What is the best way to write a query and get the planner to use indexes
> when you have optional parameters and columns that can contain NULL values?
Have you considered building a query string and using EXECUTE?
That's not as "neat" a
7.4.1 is quite old and has a number of serious known bugs. I'd suggest
you either upgrade to 8.1.4 (current) or, if you can't do that, at
least upgrade to 7.4.13 (latest 7.4) immediately.
Hilary Forbes wrote:
> Tom
>
> Thank you - I think that the underlying problem is that I was trying out
>
>
Tom
Thank you - I think that the underlying problem is that I was trying
out
REVOKE ALL FROM TABLE suppliers FOR public;
then connect as hilary and I can still see the table rows.
I appear to have to revoke each type eg
REVOKE SELECT FROM TABLE suppliers FOR public;
etc and then the code works.
Is
Hilary Forbes <[EMAIL PROTECTED]> writes:
> I have an existing table suppliers and I have created a new user
> 'hilary'
> REVOKE ALL on TABLE suppliers FROM hilary;
> now login as hilary
> SELECT * from suppliers;
> and I get all the records!!!
Most likely there's been a grant of (at least) select
REVOKE ALL on TABLE suppliers FROM hilary;
now login as hilary
SELECT * from suppliers;
and I get all the records!!!
If I create a **new** table though and then do the above, the permissionswork I
get a polite
message telling me "no go". Thissounds to me like a problem with earlier
compatibility
On Thu, 20 Jul 2006, Hilary Forbes wrote:
> Dear All
>
> We are running pg v 7.4.1 and importantly the database has been
> converted from earlier versions of pg (6.5 I seem to recall).
>
> I have an existing table suppliers and I have created a new user 'hilary'
>
> REVOKE ALL on TABLE suppliers F
Oisin Glynn wrote:
I have an issue with a select returning very slowly approx 198
seconds. I took a backup of this DB and restored it on another system
and it is returning in 28 seconds.
Windows 2000 PG Version 8.0.3 Data is inserted into the table row by
row.
Table has index on service
e
On 5/12/06, Michael Joseph Tan <[EMAIL PROTECTED]> wrote:
hi,
im new in postgresql, generally new in databases.
im trying to make a function using PGAdminIII which returns several types,
example, my query is:
"select count(id) as requests, organization from connection_requests group
by organiz
- Original Message
From: Andrew Sullivan <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thursday, May 11, 2006 1:18:08 PM
Subject: Re: [SQL] Help with a seq scan on multi-million row table
On Thu, May 11, 2006 at 10:09:44AM -0700, [EMAIL PROTECTED] wrote:
> Hi Markus & Tom,
>
On Thu, May 11, 2006 at 10:09:44AM -0700, [EMAIL PROTECTED] wrote:
> Hi Markus & Tom,
>
> Higher statistics for this column hm, I'd love to try changing
> it to see how that changes things, but I'm afraid I don't know how
> to do that. How can I change the statistics target value for this
> c
933
This number grows daily by... not sure how much, probably 5k a day currently.
Thanks,
Otis
- Original Message
From: Markus Schaber <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Sent: Thursday, May 11, 2006 6:33:55 AM
Subject: Re: [SQL] Help with a
Markus Schaber <[EMAIL PROTECTED]> writes:
> Did you try to set higher statistics targets for this columns?
Yeah, I was about to suggest the same after looking at the pg_stats
entry. It shows estimated num_distinct as 60825, ie only about 60k
distinct values in the column, which sounds kinda low
Hi, Otis,
[EMAIL PROTECTED] wrote:
> I'm not sure which numbers you are referring to when you said the estimate is
> off, but here are some numbers:
> The whole table has 6-7 M rows.
> That query matches about 2500 rows.
>
> If there are other things I can play with and help narrow this dow
s 6-7 M rows.
That query matches about 2500 rows.
If there are other things I can play with and help narrow this down, please let
me know.
Thanks,
Otis
- Original Message
From: Tom Lane <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Sent: Wednesday, May 10,
<[EMAIL PROTECTED]> writes:
> Aha! set hashjoin=off did the trick.
>-> Index Scan using ix_user_url_tag_user_url_id on
> user_url_tag userurltag0_ (cost=0.00..157.34 rows=103 width=14) (actual
> time=1.223..1.281 rows=5 loops=1666)
> Index Cond: (userurlta
d from something called Hibernate, and I'm not sure if that will let me
set enable_hashjoin=off through its API...
Thanks,
Otis
- Original Message
From: Tom Lane <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Sent: Wednesday, May 10, 2006 8:27:01 PM
Sub
<[EMAIL PROTECTED]> writes:
>-> Hash Join (cost=2797.65..140758.50 rows=3790 width=10)
> (actual time=248.530..380635.132 rows=8544 loops=1)
> Hash Cond: ("outer".user_url_id = "inner".id)
> -> Seq Scan on user_url_tag userurltag0_
1 - 100 of 298 matches
Mail list logo
