Judith wrote:
>Hi every body, somebody can show me hot to execute a
> query from a shell
echo QUERY HERE | psql databasename
Or, if you want to run several queries, run psql and run your queries there.
---(end of broadcast)---
TIP 2: Don'
Guy Fraser wrote:
> On Thu, 2006-06-04 at 13:53 +0530, AKHILESH GUPTA wrote:
> > dear all,
> > i want to encrypt and decrypt one of the fields in my table (i.e-
> > password field)
> > i have searched and with the help of pgcrypto package, using function
> > "crypt", i am able to encrypt my data,
TJ O'Donnell wrote:
> > If I have two threads modifying the same "bit" field:
> > thread1=> update table set bf=bf | '01000'
> > thread2=> update table set bf=bf | '1'
> > Will this operation always be safe (e.g. result in bf='11000')? Or
>
> Won't this always result in bf='11xxx', d
Tom Lane wrote:
> "Owen Jacobson" <[EMAIL PROTECTED]> writes:
> > I'd say there's no problem with this, PROVIDED you can ensure you'll
> > never abort before completing the computation.
>
> Not really an issue in Postgres: we do not suppor
Daniel Caune wrote:
> Is there any suggestion against using OUT parameter for local
> calculation such as using a local variable?
>
> CREATE OR REPLACE FUNCTION foo(a IN int,
>b1 OUT int,
>b2 OUT int)
> AS $$
> BEGIN
> FOR (...) LO
Here's one I used to convert an int to an interval in another project:
CREATE OR REPLACE FUNCTION to_interval (sec INTEGER) RETURNS INTERVAL AS $$
BEGIN
RETURN (sec || ' seconds')::INTERVAL;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
select to_interval (5);
to_interval
-
00:00:0
In general, to do anything useful with RETURN NEXT you need a loop. However,
it doesn't need to be a loop over another resultset: you can do a computation
in a loop, returning values as you go.
Excuse the outlook-ism.
-Owen
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PRO
Achilleus Mantzios wrote:
> O Tom Lane έγραψε στις Feb 24, 2006 :
>
> > By definition, an AFTER trigger is too late to change what was
> > stored. Use a BEFORE trigger.
>
> Too late if someone wants to store it.
> I wanna store the intented original values, thats why i use
> AFTER trigger.
> Bu
Kashmira Patel wrote:
> I did do an EXPLAIN ANALYZE as well, it also showed a
> sequential scan. The table has about 600+ rows, with around 6 of them
> matching the given id. Wouldn't an index scan be faster in this case?
Not necessarily. It's entirely possible, if your rows are small, that 600
Scott Marlowe wrote:
> On Wed, 2006-02-22 at 15:13, Andrew Sullivan wrote:
>
> > No, sorry. While we're at it, though, if you have the same field in
> > several tables, it's a good sign that your database is badly
> > normalised. You shouldn't have to update more than one table (which
> > is why
-Owen
-Original Message-
From: Henry Ortega [mailto:[EMAIL PROTECTED]
Sent: Friday, February 17, 2006 2:06 PM
To: Owen Jacobson
Subject: Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
This sounds good. I don't have plpgsql loaded though.
I am trying to load plpgsql and it's g
Henry Ortega wrote:
(question about set of all days between two dates)
I don't know of a builtin way to do it off the top of my head, but it's a
pretty simple function to write:
create function days (start date, finish date) returns setof date as $$
declare
curdate date;
begin
curdate := st
Kashmira Patel wrote:
> So I would have to put in lots of rows of data in the table
> before using the explain command?
No, but PostgreSQL's query planner may take a different approach for a small
table than a large one. The statistics used are generated during VACUUM
ANALYZE/VACUUM FULL ope
Daniel Caune wrote:
>
> Andrew Sullivan wrote:
>
> > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
> >
> > >
> > > Is there a way to force PostgreSQL using an index for a SELECT
> > > statement?
> >
> > Your best bet is to do
> >
> > set enable_indexscan=false;
> >
> > and then
SET SESSION enable_seqscan TO OFF could be interpreted as a strong hint to the
server that you want it to use indexes. It's not completely mandatory (the
server WILL still do a sequential scan if it has to) but postgresql will
strongly prefer index scans. You may also have some luck twiddling
Maciej Piekielniak wrote:
>
> Hello Owen,
>
> Wednesday, February 15, 2006, 8:56:05 PM, you wrote:
> >> ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL;
>
> OJ> Not under 7.4.
>
> Hmm, maybe postgres can copy constraints and properties in
> "create table .. as select"?
What is it
Maciej Piekielniak wrote:
>
> Wednesday, February 15, 2006, 8:31:17 PM, you wrote:
> OJ> Note that prior to 8.0 PostgreSQL does not support
> multiple ALTER actions in a single query. To get an
> equivalent effect, wrap separate ALTER TABLE queries in a transaction:
>
> OJ> BEGIN;
> OJ> alter
Andreas Kretschmer wrote:
>
> Maciej Piekielniak <[EMAIL PROTECTED]> schrieb:
>
> > Hello Andreas,
> >
> > Wednesday, February 15, 2006, 7:54:28 PM, you wrote:
> > AK> test=# alter table xyz alter column id set default
> nextval('xyz_seq'), alter column foo set default '';
> >
> > PGAdmin-SQL:
Andrew Sullivan wrote:
>
> On Wed, Feb 15, 2006 at 08:15:46AM -0800, Ken Hill wrote:
> > It has been suggested to me to increase my work_mem to make queries
> > preform faster. I believe I do this in the 'postgresql.org' file. I
> > seem to have two of these files:
> >
> > /etc/postgresql/7.4/ma
Andreas Joseph Krogh wrote:
> Any idea why this works:
>
> SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g,
> onp_group g2
> WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM
> onp_group_children WHERE
> child_id = g2.id)
> AND g2.id IN(1,2,109,105, 112);
>
> And not this:
>
Ken Hill wrote:
> I need some help with a bit of SQL. I have two tables. I want
> to find records in one table that don't match records in another
> table based on a common column in the two tables. Both tables
> have a column named 'key100'. I was trying something like:
>
> SELECT count(*)
> FRO
Bryce Nesbitt wrote:
> Markus Schaber wrote:
>
> > Bryce Nesbitt wrote:
> >
> >
> >> BEGIN;
> >> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> >> WHERE reservation_id IN
> >> (select reservation_id from reservations where date > magic)
> >> );
> >> DELETE F
Bryce Nesbitt wrote:
> Owen Jacobson wrote:
>
> > BEGIN;
> > DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> > WHERE reservation_id = reservation_to_delete);
> > DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
> &
Owen Jacobson wrote:
> BEGIN;
> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> WHERE reservation_id = reservation_to_delete);
> DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
> DELETE FROM reservations WHERE reservation_id =
> res
Bryce Nesbitt wrote:
> When I delete a record from a certain table, I need to delete a
> (possibly) attached note as well. How can I do this with
> postgres? The
> tables are like this:
>
> reservation
> reservation_id
> stuff...
>
> isuse
> issue_id
> reservation_id
r
Daniel Caune wrote:
> Hi,
>
> I try to find in the documentation whether PostgreSQL supports job,
> but I miserably failed. Does PostgreSQL support job? If not, what
> is the mechanism mostly adopted by PostgreSQL administrators for
> running jobs against PostgreSQL? I was thinking about usi
Alvaro Herrera wrote:
> [EMAIL PROTECTED] wrote:
>
> > test:/opt/pgsql# echo "copy test from '/data/log/bla';" | psql logdb
> > COPY
> >
> > But when I try to execute it as root user I get the following error:
> >
> > [est/data/log] su - postgres -c "echo 'copy test from
> /data/log/bla;'|
> >
Mark Fenbers wrote:
> I'm having trouble with installing pgadmin. If this is not the
> apporpriate group for seeking help with this, please excuse
> me and tell
> the correct one. I have not found anything else more appropriate.
>
> 'make' for pgadmin fails, I think it is because I don't hav
chester c young wrote:
> anybody have a good way to impliment constants in the database?
>
> using an immutable pgpgsql function, constant_name() - works, but not
> necessarily very efficient.
A function declared IMMUTABLE should be efficient enough; it'll usually be
evaluated once per query at
Daniel Hertz wrote:
> Given a set of checkbox values that are submitted through an
> html form,
> how do you loop through the submitted values to update more
> than one row
> in a table?
>
> Imagine a table called 'message_table':
>
> mid | message | status
> +-+---
> 1 |
Michael Höller wrote:
> Hello,
>
> I initially thought this is simple.. I want to relpace a character to
> nothing. Eg. relace "B" to "" -> ABCD to ACD.
>
> All me approches vaild but I am sure that I have seen it already and
> think it was not tricky..
>
> Can someone please help me ?
From t
You
can turn off autocommit by explicitly starting a transaction (eg. with BEGIN; in
psql or by turning off autocommit through ODBC). Statements inside the
transaction will not have their own, automatically-commited
transactions.
HTH,
Owen
-Original Message-From:
[EMAIL PROT
Marc G. Fournier wrote:
> Moved off of -hackers, since its long gotten out of that realm :)
>
> On Thu, 1 Sep 2005, Tom Lane wrote:
>
> > "Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> >> On Mon, 29 Aug 2005, Tom Lane wrote:
> >>> No, because there's no built-in cast from smallint to bool.
> >
As I
mentioned in email, the function "encrypt" exposed by libcrypt (the -lcrypt part
of your command line) has nothing to do with the "encrypt" function available in
pgcrypto.sql, which You Cannot Call From C. You have to embed calls to it
inside an SQL query like
INSERT
INTO some_table
Nick Stone wrote:
> Hope this helps
>
> SELECT
> *
> FROM
> speed_history as outside etc..
> WHERE
> (speed = (
> SELECT
> speed
> FROM
> speed_history as inside etc..
> WHERE
>
Jeff Lu wrote:
> Owen Jacobson <[EMAIL PROTECTED]> wrote:
>> Jeff Lu wrote:
>>
>>> Hi,
>>>
>>> I'm interested in encrypting an column in table. Are there any
>>> example using "C" to create the encrypted column, inserting an
Jeff Lu wrote:
> Hi,
>
> I'm interested in encrypting an column in table. Are there any
> example using "C" to create the encrypted column, inserting and
> retreiving data to/from it?
>
> the table is:
> CREATE TABLE mytable (
> id SERIAL PRIMARY KEY,
> crypted_content BYTEA
> );
>
> I
Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > I believe running count(*) means fulltable scan, and there's no way
> > to do it without it. But what about some "intermediate" table, with
> > the necessary counts?
>
> There's a fairly complete discussion in the PG list archives of a
> reasonably-e
Salve.
I understand from various web searches and so on that PostgreSQL's MVCC
mechanism makes it very hard to use indices or table metadata to optimise
count(*). Is there a better way to guess the "approximate size" of a table?
I'm trying to write a trigger that fires on insert and performs som
39 matches
Mail list logo
