[SQL] Faster count(*)?

[Owen Jacobson]

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 some
maintenance (collapsing overlapping boxes into a single large box,
specifically) as the table grows.  My initial attempt involved count(*) and,
as the number of pages in the table grew, that trigger bogged down the
database.

Any thoughts?

-O


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

Re: **SPAM** [SQL] Faster count(*)?

[Owen Jacobson]

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-efficient scheme for maintaining such counts via triggers.
> It wasn't efficient enough that we were willing to impose the overhead
> on every application ... but if you really NEED a fast count(*) you
> could implement it.  I'd like to see someone actually do it and put
> up working code on pgfoundry; AFAIK it's only a paper design so far.
>
> If you only want a very-approximate count, the best bet is to rely on
> the planner's estimates, eg
>
> regression=# explain select * from tenk1;
>  QUERY PLAN
> -
>  Seq Scan on tenk1  (cost=0.00..458.00 rows=1 width=244)
> ^
>
> Current best practice is to run the explain and parse out the "rows"
> figure using a perl (or axe-of-choice) regexp, though we could be
> persuaded to supply a simpler API if there's enough demand for it.

Yick.  Ok, given all of that, I've rewritten the trigger in question to fire
on different, indexable criteria (difference between "earliest" and "latest"
rows in the table).

Thanks, everyone.

Owen


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

Re: [SQL] Fwd: How to encrypt a column

[Owen Jacobson]

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'm getting (null) in the field with the following sql statement:
>
> strcpy(data, "data to be encrypted");
> sprintf(query_buff, "insert into mytable values('%s', '%s')", key,

Try printing out the contents of 'query_buff' here.  You shouldn't be
getting NULLs in the table, but you may well be getting '' (the empty
string) if encrypt(data, 'foo', 'bar') returns "" or (char *) (0).

> encrypt(data, 'foo', 'bar'));
> PQexec(conn, query_string);

Even better, don't do it this way.  This is potentially open to SQL
injection attacks and definitely open to problems with "interesting" values
of key.

Look up PQprepare/PQexecParams for separating the query string from the
values.  I use ODBC indirectly, so I can't help you directly, but the docs
at

should help.

> Another question is can the encrypted column be of type "text" ?

Can't see any reason why not, so long as the encrypted data is represented
as text.


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

Re: [SQL] Fwd: How to encrypt a column

[Owen Jacobson]

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 and
>>> retreiving data to/from it?
>>>
>>> the table is:
>>> CREATE TABLE mytable (
>>> id SERIAL PRIMARY KEY,
>>> crypted_content BYTEA

Consider making this column NOT NULL (crypted_content BYTEA NOT NULL).

>>> );
>>>
>>> I'm getting (null) in the field with the following sql statement:

See below: you're getting the string '(null)' and not a NULL string.

>>> strcpy(data, "data to be encrypted");
>>> sprintf(query_buff, "insert into mytable values('%s', '%s')", key,
>>
>> Try printing out the contents of 'query_buff' here. You shouldn't be
>> getting NULLs in the table, but you may well be getting '' (the
>> empty string) if encrypt(data, 'foo', 'bar') returns "" or (char *)
>> (0).
>>
>>> encrypt(data, 'foo', 'bar'));
>>> PQexec(conn, query_string);
>
> I do get a couple of warnings at compile time:
> intrasend.c:496:37: warning: multi-character character constant
> intrasend.c:496:44: warning: multi-character character constant
> intrasend.c:517:84: warning: character constant too long for its type

Somewhere in your code you've used '' instead of "" to delimit a string
literal.  Those line number and column number references will tell you
where.  The code you originally posted used the ' ' characters for 'foo' and
'bar', for instance.

(Yeah, it's inconvenient using different quoting conventions in the same
program.)

> Here's what is in the variable
> "data" = "0018`0018`64045`08112005`64045`1Discount=0.00;``2``~"
>
> printf("%s", encrypt(data, "foo", "bar"))
> prints out (null)

Well, there's the problem.  The encrypt function is returning the string
"(null)", which sprintf is (correctly) inserting into your query, and which
postgresql is then (correctly) interpreting as the literal string '(null)'
when storing the data.

Your problem is either in the encrypt() function you've written or in the
way you're using it.


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

Re: [SQL] SQL Newbie

[Owen Jacobson]

Nick Stone wrote:

> Hope this helps
>
> SELECT
>   *
> FROM
>   speed_history as outside etc..
> WHERE
>   (speed = (
>   SELECT
>   speed
>   FROM
>   speed_history as inside etc..
>   WHERE
>   (outside.interface = inside.interface)
>   LIMIT 1
>   ORDER BY
>   speed DESC
>   )
>   )

Worth noting that ORDER BY speed DESC LIMIT 1 is fundamentally the same as
MAX (speed), except that MAX (speed) doesn't take advantage of any index
present.  This is a fairly common idiom but a self-described SQL Newbie may
not see the equivalence instantly.

-Owen


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] What libraries need to be included in C program for encrypt and decrypt functions?

[Owen Jacobson]

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 (some_col) VALUES (ENCRYPT ('Hello', '', 
'aes'));

  -Original Message-From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On 
  Behalf Of The OneSent: Tuesday, August 16, 2005 7:00 
  AMTo: pgsql-sql@postgresql.orgSubject: [SQL] What 
  libraries need to be included in C program for encrypt and decrypt 
  functions?
  Hi,
   
  Can some body tell me what I need to include in the makefile for my C 
  program so it will call the correct encrypt/decrypt functions.
   
  This is what I have in the makefile
  testcrypto:  ${CC} -o ${BIN}/testcrypto.exe testcrypto.c 
  -I/usr/include/postgresql/server/libpq -L/lib/postgresql -lpq -lcrypt
   
  I'm calling the function like this 
  printf("test encryption: %s", encrypt("hello", "", "aes");
   
  But I'm getting too many arguments in function encrypt.
   
  Thank you
   
   
  
  
  Start 
  your day with Yahoo! - make it your home page 

Re: [SQL] [HACKERS] ALTER TABLE ( smallinto -> boolean ) ...

[Owen Jacobson]

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.
> >
> >> 'k, I just took a read through the "CREATE CAST" man page, 
> and don't think
> >> I can use that for this,
> >
> > Sure you can.  Make a SQL or PLPGSQL function that does the 
> conversion
> > you want and then create a cast using it.
> 
> Ah, okay, I just re-read the man page and think I stumbled 
> upon what I 
> overlooked the first time ...
> 
> all I want to do is:
> 
> CREATE CAST ( 0 AS boolean )
> WITH FUNCTION 
>   AS ASSIGNMENT;
> 
> And then each time I try to insert a '0' into a BOOLEAN 
> field, it will 
> auto convert that (based on my function) to 'f' ...
> 
> And I'd need to do a second one for 1 -> 't' ...
> 
> Am I reading it right this time ... ?

Here's what I'm using:

CREATE OR REPLACE FUNCTION int2bool (INTEGER) RETURNS BOOLEAN AS $$
  SELECT ($1 != 0);
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION bool2int (BOOLEAN) RETURNS INTEGER AS $$
  SELECT CASE WHEN $1 THEN 1
  WHEN NOT $1 THEN 0
  ELSE NULL END;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;

CREATE CAST (INTEGER AS BOOLEAN)
  WITH FUNCTION int2bool (INTEGER)
  AS ASSIGNMENT;

CREATE CAST (BOOLEAN AS INTEGER)
  WITH FUNCTION bool2int (BOOLEAN)
  AS ASSIGNMENT;

done.

Owen

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

Re: [SQL] Turn off auto-commit

[Owen Jacobson]

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 PROTECTED] [mailto:[EMAIL PROTECTED]On 
  Behalf Of Joÿffe3o CarvalhoSent: Friday, September 02, 
  2005 10:22 AMTo: PG-SQL lista de mailSubject: [SQL] Turn 
  off auto-commit
  
  Im working in postgresql 8.0 in windows XP. How can I turn off 
  auto-commit.
  I already tried a few things but it didn't worked.Regards
  João Carvalho
  
  
  Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!

Re: [SQL] how to replace

[Owen Jacobson]

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 the manual:

replace(string text, from text, to text) returns text
  Replace all occurrences in string of substring from with substring to.



To replace only on output, for example:

SELECT replace (sometextcolumn, from 'B', to '') FROM sometable;

To replace the data,

UPDATE sometable SET sometextcolumn = replace (sometextcolumn, from 'B', to '');

HTH,
Owen

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

Re: [SQL] Multi-row update w. plpgsql function

[Owen Jacobson]

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  |  Text1   |  H
>   2  |  Text2   |  H
>   3  |  Text3   |  H
>   4  |  Text4   |  H
>  
> A web page presents the user with all messages flagged with 'H'. User 
> checks messages 1,3 and 4 and submits form.
> (i.e. approved=1&approved=3&approved=4)
>  
> After performing postgreSQL update, rows 1, 3 and 4 would be 
> updated to:
>  
> mid | message | status
> +-+---
>   1  |  Text1   |  A
>   2  |  Text2   |  H
>   3  |  Text3   |  A
>   4  |  Text4   |  A

BEGIN;
UPDATE message_table SET status = 'A' WHERE mid = 1;
UPDATE message_table SET status = 'A' WHERE mid = 3;
UPDATE message_table SET status = 'A' WHERE mid = 4;
COMMIT;

would do that.  Have your application generate an appropriate UPDATE line for 
each "approved" entry in the form data, wrap it in a transaction, and away you 
go.

> I have never written a plpgsql function, but tried:
> 
> CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNS 
> integer AS
> $body$
> DECLARE
>  new_status varchar;
>  new_sample record;
> 
> BEGIN
>  new_status := 'A';
> 
>  FOR new_sample IN SELECT * FROM message_table WHERE 
> status='H' ORDER BY 
> mid LOOP

1.  No need for ORDER BY here, we're not doing anything user-visible or 
order-dependent with the data, so it's a waste of CPU time.
2.  You're not using new_sample for anything, just retrieving it.

>   UPDATE message_table SET status = new_status
>   WHERE mid = approved;

Consider that you can only pass a single value to an INTEGER parameter 
("approved"); this will repeatedly update a single entry where mid = approved.  
You could simplify the function as written to

CREATE OR REPLACE FUNCTION update_messages (INTEGER) RETURNS VOID AS $$
  UPDATE message_table SET status = 'A' WHERE mid = $1;
$$ LANGUAGE SQL;

>  END LOOP;
> 
>  RETURN 1;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>  
> I call the function with:
> SELECT update_messages();
> 
> I'm using apache cocoon, which is why you see the variable 
> placeholder: 
> );

I'm not familiar with Cocoon, but I'd expect that to return only the first of 
the "approved" values from the HTTP request.  If you add logging to the stored 
function (RAISE NOTICE 'approved: %', approved; near the start of the function, 
for instance) and tell PostgreSQL to store the logs, you can see what values 
your function is actually being called with.

What you really want to do is begin a transaction, loop over all the values of 
approved present in the form data and call (the rewritten version of) 
update_messages for each one, then commit the transaction.

-Owen

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

Re: [SQL] best way for constants in the database

[Owen Jacobson]

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 most.  The real answer is, as usual, "it depends."  
What're you trying to accomplish with a constant?

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

Re: [SQL] pgadmin

[Owen Jacobson]

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 have 
> wxWidgets.  
> wxWidgets fails.  I think it is because I don't have Motif/Lesstif.  
> Lesstif fails because of another large list of dependencies.  
> For Pete's 
> sake!  Do I have to double the size of my Operating System (which is 
> Linux, Redhat 9, BTW) just to install pgadmin?  I've 
> installed lots of 
> software packages over the years, mostly through the 
> ./configure;make;make install sequence, but this is the most 
> difficult 
> installation I've ever encountered.  I don't have a stripped down 
> version of the O/S either... it's pretty much a full install of RH9.
> 
> Does anyone have any ideas I could try?

Find and install an RPM (or an SRPM) for pgadmin, rather than installing it 
from the source tarball?  The pbone RPM search suggests that there are such 
things, though they may be a few versions behind*.

When I was using redhat 9, I found that apt-rpm was invaluable; the lack of any 
other automatic dependency resolution tools did rather make RPMs a pain.

-Owen

* for Redhat 9.  There's more-recent pgadmin packages for Fedora, which may 
have spec files you can make use of to build an RPM for older Redhat.

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

Re: [SQL] psql in the command line

[Owen Jacobson]

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;'|
> > psql logdb"
> > ERROR:  syntax error at or near "/" at character 16
> > LINE 1: copy test from /data/log/bla;
> 
> This is clearly not the same line.  The quotes are not in the same
> position as in the line above.
> 
> su - postgres -c "psql logdb -c 'copy test from \'data/log/bla\''"

Or, preferably:

$ echo "copy test from '/data/log/bla';" | sudo -u postgres psql logdb

HTH,
Owen

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

Re: [SQL] Does PostgreSQL support job?

[Owen Jacobson]

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 using
> cron/plsql/sql-scripts on Linux.

The answer really depends on what you mean by "jobs".  If you have a database 
task that can be expressed as a series of commands with no interaction 
involved, you can just put those commands in a file (your-job-name.sql) and run 
it using psql and cron:

# replace leading stars with cron time settings
* * * * * psql your-database -i your-job-name.sql

If you need something more complex, either a function which is executed from a 
script or a full-blown client program may be required.  IME that's fairly rare.

-Owen

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

Re: [SQL] Delete with join -- deleting related table entries?

[Owen Jacobson]

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
references reservation (reservation_id) -- ADD
> stuff..
> 
> note
> issue_id
references isuse (issue_id) -- ADD (kept typo in example)
> text comments...
> 
> A select that pulls out what I want to delete is:
> 
> SELECT reservation_id,issue_id,note_id,eg_note.comments FROM
> eg_reservation
>LEFT JOIN eg_issue USING (reservation_id)
>LEFT JOIN eg_note USING (issue_id)
>WHERE reservation_id > condition;
> 
> Can anyone help me turn this into a DELETE statement?

1.  Add foreign key references between the tables to ensure that there are only 
notes and issues (isuses? :) for existing issues and reservations respectively. 
 You can make those references 'ON DELETE CASCADE' so that a delete of the 
original reservation cascades down to related entries in the issue table, which 
in turn cascade down to the related entries in the note table.

2.  Or...

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 = reservation_to_delete;
END;

with an appropriate value or expression substituted into reservation_to_delete. 
 This would be the "hard way", but (as it's in a single transaction) will still 
protect other clients from seeing a partial delete.

Get yourself a good, non-MySQL-specific database book, which should explain how 
referential integrity is handled in databases.

-Owen

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

Re: [SQL] Delete with join -- deleting related table entries?

[Owen Jacobson]

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 = 
> reservation_to_delete;
> END;

That should be COMMIT;, not END;.  Been writing too much pl/pgsql.
-Owen

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Delete with join -- deleting related table entries?

[Owen Jacobson]

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;
> >   DELETE FROM reservations WHERE reservation_id = 
> reservation_to_delete;
> > COMMIT;
> >
> > With an appropriate value or expression substituted into 
> > reservation_to_delete.  This would be the "hard way", but (as 
> > it's in a single transaction) will still protect other 
> > clients from seeing a partial delete.
>
> Yup, that's exactly how I delete reservations one a time.   But here I
> need to select a few thousand reservations, and I don't think 
> this will
> work:
> 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 FROM isuse WHERE reservation_id IN
>  (select reservation_id from reservations where date > magic)
>   DELETE FROM reservations WHERE reservation_id IN
>  (select reservation_id from reservations where date > magic)
> COMMIT;
> 
> I suppose I can do the subselect as a perl wrapper, but I was thinking
> that maybe SQL could do it all for me

Further thinking produced the following functional example.

CREATE TABLE reservation (
  reservation_id INTEGER NOT NULL,
  date DATE NOT NULL
);

CREATE TABLE issue (
  issue_id INTEGER NOT NULL,
  reservation_id INTEGER NOT NULL
);

CREATE TABLE note (
  issue_id INTEGER NOT NULL
);

INSERT INTO reservation VALUES (1, '2006-01-01');
INSERT INTO reservation VALUES (2, '2006-01-15');
INSERT INTO reservation VALUES (3, '2006-02-01');

INSERT INTO issue VALUES (1, 1);
INSERT INTO issue VALUES (2, 1);
INSERT INTO issue VALUES (3, 2);
INSERT INTO issue VALUES (4, 2);
INSERT INTO issue VALUES (5, 3);
INSERT INTO issue VALUES (6, 3);

INSERT INTO note VALUES (1);
INSERT INTO note VALUES (2);
INSERT INTO note VALUES (3);
INSERT INTO note VALUES (4);
INSERT INTO note VALUES (5);
INSERT INTO note VALUES (6);

-- PostgreSQL 8.0 and prior
BEGIN;
  DELETE FROM note
WHERE note.issue_id = issue.issue_id
  AND issue.reservation_id = reservation.reservation_id
  AND reservation.date > '2006-01-16';
  DELETE FROM issue
WHERE issue.reservation_id = reservation.reservation_id
  AND reservation.date > '2006-01-16';
  DELETE FROM reservation WHERE date > '2006-01-16';
COMMIT;

-- PostgreSQL 8.1 and later
BEGIN;
  DELETE FROM note USING issue, reservation
WHERE note.issue_id = issue.issue_id
  AND issue.reservation_id = reservation.reservation_id
  AND reservation.date > '2006-01-16';
  DELETE FROM issue USING reservation
WHERE issue.reservation_id = reservation.reservation_id
  AND reservation.date > '2006-01-16';
  DELETE FROM reservation WHERE date > '2006-01-16';
COMMIT;

The version using subselects works fine, too.

-Owen

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

Re: [SQL] Delete with join -- deleting related table entries?

[Owen Jacobson]

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 FROM isuse WHERE reservation_id IN
> >>  (select reservation_id from reservations where date > magic);
> >>   DELETE FROM reservations WHERE reservation_id IN
> >>  (select reservation_id from reservations where date > magic);
> >> COMMIT;
> >>
> >> I suppose I can do the subselect as a perl wrapper, but I 
> >> was thinking that maybe SQL could do it all for me
> >
> > Why do you think this won't work? (provided you add the 
> missing ) and ; :-)
> 
> Wow.  It worked.  Cool.  I guess the reservations don't get deleted
> until they are not needed any more...
> 
> Not the fastest thing in the world.  But it worked.

EXPLAIN works with DELETE too.  Joins in general on unindexed fields can be 
pretty slow; if you see a lot of Seq Scan entries in the EXPLAIN output, you 
might consider having indexes added on appropriate fields.

Then again, if you were going to do that, you might as well just fix the schema 
to use REFERENCES...ON DELETE CASCADE and be done with it. :)

-Owen

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

Re: [SQL] Non Matching Records in Two Tables

[Owen Jacobson]

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(*)
> FROM table1, table2
> WHERE (table1.key100 != table2.key100);
> 
> But the query is very slow and I finally just cancel it. Any help
> is very much appreciated.

That's a cartesian join, there, and it'll be huge (on the order of N*M rows, 
where N and M are the number of rows in the first and second tables 
respectively).

It sounds like, from your description, you want to find rows in table1 that 
don't have a corresponding row in table2.  This should work:

SELECT count(*)
  FROM table1 LEFT JOIN table2 ON table1.key100 = table2.key100
  WHERE table2.key100 IS NULL;

This will still be fairly slow unless there are indexes on table1.key100 and 
table2.key100, but nowhere near as slow as the original query.

Frank Bax's solution will work if what you want is a count of rows in table1 
that don't have a corresponding row in table2 or in table2 that don't have a 
corresponding row in table1; for that specific requirement you may actually be 
better off doing two queries (one for each table) and adding the results 
together.

-Owen

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

Re: [SQL] Problems with distinct

[Owen Jacobson]

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:
> 
> SELECT g.id, g.p_id, distinct(g.groupname) 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);

Distinct is an SQL keyword, not a function.  The former is exactly equivalent to

SELECT DISTINCT g.groupname, g.id,  FROM 

except that the first field is inside a (trivial) expression that makes it look 
like a function call.

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

Re: [SQL] Modifying postgresql.org file

[Owen Jacobson]

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/main/postgresql.org
> > /usr/share/postgresql/7.4/postgresql.conf.sample
> 
> Where did you get this version of Postgres?  The main config file
> should be $PGDATA/postgresql.conf.  Anything else is probably the
> result of your system's packaging having done some magic.  But in any
> case, unless I'm misremembering, the work_mem setting isn't in 7.4.

In 8.0, the setting sort_mem was renamed to work_mem (sayeth the 8.0.0 release 
notes).  The 7.4 documentation describes the same features for sort_mem that 
are now described under work_mem, so adjusting sort_mem should be appropriate.

-Owen

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

Re: [SQL] alter table

[Owen Jacobson]

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:
> > 
> > alter table xyz alter column id set default 
> nextval('xyz_seq'), alter column foo set default '';
> > 
> > ERROR:  syntax error at or near "," at character 63
> 
> Hmm.
> 
> test=# select version();
>version
> --
> 
>  PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc 
> (GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6)
> (1 row)

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:

BEGIN;
alter table xyz alter column id set default nextval('xyz_seq');
alter table xyz alter column foo set default '';
COMMIT;

Also, are you sure you want '' as a column default, and not ALTER COLUMN foo 
DROP DEFAULT?

-Owen

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

Re: [SQL] alter table

[Owen Jacobson]

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 table xyz alter column id set default nextval('xyz_seq');
> OJ> alter table xyz alter column foo set default '';
> OJ> COMMIT;
> OJ> Also, are you sure you want '' as a column default, and 
> not ALTER COLUMN foo DROP DEFAULT?
> OJ> -Owen
> 
> OK. THX. Second question:
> 
> First, maybe set many fields with the same action - ex. set default?
> 
> Ex. on mysql
> 
> ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
>  MODIFY specific_name char(64) DEFAULT '' NOT NULL,
>  MODIFY sql_data_access
> enum('CONTAINS_SQL',
>  'NO_SQL',
>  'READS_SQL_DATA',
>  'MODIFIES_SQL_DATA'
> ) DEFAULT 'CONTAINS_SQL' NOT NULL

Under PostgreSQL 7.4 you'd need to do those as three separate ALTER TABLE 
statements:

BEGIN;
ALTER TABLE proc ALTER name DEFAULT '' NOT NULL;
ALTER TABLE proc ALTER specific_name DEFAULT '' NOT NULL;
... and so on ...
COMMIT;

Note that ALTER TABLE under postgresql cannot change a column's type (including 
precision or length).  You can fake it by renaming the existing column, 
creating a new column of the appropriate type, UPDATEing data from the old 
column to the new column, [setting the new column's constraints,] and finally 
removing the old column, but it's a long-winded process.

> Second, can i modify more than 1 option with alter table on 
> one field?:
> 
> ex (mysql):
> ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL;

Not under 7.4.

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

   http://archives.postgresql.org

Re: [SQL] alter table

[Owen Jacobson]

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 you're actually trying to accomplish?  There's got to be a better 
way, but without understanding what you're doing (rather than how) it's hard to 
give you advice.

CREATE TABLE AS and SELECT INTO only reproduce data, not metadata.  AFAIK 
duplicating a table's constraints involves fishing around in the pg_ system 
tables.

-Owen

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

   http://archives.postgresql.org

Re: [SQL] How to force PostgreSQL using an index

[Owen Jacobson]

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 the 
cpu_index_tuple_cost option.

- Owen

(Apologies for the Outlookism.)

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Caune
Sent: Wednesday, February 15, 2006 1:59 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] How to force PostgreSQL using an index


Hi,
 
Is there a way to force PostgreSQL using an index for a SELECT statement?  I 
just want to confirm that the index PostgreSQL decides to use is better than 
the index I supposed PostgreSQL would use (I already analyze the table).
 
Regards,
 
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
 

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

Re: [SQL] How to force PostgreSQL using an index

[Owen Jacobson]

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 do the EXPLAIN ANALYSE for your select.
> 
> I see, but that doesn't explain whether it is possible to specify the
> index to use.  It seems that those options just force PostgreSQL using
> another plan.

(snip)

> I have an index on EVENT_DATE_CREATED that does it job.  But I though
> that I can help my favourite PostgreSQL if I create a 
> composite index on
> EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED
> is more dense that EVENT_NAME).
> 
> PostgreSQL prefer the simple index rather than the composite index (for
> I/O consideration, I suppose).  I wanted to know how bad the composite
> index would be if it was used (the estimate cost).

Drop the simple index and re-create it when you're done?

As I understand it, the problem with letting clients specify which indexes to 
use is that they tend, on the whole, to be wrong about what's most efficient, 
so it's a feature almost specifically designed for shooting yourself in the 
foot with.  I agree that it'd be useful for experimenting with indexing 
schemes, but then, so is DROP INDEX.

-Owen

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

Re: [SQL] Using EXPLAIN-ANALYZE

[Owen Jacobson]

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 operations and, under 8.1, are probably maintained by 
autovacuum, but you can always vacuum manually and see if that changes the 
query plan.

-Owen

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.

[Owen Jacobson]

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 := start;
  while (curdate <= finish) loop
return next curdate;
curdate := curdate + 1;
  end loop;
  return;
end;
$$ language plpgsql;

# select * from days ('2006-02-01', '2006-02-07');
days

 2006-02-01
 2006-02-02
 2006-02-03
 2006-02-04
 2006-02-05
 2006-02-06
 2006-02-07
(7 rows)


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

Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.

[Owen Jacobson]

That usually indicates that, for whatever reason, plpgsql.so is from a 
different version of PostgreSQL than the database server.  If you installed 
PostgreSQL from source, make sure you configured the server to look in the same 
lib dir as its libs were installed to; if you've installed from package 
management of some kind (RPM?) make sure you have the same versions of all 
postgres-related packages.

You should also upgrade, if possible.  7.3 is effectively obsolete (37 releases 
old); there are a number of bugfixes and performance improvements in more 
recent versions.

-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 giving me:
ERROR:  Load of file /usr/lib/pgsql/plpgsql.so failed: 
/usr/lib/pgsql/plpgsql.so: undefined symbol: xlateSqlType 
createlang: language installation failed

I have pgsql 7.3.2
I am googling and can't seem to find the answer. Any help would be appreciated.


On 2/17/06, Owen Jacobson <[EMAIL PROTECTED]> wrote:
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 := start;
  while (curdate <= finish) loop
return next curdate;
curdate := curdate + 1;
  end loop;
  return;
end;
$$ language plpgsql;

# select * from days ('2006-02-01', '2006-02-07');
days

2006-02-01
2006-02-02
2006-02-03
2006-02-04
2006-02-05
2006-02-06
2006-02-07
(7 rows)


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

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

Re: [SQL] Update in all tables

[Owen Jacobson]

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 there isn't a way to do this automatically).
> 
> Unless they're all FK/PK pairs, in which case you on update / delete
> cascade FKs.

It's entirely too bad this has to be designed into the DDL for the table.  It'd 
be nice if you could specify "CASCADE" on UPDATE or DELETE queries.  I don't 
see that it's any more of a gun than ON UPDATE/ON DELETE CASCADE in the foreign 
key definition.

-Owen

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

Re: [SQL] Question about index scan vs seq scan when using count()

[Owen Jacobson]

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 rows 
will fit on a single disk page.  The index will be stored on a(t least one) 
separate disk page.  The cost of loading a page from disk pretty much swamps 
the cost of processing rows on a page, so in general the server tries to 
minimize the number of pages used.  To use an index for a one-page table, it'd 
have to load two pages (the table and the index); to do a sequential scan over 
a one-page table it only has to load the table.

Indexes are useful because they allow the DB to reduce the total number of 
pages loaded to complete a query.

-Owen

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] After Trigger assignment to NEW

[Owen Jacobson]

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.
> But i would like to alter what a final AFTER trigger would see.
> 
> I'll elabarote a little.
> 
> An update happens.
> The row is stored.
> An after trigger is fired that alters some NEW columns
> (nullifies them), aiming for a subsequent trigger
> to see the altered results .
> 
> It should be something like a pointer to a HeapTuple, (right?),
> so that would be feasible i suppose.
> 
> I would not even make a post if it was something that trivial.
> 
> I hope you get my point.

Your real problem is that the "subsequent" trigger has behaviour you don't 
like.  That's what you should be fixing.  If dbmirror has no way to exclude 
specific tables from mirroring, take it up with them as a feature request, or 
patch dbmirror to work how you want it to.

AFTER triggers *must* receive the row that was actually 
inserted/updated/deleted.  If they could receive a "modified" row that didn't 
reflect what was actually in the database, all sorts of useful trigger-based 
logging and replication patterns wouldn't work, and there's really no other way 
to implement them.  See also Tom Lane's other message for further implications 
of being able to modify the rows seen by AFTER triggers.

I'd also be hesitant to write triggers that have to execute in a specific order.

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

Re: [SQL] plpqsql and RETURN NEXT requires a LOOP?

[Owen Jacobson]

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 PROTECTED] Behalf Of Davidson, Robert
Sent: Tuesday, March 21, 2006 9:51 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] plpqsql and RETURN NEXT requires a LOOP?


From my reading of 36.7.1 Returning from a Function
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
it appears that RETURN NEXT in a plpgsql function requires you to loop through 
the result set. Is this correct? If so, I would be happy to post this example 
to the interactive docs (which could use a RETURN NEXT example), but wanted to 
make sure that I wasn't missing something more elegant or more efficient.
Best Regards,
Robert Davidson
-
CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO test VALUES ('a', 1);
INSERT INTO test VALUES ('a', 2);
INSERT INTO test VALUES ('b', 5);
INSERT INTO test VALUES ('b', 6);
CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM test WHERE textcol = pText LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;
SELECT * FROM ReturnNexting('a');

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

   http://archives.postgresql.org

Re: [SQL] Using a parameter in Interval

[Owen Jacobson]

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:05
(1 row)

You should be able to replace ' seconds' with ' weeks' just fine.

Excuse the outlook-ism,
-Owen
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Davidson, Robert
Sent: Tuesday, March 21, 2006 4:53 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Using a parameter in Interval


No matter how I try to concatenate, I can't seem to get a parameter to be used 
by INTERVAL in a function:
CREATE OR REPLACE FUNCTION Testing(TrailingWeeks int) RETURNS date AS $$
BEGIN
RETURN current_date - INTERVAL (CAST(TrailingWeeks AS varchar) || ' 
weeks');
END;
$$ LANGUAGE plpgsql;
--select * from testing(1);
ERROR:  syntax error at or near "CAST" at character 34
QUERY:  SELECT  current_date - INTERVAL (CAST( $1  AS varchar) || ' weeks')
CONTEXT:  SQL statement in PL/PgSQL function "testing" near line 2
I have tried concatenating it as a declared variable (with and without 
apostrophes)
1 weeks
And 
'1 weeks'
With no success. Any tips?
Many thanks,
Robert

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] OUT parameter

[Owen Jacobson]

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 (...) LOOP
> b1 = (...);
> b2 = (...);
>   END LOOP;
> END;
> $$ LANGUAGE PLPGSQL;

I'd say there's no problem with this, PROVIDED you can ensure you'll never 
abort before completing the computation.  It's not a good idea to modify out 
parameters partway; programmers (myself included) have this nasty habit of 
assuming, rightly or wrongly, that a failed function call won't have destroyed 
the parameters.

If you can't ensure you'll always complete, use locals.

-Owen

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

Re: [SQL] OUT parameter

[Owen Jacobson]

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 support pass-by-reference
> parameters and are unlikely to start doing so.  There isn't any way
> that you can affect locals of a calling procedure before you return.

Then I've misunderstood the semantics of OUT and more importantly INOUT 
parameters.  Thanks for the correction; I'm reading Daniel Caune's notes on the 
docs now.

-Owen

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

Re: [SQL] Bitfields always atomic? Other way to store attributes?

[Owen Jacobson]

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', depending on the
> original values of bf?

Not even.  Consider:

thread1=> update table set bf=bf | '01000'
thread3=> update table set bf=bf & '10111'
thread2=> update table set bf=bf | '1'

Now you get bf=10xxx.

They're thread safe in that all transformations will be applied as-if serially, 
so no bit sets or unsets will be lost, but you can't guarantee that another 
client won't interfere with the results.

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

Re: [SQL] pgcrypto-crypt

[Owen Jacobson]

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,
> > but there is nothing which i found to decrypt that same data,

The 'crypt' function in pgcrypto is analogous to the unix crypt(3) function, 
which is actually a hashing function and not an encryption function -- meaning 
you *can't* (realistically) decrypt it.  Use 'encrypt', as demonstrated below.


> INSERT INTO crypto VALUES (1,'test1',encrypt('daniel', 'fooz', 'aes'));
> INSERT INTO crypto VALUES (2,'test2',encrypt('struck', 'fooz', 'aes'));
> INSERT INTO crypto VALUES (3,'test3',encrypt('konz', 'fooz', 'aes'));
> 
> SELECT * FROM crypto;
> 
> SELECT *,decrypt(crypted_content, 'fooz', 'aes') FROM crypto;
> 
> SELECT *,decrypt(crypted_content, 'fooz', 'aes') FROM crypto WHERE
> decrypt(crypted_content, 'fooz', 'aes') = 'struck';
> 
> I could not test it, since I do not have pgcrypto installed.

This works perfectly.

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

Re: [SQL] Query from shell

[Owen Jacobson]

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't 'kill -9' the postmaster