This is not a question, but a solution. I just wanted to share this
with others on the list in case it saves you a few hours of searching...
I wanted to select several rows of data and have them returned in a
single record with the rows joined by a delimiter. Turns out this is
very easy to d
I want to select several rows of data and have them returned in a single
record with the rows joined by a delimiter. It would be great if this
could be done in a generic way possibly using the GROUP BY like this:
SELECT a.id, a.name, STR_SUM(b.name, ',') AS b_names
FROM a, b
WHERE a.i
Berend Tober wrote:
D. Dante Lorenso wrote:
I want to select several rows of data and have them returned in a
single record with the rows joined by a delimiter.
Review the User Comments at
"http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html";
for some ideas.
All,
I have a stored procedure that I use to manage a queue. I want to pop
an item off the queue to ensure that only one server is processing the
queue item, so inside PGSQL, use SELECT ... FOR UPDATE to lock the row.
Here's how I pop the queue item:
---
Erik Jones wrote:
On Aug 15, 2007, at 2:39 PM, [EMAIL PROTECTED] wrote:
Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item, so
inside PGSQL, use SELECT ... FOR UPDATE to lock the row...
When my server is
Douglas McNaught wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
How quickly after you update the row status are you comitting (and
releasing locks)?
I am calling a stored proc from PHP. Since I do not begin a
transaction, I assume that my call is automatically commit
Tom Lane wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
Douglas McNaught wrote:
How quickly after you update the row status are you comitting (and
releasing locks)?
I am calling a stored proc from PHP. Since I do not begin a
transaction, I assume that my call
Michael Glaesemann wrote:
On Aug 20, 2007, at 16:58 , Ed L. wrote:
You'd have to specify your table WITH OIDS anyway as they're no longer used by
default for table rows, so there's really nothing to be gained by using
oids.
How exactly can you get rid of OIDs when using a language like PHP?
Rainer Bauer wrote:
"D. Dante Lorenso" wrote:
Using a brain-dead sample table that looks like this:
CREATE table some_table (
col0 SERIAL,
col1 VARCHAR,
col2 VARCHAR
);
I want to do something like this:
I
Wei Weng wrote:
I want to implement something like the following:
CREATE OR REPLACE FUNCTION AddDays
You don't know how many seconds are in a day, so just add the days using
SQL.
RETURN time + (days || ' days')::INTERVAL;
You don't even need to make that a function, just do that you
Michael Glaesemann wrote:
On Aug 28, 2007, at 16:55 , D. Dante Lorenso wrote:
RETURN time + (days || ' days')::INTERVAL;
It's bad practice to concatenate like this. Use time + days * interval
'1 day' and be done with it.
Why? Is this functionality expected to b
Michael Glaesemann wrote:
On Aug 28, 2007, at 17:22 , D. Dante Lorenso wrote:
Michael Glaesemann wrote:
On Aug 28, 2007, at 16:55 , D. Dante Lorenso wrote:
RETURN time + (days || ' days')::INTERVAL;
It's bad practice to concatenate like this. Use time + days *
interval
Pavel Stehule wrote:
2007/9/12, Jay Dickon Glanville <[EMAIL PROTECTED]>:
- I write a function (it doesn't matter what language it's in:
PL/pgSQL, PL/Java, etc)
- I register that function as a "post-commit" callback function
- when a client commits a transaction, the function gets called, and
th
[EMAIL PROTECTED] wrote:
I want to know about the size of my database. For example, I want to know
how many Mb of data for current myDatabase database in a postgres server.
SELECT pg_size_pretty(pg_database_size(current_database()));
-- Dante
---(end of broadcast)-
Not really a question here, I just wanted to share my joy with the
group. I'm loving the new RETURNING clause in PostgreSQL. This is
really cool stuff ...
8<
CREATE OR REPLACE FUNCTION "public"."testfunc" () RETURNS bigint AS
$body$
DECLARE
my_var
All,
I'm trying to use the crypt and decrypt functions from contrib and have
installed them into my database. The definition for crypt seems to
require that I use BYTEA datatype to input the data I need to encrypt.
All of my data is either TEXT or VARCHAR, though and not BYTEA.
I was trying
Pat Maddox wrote:
I'd like to store some tree data in my database. I want to be able to
sort the data but maintain a tree structure
Is it possible to pull all the data like that with one query? How do
I need to structure the table, and what query do I have to run in
order to make it happen?
All,
I did this in my database:
CREATE CAST (VARCHAR AS BYTEA) WITHOUT FUNCTION;
But when I use pg_dump to dump the database and use pg_restore to bring
it back on a freshly created database, the CAST is the only part of the
restore which is missing.
I'm using PostgreSQL 8.2.4 for both th
All,
I want to use the ENCRYPT and DECRYPT functions from contrib, but they
require inputs of BYTEA.
My data is in VARCHAR and TEXT fields and when I try to use the contrib
functions, they complain about wrong datatypes. Is there a string
function or something that will take a VARCHAR or TE
Richard Huxton wrote:
D. Dante Lorenso wrote:
I want to use the ENCRYPT and DECRYPT functions from contrib, but they
require inputs of BYTEA.
My data is in VARCHAR and TEXT fields and when I try to use the
contrib functions, they complain about wrong datatypes. Is there a
string function
All,
Getting the first 4 characters from the begining of a string is easy enough:
SELECT SUBSTR('ABCDEFGHIJKLMNOP', 1, 4);
Returns 'ABCD'. But getting the last 4 characters appears to be a
little more work and is ugly:
SELECT SUBSTR('ABCDEFGHIJKLMNOP', LENGTH('ABCDEFGHIJKLMNOP') - 3, 4)
Rodrigo De León wrote:
On Dec 12, 2007 4:11 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote:
Is there an easy (preferred) method that I'm missing?
select substring('ABCDEFGHIJKLMNOP' from '$');
Thanks! Now, since I'd like to see a number in ther
All,
I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE
commands. Is this possible?
UPDATE invoice i
SET reserve_ts = NOW() + '1 hour'::timestamp
FROM account a
WHERE a.acct_id = i.acct_id
AND i.reserve_ts < NOW()
AND a.status = 'A'
AND i.is_paid IS FALSE
ORDER B
John D. Burger wrote:
D. Dante Lorenso wrote:
I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE
commands. Is this possible?
UPDATE invoice i
SET reserve_ts = NOW() + '1 hour'::timestamp
FROM account a
WHERE a.acct_id = i.acct_id
AND i.reserve_t
Uwe C. Schroeder wrote:
Ok, something I've been toying around with for a while.
Here's the scenario:
Imagine a blogging app.
I have a table for blogs with a blog_id (primary key)
and a table blog_comments also with a comment_id as primary key and a foreign
key holding the blog_id the post belong
Bill Moran wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> wrote:
All,
I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE
commands. Is this possible?
UPDATE invoice i
SET reserve_ts = NOW() + '1 hour'::timestamp
FROM account a
WHE
All,
I have a weird situation where my index IS used when I use a query that
hard-codes a value but it does NOT use the index when the value is
returned from a PGSQL function:
==
DOES NOT WORK
scan will evaluate the
comparison value only once, not once at each row, it is not valid to use
a VOLATILE function in an index scan condition.)"
-- Dante
D. Dante Lorenso wrote:
All,
I have a weird situation where my index IS used when I use a query that
hard-codes a value but it do
D. Dante Lorenso wrote:
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
I have a BIGINT column which might contain NULL values. I want to pass
a value to compare with that column in my WHERE clause. If the value
I'm comparing is 0, I want it to
All,
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
I have a BIGINT column which might contain NULL values. I want to pass
a value to compare with that column in my WHERE clause. If the value
I'm comparing is 0, I want it to match the NULL values. Here
Tom Lane wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
If the value I'm comparing is 0, I want it to match the NULL values.
[ raised eyebrow... ] Sir, you need to rethin
Tom Lane wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
regression=# select null IS NOT DISTINCT FROM 42;
?column?
--
f
(1 row)
regression=# select null IS NOT DISTINC
All,
I have a simple query:
SELECT tcq_id
FROM queue q
WHERE q.status = 'D'
ORDER BY tcq_id ASC
LIMIT 1;
What I'm trying to do is find all the items in the queue which have a
status of 'D' and then select the oldest item first. My data is very
skewed such that there are not many re
All,
I have 2 tables which both have triggers on them. When I delete a row
on table A, a cascading trigger ends up modifying rows in table B. The
modified rows in table B trigger an update on rows in table A which
happens to be the same row that I am trying to delete.
I don't get any error
All,
I have 2 tables which both have triggers on them. When I delete a row
on table A, a cascading trigger ends up modifying rows in table B. The
modified rows in table B trigger an update on rows in table A which
happens to be the same row that I am trying to delete.
I don't get any error
All,
I have 2 tables which both have triggers on them. When I delete a row
on table A, a cascading trigger ends up modifying rows in table B. The
modified rows in table B trigger an update on rows in table A which
happens to be the same row that I am trying to delete.
I don't get any error
All,
In the past I have used foreign keys to lookup tables for small lists of
values that I now think ENUM could do the job of. I was hoping that by
using ENUM, I would avoid having to do joins in my queries, and that I'd
be making the data more robust and faster.
I used to have a table for
Andreas 'ads' Scherbaum wrote:
On Tue, 22 Apr 2008 20:58:30 +0800 Craig Ringer wrote:
D. Dante Lorenso wrote:
The problem is that once I create a column in my account table that
uses this 'account_type' datatype, I can't seem to change or add to it
any more. I want t
Scott Marlowe wrote:
On Tue, Apr 22, 2008 at 2:45 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote:
So, the advice here is "don't use ENUM"?
I was really hoping that it would be more efficient to not have to do all
the foreign keys and joins for tables that may have 4-5 en
Andrew Sullivan wrote:
On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote:
Absolutely true. Which is odd, because this example is trotted out
whenever there's a thread about ENUMs.
I don't think it's odd at all. In my view, the people who think enums are a
good datatype for databases are ex
Alvaro Herrera wrote:
D. Dante Lorenso wrote:
Or, here's another way to look at it ... make it easier to modify ENUM
datatypes because we all know that you will eventually need that feature
whether you males, females, and unknowns think so or not.
Agreed. Let's keep in min
Tino Wildenhain wrote:
D. Dante Lorenso wrote:
Alvaro Herrera wrote:
D. Dante Lorenso wrote:
Or, here's another way to look at it ... make it easier to modify
ENUM datatypes because we all know that you will eventually need
that feature whether you males, females, and unknowns think
Instead of doing this:
CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF record AS
$body$
...
$body$
LANGUAGE 'plpgsql' VOLATILE;
I'd like to be able to do this:
CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF (col1name BIGIN
Instead of doing this:
CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF record AS
$body$
...
$body$
LANGUAGE 'plpgsql' VOLATILE;
I'd like to be able to do this:
CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF (col1name BIGIN
Pavel Stehule wrote:
Hello
2008/5/10 D. Dante Lorenso <[EMAIL PROTECTED]>:
Instead of doing this:
CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF record AS
$body$
...
$body$
LANGUAGE 'plpgsql' VOLATILE;
I'd like to be able
Tom Lane wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
I'd like to be able to do this:
CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS
You realize of course that you can do this *t
I'd like to find a timestamp function that WILL change within a transaction.
This function will return to me a 15 digit BIGINT number in base10:
SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) * 10)::bigint;
The problem is that NOW() does not change within a transaction and so I
keep getting the
I'd like to convert very large unsigned numbers (ala bigint) to a text
string using base62. I created this PL/PERL function to do the trick:
CREATE OR REPLACE FUNCTION "public"."ls_crypt_convert_base" (in_value
text, in_base integer) RETURNS text AS
$body$
my ($value, $
Stuart Cooper wrote:
I'd like to convert very large unsigned numbers (ala bigint) to a text
string using base62. I created this PL/PERL function to do the trick:
base 62 is cruel and unusual punishment. Introduce two more printing
characters to your set a..z, A..Z, 0..9 such as "_" and "!" and
I have several records in my database which have encrypted fields. I
want to find all the ones that match a certain format but do NOT match
another.
My problem is that the 'cc_encrypt' function is being executed for every
matching row in the table instead of just once. The function was
defi
Tom Lane wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
This format causes the function to execute too many times:
SELECT COUNT(*) AS result
FROM credit_card
WHERE card_number_enc = cc_encrypt('4111---', 'pwd')
Tom Lane wrote:
"D. Dante Lorenso" <[EMAIL PROTECTED]> writes:
So, that not being the problem, any ideas? Is it an 8.2.4 thing?
I can't reproduce any such problem in 8.2 branch tip, and a desultory
scan of the CVS history back to 8.2.4 doesn't turn up any obviousl
All,
I used to rave about how great the EMS PostgreSQL tools were, but lately
I've been disappointed by the lack of support for their Database
Comparer 2007 for PostgreSQL.
I need a tool that will compare 2 database schemas and allow me to
migrate changes from one database to another. What
All,
I used to rave about how great the EMS PostgreSQL tools were, but lately
I've been disappointed by the lack of support for their Database
Comparer 2007 for PostgreSQL.
I need a tool that will compare 2 database schemas and allow me to
migrate changes from one database to another. What
All,
I have 2 tables with a lot of columns with similar names. I'd like to
join both tables and include all columns from each without naming
collisions. I can use the 'AS' to rename a single column, but is there
a way to do the rename in bulk by just appending a prefix or suffix to
the colu
Scott Marlowe wrote:
On Thu, Aug 28, 2008 at 3:38 PM, Bill <[EMAIL PROTECTED]> wrote:
I am new to PostgreSQL but it seems to me that lastval() will only work if
the insert does not produce side effects that call nextval(). Consider the
case where a row is inserted into a table that has an after
Matthew Wilson wrote:
On Tue 02 Sep 2008 04:40:55 PM EDT, Scott Marlowe wrote:
On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson <[EMAIL PROTECTED]> wrote:
On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
If the two subordinate tables ALWAYS have to point to the same place,
why two tables
a.join_column = b.join_column);
Is this how to do it, or is there a simpler syntax I'm missing? What's
the formal terminology for what I'm doing here? Is there a way to
specify a FULL OUTER JOIN without naming the join column since I don't
need one?
-- Dante
--
D
27;plperl' VOLATILE RETURNS NULL ON NULL INPUT;
--
Perhaps you can use PL/PERL and a function like these to modify "global"
variables that you can increment as you do your select. Something like:
SELECT global_var_set(0);
SELECT global_var_inc() AS row_counte
or
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant
--
--
D. Dante Lorenso
[EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
All,
I can find the names of all tables in the database with this query:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_name ASC;
Then, in code, I can loop through all t
] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of D. Dante Lorenso
Sent: Monday, February 26, 2007 2:20 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Most efficient report of number of records in all
tables?
All,
I can find the names of all tables in the database with this query:
SE
Bruno Wolff III wrote:
On Sat, Dec 13, 2003 at 02:20:15 -0600,
"D. Dante Lorenso" <[EMAIL PROTECTED]> wrote:
I'd like to run a clean up command on my tables to
eliminate rows that I'm no longer using in the database.
I want to do something like this:
DEL
Martijn van Oosterhout wrote:
On Sat, Dec 13, 2003 at 09:48:16PM -0600, D. Dante Lorenso wrote:
This is something very ugly indeed and is what I'll have to resort to unless
I can find something cleaner. Ideally, I would be able to run this cleanup
on a subset of the table data after an i
Joe Conway wrote:
D. Dante Lorenso wrote:
I have a simple table that I'd like to query to pull
out a heirarchy from a tree relationship. What is the
best way to do this without a 'CONNECT BY' clause like
Oracle has?
See connectby() in contrib/tablefunc.
Yep. That's wh
Marc G. Fournier wrote:
On Mon, 29 Dec 2003, D. Dante Lorenso wrote:
Trying to use the 'search' in the docs section of PostgreSQL.org
is extremely SLOW. Considering this is a website for a database
and databases are supposed to be good for indexing content, I'd
expect a much fas
Sergey Olefir wrote:
So the logical choice would be int8, right? Unfortunately quite wrong.
Statement of the form: "SELECT * FROM table WHERE id=1"
will never use index for id (assumming id is int8) since '1' is of type
int4. This is confirmed both by documentation and SQL EXPLAIN (after set
enabl
I just ran into a dump/restore problem with a bigserial column
on a renamed table.
BIGSERIAL columns when created will automagically create
the sequence also. The sequence name is derived from
the table name and column name.
I had a table named 'audio_usage' and defined a column like this:
a
Tom Lane wrote:
Adding an MD5 hash contributes *absolutely zero*, except waste of space,
to any attempt to make a GUID. The hash will add no uniqueness that was
not there before.
The cool thing about a 'GUID' (or in my example a hashed sequence number
[sure
toss in some entropy if you want it]
First I created a function that selected the next available pin
code from a table of pre-defined pin codes:
CREATE FUNCTION "public"."get_next_pin_code" () RETURNS varchar AS'
DECLARE
my_pin_code VARCHAR;
BEGIN
...
/* this is the pincode we just fetched */
RETUR
they can try to look up information on other customers by doing:
http://domain.com/application/load_record.html?customer_id=12346
http://domain.com/application/load_record.html?customer_id=12344
...basically walking the sequence. Sure, you will protect against this
to happen. NOW
71 matches
Mail list logo
