I've got another great advice to use DELETE ... USING
instead of DELETE ... (SUB-SELECT),
so my procedure looks now like this (for archive):
create or replace function pref_delete_user(_id varchar,
_reason varchar) returns void as $BODY$
begin
c
Hello,
I'm trying to add 1 week "VIP-status" to all users in a table:
update pref_users set vip = max(vip, now()) + interval '1 week';
but max() doesn't work with timestamps.
Is there maybe still a way to solve it with a one-liner?
Thank you
Alex
--
Sent via pgsql-general mailing list (pg
Hello fellow postgres users,
in my game using PostgreSQL 8.4.9 players can
purchase a VIP ("very important person") status:
# \d pref_users;
Table "public.pref_users"
Column |Type | Modifiers
+-+---
Thank you Andreas - now that one case works ok,
On Thu, Dec 29, 2011 at 7:44 PM, Andreas Kretschmer
wrote:
> Try "if (not coalesce(has_vip, false)) then ..."
but the other case not:
# create or replace function pref_move_week(_from varchar,
_to varchar) returns void as $BODY$
Hello again, please 1 more question:
can I have a SELECT statement inside of an IF-conditional?
The doc
http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html
does not list such an example.
I'm asking, because I'd like to get rid of the has_vip
variable in my rewritten procedu
Awesome advices here.
Thank you and happy new year.
On Fri, Dec 30, 2011 at 2:07 PM, Alban Hertroys wrote:
> select 1 from pref_users where id=_from and vip > current_timestamp +
> interval '1 week';
>
> if not found then
> return;
> end if;
>
> "found" is a special pl/psql keyword that tel
Hello,
I have an 8.4.9 table, where users can
assess other users (i.e. "nice" vs. "not nice"):
# \d pref_rep
Table "public.pref_rep"
Column |Type | Modifiers
+-+
Hello again,
> On Fri, Dec 30, 2011 at 2:07 PM, Alban Hertroys wrote:
>> select 1 from pref_users where id=_from and vip > current_timestamp +
>> interval '1 week';
>>
>> if not found then
>> return;
>> end if;
>>
unfortunately I get the error in PostgreSQL 8.4.9:
# select pref_move_week('D
Is it because my procedure is declared as "void"?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello Ray and others,
On Sat, Dec 31, 2011 at 10:26 AM, Raymond O'Donnell wrote:
>> # select pref_move_week('DE16290', 'DE1');
>> ERROR: query has no destination for result data
>> HINT: If you want to discard the results of a SELECT, use PERFORM instead.
>> CONTEXT: PL/pgSQL function "pref_mo
Hello!
I'm using PostgreSQL 8.4.9 on CentOS 6.2 and with bash.
The following cronjob works well for me
(trying to send a mail to myself - for moderation):
6 6 * * * psql -c "select
'http://mysite/user.php?id=' ||id, about from pref_rep where
length(about) > 1 and la
Hello,
I have a table holding week numbers (as strings)
and user ids starting with OK, VK, FB, GG, MR, DE
(coming through diff. soc. networks to my site):
afarber@www:~> psql
psql (8.4.9)
Type "help" for help.
pref=> select * from pref_money;
id| money | yw
---
Thank you David -
On Wed, Feb 22, 2012 at 9:56 PM, David Johnston wrote:
> SELECT SUM(CASE WHEN id ~ '^FB' THEN 1 ELSE 0 END) AS fb_cnt, repeat for each
> known type (and I generally code one for unknown as well).
>
> Depending of your use case building out the non-column version and pushing it
Hello,
I run a small card game with PostgreSQL 8.4.11 on
CentOS 6 at https://apps.facebook.com/video-preferans/
List of databases
Name| Owner | Encoding | Collation |Ctype| Access privileges
---+--+--+-+
Hello,
On Thu, Apr 26, 2012 at 10:11 PM, Merlin Moncure wrote:
>> Does anybody have an advice on what data type
>> to use best for such a JSON "string"?
>>
>> Should I take varchar, text or bytea.
>>
>> And for the latter - how to handle it in Perl
>> if I currently use DBI and DBD::Pg?
>>
>> For
Hello,
in 8.4.9 is it please possible to add "on delete cascades" to
the both foreign keys in the following table w/o dropping the table?
# \d pref_scores
Table "public.pref_scores"
Column | Type | Modifiers
-+---+---
id | char
So it's not a problem to drop and recreate the FOREIGN KEYs?
And can I use START TRANSACTION while doing it?
On Fri, Apr 27, 2012 at 9:30 PM, Raymond O'Donnell wrote:
> On 27/04/2012 19:59, Alexander Farber wrote:
>> in 8.4.9 is it please possible to add "on delete
Thank you - this has worked perfectly
On Fri, Apr 27, 2012 at 10:18 PM, Richard Broersma
wrote:
> You could, but you don't need to since you can do all of this is one
> statement:
>
> ALTER TABLE public.pref_scores
> DROP CONSTRAINT pref_scores_gid_fkey,
> ADD CONSTRAINT pref_scores_gid_fkey
>
Hello,
In PostgreSQL 8.4.9 with pgbouncer 1.3.4 in
pool_mode = session (but some users connect to the
database directly) - when I login as "super user" with
psql -U postgres -W postgres
What is the command please to disconnect all
"normal users" and prevent them from connecting
again while I
Hello,
I've pg_dump'ed a database from PostgreSQL 8.2.6 / openSUSE 10.3
and now trying to load it as a "postgres" user at 8.1.1 / CentOS 5.2
and get numerous errors like:
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
psql:denkwerk.sql:1156: ERROR: syntax error at or near "OWNED" at charac
Thank you for replies, I've decided to upgrade by adding
http://yum.pgsqlrpms.org/ to the yum config at my CentOS server
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello,
On Wed, Dec 10, 2008 at 3:49 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> We run pgsql 8.3 on Centos 5.2 and are VERY happy with the PGDG rpms on it.
>
thanks for the confirmation Scott. I have installed PGDG with pgsql 8.2
at my CentOS 5.2 server and it seems to work now. I hope any upd
Hello,
I've just read about WAL and tried to set these 2 commands
for my test database (which is doing nothing 99% of time):
archive_command = 'cp -v %p /var/lib/pgsql/data/archive/%f'
archive_timeout = 300 # force a logfile segment switch after this
Dear PgSQL users,
is there please a way to display the executed SQL code?
I'm using phpBB 3.0.4 / PostgreSQL 8.2.6 / PHP 5.2.5 / OpenBSD 4.3
to develop a card game in Flash / C / Perl (at http://preferans.de )
and would like to log each game round by simply posting and replying
in one of the phpB
Hello,
I'm programming a small Flash game with PostgreSQL 8.1.4
and phpBB serving as backend. The data in the database is
in windows_1251 encoding. For my game I have to convert
it into utf8, and at the command prompt it seems to work (I recon
this because 7 win1251 chars seem to produce 14 utf8
I started to prepare a test case and realized I had a bug.
So convert() works for me, sorry for my previous message!
Regards
Alex
--
http://preferans.de
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Hello Alvaro,
On 12/24/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
Alexander Farber wrote:
> I started to prepare a test case and realized I had a bug.
> So convert() works for me, sorry for my previous message!
In any case, it's probably saner if you SET client_encoding at
Hello PostgreSQL users!
I have this data stored in WIN1251 encoding, which
is being fetched by a libpq application I'm developing:
phpbb=> show client_encoding;
-
WIN1251
(1 row)
phpbb=> \d phpbb_users;
username | character varying(25) | not null default '
And additional question please:
Can I still be sure that the data returned in the
convert(username using windows_1251_to_utf8)
column will be 0-terminated or should I fetch
the data length using PQgetlength and maintain
that value in my C-program?
Thank you
Alex
On 1/11/07, Alexander Farber
Hi Martijn,
On 1/11/07, Martijn van Oosterhout wrote:
If you need the string in UTF-8, why not just set the "client_encoding"
to "utf8" and then the server will only send you strings in utf8, not
conversion necessary.
actually you are right, because I need all my data in UTF8 anyway
(for a we
Hello,
I'm trying to write an Apache 1.3.29 module connecting to PostgreSQL 8.1.0
on OpenBSD -current and have few probably simple questions:
When an Apache child is initialized, I'd like to establish connection to
the database and to prepare 2 queries. And then later in the repeating
response ph
t the fastest method?)
Regards
Alex
On 1/28/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> Alexander Farber <[EMAIL PROTECTED]> writes:
> > 4) How do I set the last PQprepare argument, the const Oid *paramTypes?
>
> You'd need to look up the OIDs of the parameter types. I
Hello,
I've created a user and a database both wrongly named "phpbb".
After that I have renamed both to "punbb" using "ALTER DATABASE"
and "ALTER USER". Now everything works fine, except I always
have to specify the database when connecting (both using psql or
the PQconnectdb() from my C-program):
Hi,
On 1/30/06, Doug McNaught <[EMAIL PROTECTED]> wrote:
> Alexander Farber <[EMAIL PROTECTED]> writes:
> > h754814:afarber {103} psql -U punbb
> > psql: FATAL: database "phpbb" does not exist
>
> psql uses your Unix username as the default dat
Argh, I had "phpbb" in my env ( ~/.login) too:
setenv PGDATA /var/postgresql/data
setenv PGDATABASE phpbb
setenv PGHOST /var/www/tmp
setenv PGUSER phpbb
Now everything makes sense (I was thinking
about some kind of system table for default databases).
Sorry an
Hello,
these 2 statements work fine for me on the psql-prompt:
punbb=> select id, username, md5('deadbeef' || password) from users
where id = 7;
id | username | md5
+--+--
7 | Alex | b962415469222eeb31e739c3afbc8a4a
(1 row)
punbb=>
Hello,
in my application I'm trying to authenticate users
against a table called "users". The integer column
"id" should match, but also an md5 hash of the
"password" column (salted with a string) should match.
My authentication function (written in C, using libpq)
should return a "username" (is
from users where id = 4;
QUERY PLAN
--
Index Scan using users_pkey on users (cost=0.00..5.95 rows=1 width=156)
Index Cond: (id = 4)
(2 rows)
Regards
Alex
On 7/3/06, Martijn van Oosterhout wrote:
On Mon, Jul 03, 2006 at 03:13:15P
Hi Alban,
On 7/3/06, Alban Hertroys <[EMAIL PROTECTED]> wrote:
Alexander Farber wrote:
> punbb=> select username, md5('deadbeef' || password) from users where id
> = 4;
> username | md5
> --+--
> Vasja
Hello,
I have this strange problem that the following statement works:
phpbb=> select user_id, username from phpbb_users
phpbb-> where user_id in (select ban_userid from phpbb_banlist);
user_id | username
-+--
3 | La-Li
(1 row)
But the negative one returns nothing:
phpb
Hello,
thank you and sorry for asking a FAQ.
I've fixed my problem now by:
select user_id, username from phpbb_users where user_id not in
(select ban_userid from phpbb_banlist where ban_userid is not null);
but still your explanation feels illogical
to me even though I know you're right...
On
Hello,
I'm trying to compile a libpq program under Debian 3.1r2 with these
packages installed:
$ dpkg -l | grep postgres
ii postgresql 7.4.7-6sarge2 object-relational SQL database management sy
ii postgresql-cli 7.4.7-6sarge2 front-end programs for PostgreSQL
ii postgresql-con 7.4.7-6sa
bpq-program, to
detect an older PostgreSQL version? The headers
/usr/include/postgresql/libpq-fe.h and
/usr/include/postgresql/postgres_ext.h
don't have any PG_VERSION or similar define...
On 8/24/06, Alexander Farber <[EMAIL PROTECTED]> wrote:
I'm trying to compile a libpq progra
On 8/24/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Alexander Farber" <[EMAIL PROTECTED]> writes:
> Does anybody have an idea please, what could I be doing wrong?
Trying to use a subroutine added in 8.0 in 7.4.
Regards
Alex
--
http://preferans.de
Ok, I've upgraded to:
$ dpkg -l | grep postgres
ii postgresql-8.1 8.1.4-4bpo1object-relational SQL database,
version 8.1
ii postgresql-cli 8.1.4-4bpo1front-end programs for PostgreSQL 8.1
ii postgresql-cli 57bpo1 manager for multiple PostgreSQL
client versi
ii postgr
301 - 345 of 345 matches
Mail list logo
