Hello,
I work on several small Java projects (using Maven+NetBeans) and store them
in a public git repository.
I would like to use PostgreSQL JDBC in some of the projects, but don't want
to make the connection credentials of my database public.
Surely there are other developers out there, who ha
Dear PostgreSQL users,
I have a stored procedure defined as:
CREATE OR REPLACE FUNCTION words_merge_users(
IN in_users jsonb,
IN in_ip inet,
OUT out_uid integer)
RETURNS integer AS
$func$
...
$func$ LANGUAGE plpgsql;
Which I can successfully call at ps
at 3:45 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Jun 14, 2016 at 9:33 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>>
>> CREATE OR REPLACE FUNCTION words_merge_users(
>> IN in_users jsonb,
>>
Good afternoon,
at PostgreSQL 9.5.3 I have a stored function (full source code below)
returning void, which I successfully call with PHP:
function skipGame($dbh, $uid, $gid) {
$sth = $dbh->prepare('SELECT words_skip_game(?, ?)');
$sth->execute(array($uid, $gid));
}
Now I am tryin
Actually I meant the doc
https://jdbc.postgresql.org/documentation/94/update.html
Hello Jan,
On Wed, Jun 15, 2016 at 4:17 PM, Jan de Visser wrote:
> On Wednesday, June 15, 2016 3:56:07 PM EDT Alexander Farber wrote:
> > Now I am trying to call the same function through JDBC driver
> 9.4.1208.jre7:
> >
> > private static fi
I only understand a quarter of what you guys are writing,
but to me the JDBC driver throwing SQLException
"A result was returned when none was expected"
when my stored function is declared as "void" with
CREATE OR REPLACE FUNCTION words_skip_game(
IN in_uid integer,
IN in_gid integ
Hello fellow PostgreSQL users,
does anybody else observe the problem, that calling lower() method on UTF8
cyrillic strings works on Mac and Linux for version 9.5.3, but fails on
Windows 7 / 64 bit (I am using the unzippable version w/o installer)?
I am probably not providing enough information he
Here the Windows-log excerpt (the 5 cyrillic letters stay uppercased):
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: statement: SET client_encoding = 'UTF8';
LOG: execute : SELECT out_gid AS gid FROM words_play_game($1, $2,
$3::jsonb)
DETAIL: param
More info:
# \l+
Name| Owner | Encoding | Collate | Ctype |
---+-+--+-+---+
postgres | user1 | UTF8 | C | C |
template0 | user1 | UTF8 | C | C |
| | | | |
template1 | user1
Thank you for the replies.
On CentOS 7 Linux with pgdg 9.5.3 the lower() method works and I have:
# \l
List of databases
Name| Owner | Encoding | Collate |Ctype|
---+--+--+-+-+
postgres | po
Hello Charles, unfortunately on Windows 7 this fails:
psql (9.5.3)
Type "help" for help.
# select lower(('И'::text collate "en_US")) ;
ERROR: collation "en_US" for encoding "UTF8" does not exist
LINE 1: select lower(('?'::text collate "en_US")) ;
^
By the way I th
Good evening,
I wonder, why the following returns NULL and not 0 in 9.5.3?
# select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1);
array_length
--
(1 row)
# select array_length(array_remove(ARRAY[3,3,3],3), 1);
array_length
--
(1 row)
In a code for a wor
Thank you, so should I maybe switch to cardinality then?
Good afternoon,
In PostgreSQL 9.5.3 I have created a function (full source code at the
bottom), which goes through an 15 x 15 varchar array and collects words
played horizontally and vertically.
I have declared the function as:
CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid inte
Thank you -
On Wed, Aug 10, 2016 at 3:18 PM, Charles Clavadetscher <
clavadetsc...@swisspug.org> wrote:
>
> #variable_conflict [use_column|use_variable] before BEGIN:
>
> - http://dba.stackexchange.com/questions/105831/naming-
> conflict-between-function-parameter-and-result-of-join-with-using-cl
Thank you for confirming, I supposed I have to use RETURN QUERY and now it
works.
There is still 1 open question -
In my custom function:
CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb)
RETURNS TABLE(word varchar, score integer) AS
$func$
I iterate through tiles passed as last argument and
Thank you Adrian and others -
I am trying to replace INSERT into temp table in my custom function by
RETURN NEXT, but get an error:
CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb)
RETURNS TABLE(word varchar, sc
Both variants have worked for me, thanks
I am using 9.5.3 on CentOS 7 (my "production server" and Win 7, Mac (my
"dev machines") :)
Where does RETURN NEXT EXPRESSION work, on 9.6?
No, actually both variants work for me right now at 9.5.3 on Mac -
On Wed, Aug 10, 2016 at 8:31 PM, Adrian Klaver
wrote:
>
>> Given what you are doing, RETURN TABLE it will not work there for the
> same reason it does not work in 9.5:
>
> https://www.postgresql.org/docs/9.6/static/plpgsql-contro
Good morning,
why does this syntax fail in 9.5.3 please?
I am trying to call 2 custom functions from a third one with:
CREATE OR REPLACE FUNCTION play_game(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb,
OUT out_gid integer)
RETURNS integer AS
$f
Francisco, thanks, but -
On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte
wrote:
>
> https://www.postgresql.org/docs/9.5/static/plpgsql-
> statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
>
>
but the custom function I am trying to call (from another function) does
not return one row, but several
Thank you, I have rewritten it into:
BEGIN
PERFORM check_positions(in_uid, in_gid, in_tiles);
CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT
DROP;
INSERT INTO _words
SELECT
out_word AS word,
max(out_score) AS score
Thank you Craig, this has worked in my custom function too:
BEGIN
PERFORM check_positions(in_uid, in_gid, in_tiles);
CREATE TEMP TABLE _words ON COMMIT DROP AS
SELECT
out_word AS word,
max(out_score) AS score
FROM check_words(in_uid,
Good evening,
with PostgreSQL 9.5.3 I am using the following table to store 2-player
games:
DROP TABLE IF EXISTS words_games;
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT
Hi Igor,
On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman wrote:
> mailto:pgsql-general-ow...@postgresql.org] *On Behalf Of *Alexander Farber
>
>
https://gist.github.com/afarber/c40b9fc5447335db7d24
>
>
>
> Certain MOVE exists only within particular GAME: no GAME -> no M
Hello again,
I have went the ALTER TABLE route to add my 2 "cyclic" FKs:
https://gist.github.com/afarber/c40b9fc5447335db7d24
And now I have these 2 tables in my 9.5.3 database:
#TABLE words_moves;
mid | action | gid | uid |played |
Thank you and apologies for the misformated mail - I kept fixing it for
many minutes, but once I hit the "Send" button in Gmail it went south again.
Good afternon,
in 9.5.3 I have defined the following custom function:
CREATE OR REPLACE FUNCTION words_select_games(IN in_uid integer)
RETURNS SETOF RECORD AS
$func$
BEGIN
RETURN QUERY SELECT
g.gid AS gid,
EXTRACT(EPOCH FROM g.created)::int AS creat
Thank you, I was just wondering if there is a simpler way... but ok
On Fri, Aug 26, 2016 at 5:29 PM, Tom Lane wrote:
>
> I think you are looking for the RETURNS TABLE syntax.
>
Hello,
what do you use to retrieve a jsonb column using JDBC?
I have tried
Object last_tiles = rs.getObject("last_tiles");
and the resulting Object seems to be a String.
Then I have called (using Jetty JSON class here):
Object last_tiles = JSON.parse(rs.getString("last_tiles"
Thank you for your comments!
I have switched to SQL function now
(I didn't realize it is better performancewise) -
CREATE OR REPLACE FUNCTION words_select_games(IN in_uid integer)
RETURNS TABLE(
out_gid integer,
out_created integer,
out_playe
On Mon, Aug 29, 2016 at 7:50 PM, Merlin Moncure wrote:
> On Sat, Aug 27, 2016 at 5:39 AM, Alexander Farber
> wrote:
> >
> > List last_tiles = (List) JSON.parse(rs.getString("last_
> tiles"));
> >
> > has not work for me even though the strin
Good evening,
please help me to figure out, why doesn't this simple test function return
a row with 42, NULL values:
CREATE OR REPLACE FUNCTION words_merge_users_2(
IN in_users jsonb,
IN in_ip inet
) RETURNS TABLE (
out_uid integer,
If I'd like to always return exactly 1 row -
why wouldn't just RETURN work?
(That's what I kept trying)
On Fri, Sep 2, 2016 at 7:27 PM, Pavel Stehule
wrote:
>
>
> 2016-09-02 19:21 GMT+02:00 Alexander Farber :
>
>> why doesn't this simple test func
Good afternoon,
for a 2-player game I store moves in the following 9.5.4 table:
CREATE TYPE words_action AS ENUM ('play', 'skip', 'swap', 'resign');
CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_game
Thank you, Sandor -
On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku wrote:
>
> Get the last 6 record and
>
> 1. ... action='SKIP' as isskip ... then you can group on and count the
> skip moves. If there is 6 of them the game ends.
>
> 2. ... sum(case when action='SKIP' then 1 else 0 end) ... If th
me(integer,integer) line 27 at SQL
statement
On Tue, Sep 6, 2016 at 2:30 PM, Sándor Daku wrote:
> On 6 September 2016 at 14:23, Alexander Farber > wrote:
>
>>
>> On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku
>> wrote:
>>
>>>
>>> Get the last 6 re
Hello Charles and other, please excuse my stupidity, but -
On Tue, Sep 6, 2016 at 2:52 PM, Charles Clavadetscher <
clavadetsc...@swisspug.org> wrote:
>
> You must group by played, as the message suggests. You are implicitly
> selecting the column through order by, although you don't have it in th
Also tried the second suggestion:
words=> select count(action='skip') from words_moves where gid=3 group by
played order by played desc limit 6;
count
---
1
1
1
1
1
1
(6 rows)
Sandor, this has worked, thank you -
On Tue, Sep 6, 2016 at 3:35 PM, Sándor Daku wrote:
>
> Of course you need the played field you relied on it in the order by
> clause. You can use the result of a select in a from clause of another
> select.
>
> SELECT SUM(skips) from (SELECT CASE WHEN action=
Good afternoon,
when trying to create a custom function to temporary ban a user:
CREATE OR REPLACE FUNCTION words_ban_user(
IN in_uid integer,
IN in_until varchar,-- '1 week' OR '1 month' OR '1 year'
IN in_reason varchar)
RETURNS void AS
$func$
BEGIN
Hello Rob,
On Wed, Sep 7, 2016 at 3:24 PM, rob stone wrote:
>
> I think the interval values need to be quoted.
> In any event I always use the P or T syntax. 'P1D' means add one day,
>
unfortunately, I can not call INTERVAL 'in_until', that wouldn't work.
Also 'P1D' vs. '1 day' seems to be jus
Thank you, this works well now and comments about IN is appreciated too :-)
Good afternoon,
I have a question please.
In one table I store user ids and their IP addresses -
CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL
);
And in another table I keep 2-player games and timestamps of last moves
(NULL i
Thank you Brian and others, but -
On Fri, Sep 9, 2016 at 5:22 PM, Brian Dunavant wrote:
> I'm making the assumption that you only have one ip/user in words_users.
>
> with lockrow as (
>SELECT g.gid, u.ip
>FROM words_games g join words_users u
> ON (g.player1 = u.uid)
> WHERE
Good evening!
For a 2-player game I am trying to create a custom SQL function, which
stores a new message (if not empty) into words_chat table and then return
all messages from that table for a given game:
CREATE OR REPLACE FUNCTION words_get_chat(
in_uid integer,
Thank you Vik and others -
On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing wrote:
> On 09/26/2016 08:22 PM, Alexander Farber wrote:
> >
> > CREATE OR REPLACE FUNCTION words_get_chat(
> > in_uid integer,
> > in_gid integer,
> &g
Sorry, I've just realized you did that already.
And the WITH cte AS part is optional in this case...
Thank you
On Mon, Sep 26, 2016 at 9:12 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:
> Thank you Vik and others -
>
> On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearin
Maybe your are after IS NOT DISTINCT FROM NULL
https://www.postgresql.org/docs/current/static/functions-comparison.html
Good evening,
I have a question please on which kind of statement to use -
In a table I store user info coming from social networks (Facebook,
Twitter, ...):
CREATE TABLE words_social (
sid varchar(255) NOT NULL,
social integer NOT NULL CHECK (0 <= social AND social <= 6),
login lately and would expect her Facebook-photo
to be seen (the record with the highest "stamp" value).
Regards
Alex
On Wed, Oct 19, 2016 at 8:51 PM, Adrian Klaver
wrote:
> On 10/19/2016 11:35 AM, Alexander Farber wrote:
>
>> In a table I store user info coming from social ne
Please let me rephrase my question so that it is better understandable -
In PostgreSQL 9.5.3 I keep player infos from various social networks:
# TABLE words_social;
sid | social | female | given | family | photo | place | stamp|
uid
---+++-++---+
Should I use LATERAL JOIN?
On Fri, Oct 21, 2016 at 3:24 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:
>
> In PostgreSQL 9.5.3 I keep player infos from various social networks:
>
> # TABLE words_social;
> sid | social | female | given | family | photo | place
Hello,
is it please possible to rewrite the SQL query
SELECT DISTINCT ON (uid)
uid,
female,
given,
photo,
place
FROM words_social
WHERE uid IN (SELECT player1 FROM games)
OR uid IN (SELECT player2 FROM
Thank you for the advices and I have also got few answers at
http://stackoverflow.com/questions/40304011/where-in-condition-and-multiple-columns-in-subquery
Regards
Alex
Good afternoon,
is it please posible to optimize the following SQL query with numerous CASE
statements (on same condition!) without switching to PL/pgSQL?
SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int,
EXTRACT(EPOCH FROM g.finished)::int,
g.letters,
Hi Geoff,
On Mon, Oct 31, 2016 at 4:21 PM, Geoff Winkless wrote:
>
> You could break the game table apart into game and gameplayer.
>
> That's more "normal" and fits much more nicely, IMO, and you could
> then resolve the CASE by using joins between game and (twice)
> gameplayer:
>
> SELECT ...
Ah, thanks - I've got that with JOINing via CASE now...
On Mon, Oct 31, 2016 at 5:50 PM, Geoff Winkless wrote:
> especially since (as I said in the last paragraph of my email), you
> can remove all of the CASEs except the hand/score ones by just JOINing
> the other tables via a CASE anyway.
>
Hello,
why does this fail in PostgreSQL 9.5 please?
Here is my custom SQL function :
CREATE OR REPLACE FUNCTION words_unban_user(
in_uid integer)
RETURNS integer AS
$func$
UPDATE words_users SET
banned_until = null,
banned_reason = null
WHERE uid =
Thank you, Rob -
On Fri, Dec 2, 2016 at 11:12 AM, Rob Sargent wrote:
>
> > On Dec 2, 2016, at 2:52 AM, Alexander Farber
> wrote:
> >
> > CREATE OR REPLACE FUNCTION words_unban_user(
> > in_uid integer)
> > RETURNS integer AS
> >
Good evening,
in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous
commands in a stored function?
I have a stored function (the code is at the bottom), which takes a JSON
array of objects as arguments.
First it prepares some data and then loops through the JSON array and
upserts
Good morning,
with the following stored function I would like to validate user data:
CREATE OR REPLACE FUNCTION check_user(
in_social integer,
in_sid varchar(255),
in_auth varchar(32))
RETURNS boolean AS
$func$
Hi Laurenz,
how to set such a savepoint inside of a stored function?
Can I call "START TRANSACTION", and then at some point later in the same
stored function call RAISE EXCEPTION?
Regargs
Alex
On Wed, Mar 2, 2016 at 10:37 AM, Albe Laurenz
wrote:
> Andreas Kretschmer wrote:
&
Thank you, this is very helpful, just 1 little question:
Why do you write just EXCEPTION?
Shouldn't it be RAISE EXCEPTION?
Regards
Alex
On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz
wrote:
> Alexander Farber wrote:
> > how to set such a savepoint inside of a stored function?
/16, Alexander Farber wrote:
> >
> > CREATE OR REPLACE FUNCTION check_user(
> > in_social integer,
> > in_sid varchar(255),
> > in_auth varchar(32))
> > RETURNS boolean AS
> >
Thank you all for the valuable replies.
I've also got suggestions to use IS NOT DISTINCT FROM or STRICT at
http://stackoverflow.com/questions/35742865/how-to-ensure-that-a-stored-function-always-returns-true-or-false
but the former has the edge case of NULL=NULL returning TRUE
and with the latte
Hello,
for a Scrabble-like word game using PostgreSQL 9.5 as backend
I am trying to add CHECK constraints to the VARCHAR arrays:
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE
Okay, let's call it <~
:-)
On Fri, Mar 4, 2016 at 4:53 PM, Tom Lane wrote:
> Alexander Farber writes:
> > I am trying to add CHECK constraints to the VARCHAR arrays:
>
> > hand1 varchar[7] NOT NULL CHECK (ALL(hand1) ~ '^[*A-Z]$'),
>
> > bu
Hello,
could someone please recommend the most efficient way subtracting elements
of one array from the other in PostgreSQL 9.5?
Should I create a new array or can I work on the existing one (and if the
latter - will FOREACH work well when elements are removed "from under its
feet"?).
Both array
Thank you, but shouldn't I better use FOREACH for my task?
On Sun, Mar 6, 2016 at 6:34 PM, bricklen wrote:
> On Sun, Mar 6, 2016 at 9:22 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>> could someone please recommend the most efficient way subtractin
Hello again,
I could not find a solution neither
at http://postgres.cz/wiki/Array_based_functions
nor at https://github.com/JDBurnZ/postgresql-anyarray
So I have written my own stored function
to subtract one non-unique array from another
(like swapping tiles in a word game):
DROP FUNCTION IF E
27;,'C'], ARRAY['A','B']);
NOTICE: remove_ignore = {f,f}
words_array_subtract
--
{A,B,C}
(1 row)
On Tue, Mar 8, 2016 at 2:28 PM, Alexander Farber wrote:
> I could not find a solution neither
> at http://postgres.cz/wiki/Array_based_functions
Hello fellow PostgreSQL users,
what is please the most efficient way to delete a slice from the start of a
longer array (after I have copied it to another array)?
Do I really have to copy a large slice of the array to itself, like in the
last line here:
pile_array := pile_array || swap_a
Hello Mike,
On Wed, Mar 9, 2016 at 2:42 PM, Mike Sofen wrote:
>
> Have you considered a normal (relational), non-array-based data model for
this app (2 or 3 tables in a 1:M/M:M) instead of the single table model
you’ve shown? That would then allow you to use normal sql set-based
operations that
Hello Chris,
On Wed, Mar 9, 2016 at 4:42 PM, Chris Travers
wrote:
>
>
> On Wed, Mar 9, 2016 at 1:10 PM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>>
>> what is please the most efficient way to delete a slice from the start of
>> a longer a
Hello!
I am trying to move from (successfully) using VM Fusion with CentOS Linux
image on Mac OSX "El Capitan" for web development to Homebrew:
brew update
brew install postgres
brew tap homebrew/services
This resulted in some files installed to
/usr/local/Cellar/postgresql/9.5.1
Hello fellow pgsql users,
I am programming a word game backend in PL/pgSQL and have already reached a
point, where (too) many stored functions are declared in a single file
words.sql:
# SELECT proname || '(' || oidvectortypes(proargtypes) || ')'
FROM pg_proc INNER JOIN pg_namespace ns ON
Thanks for your replies.
While I use "\i" regularly I just didn't realize it would be suitable here
as well :-)
Regards
Alex
Hello,
I'm trying to create a table, where md5 strings will serve as primary keys.
So I'd like to add a constraing that the key length should be 32 chars long
(and contain [a-fA-F0-9] only):
create table gps (
id varchar(32) primary key CONSTRAINT char_length(id)==32,
stamp timestamp DEFAULT curr
snake=> create table gps (
id varchar(32) primary key CONSTRAINT id_length char_length(id)=32,
stamp timestamp DEFAULT current_timestamp,
pos point);
ERROR: syntax error at or near "char_length"
LINE 2: id varchar(32) primary key CONSTRAINT id_length char_length(...
Thank you for your advices.
I actually would like to store GPS coordinates, but anonymously,
so I was going to save md5(my_secret+IMEI) coming from a mobile...
I have to lookup if uuid is supported there
Regards
Alex
On Sat, Oct 16, 2010 at 11:08 PM, Rob Sargent wrote:
> Merlin Moncure wrote:
Hello, really good advices here! But -
On Sun, Oct 17, 2010 at 2:37 AM, Rob Sargent wrote:
> I just read the "anonymously" part, so I take it you have ruled out
> recording the given coordinate components directly, in multiple columns
> presumably? Otherwise it seems you could then do a) a compo
Hello again,
I have 1 more question please:
how do you select the "x" and "y" parts of a point data type?
Can't find it in
http://www.postgresql.org/docs/8.4/interactive/functions-geometry.html
For example, I have this table with a "pos" column:
snake=> \d gps
Table "public.g
Hello Postgres users,
to mimic the MySQL-REPLACE statement I need
to try to UPDATE a record and if that fails - INSERT it.
But how can I detect that the UPDATE has failed in my SQL procedure?
begin transaction;
create table pref_users (
id varchar(32) primary key
Thanks Pavel, but I have an SQL procedure and not plpgsql?
On Sun, Oct 31, 2010 at 9:34 AM, Pavel Stehule wrote:
>> But how can I detect that the UPDATE has failed in my SQL procedure?
>>
>
> see:
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
> near to end of p
Thanks for all the comments.
Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are
functions atomic?
Regards
Alex
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
And would a pure SQL-function solution to call
an INSERT followed by an UPDATE in its body
and ignoring the error? (don't know how ignore it
best though, so that I don't ignore other critical errors)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your sub
[corrected typo, sorry]
And wouldn't a pure SQL-function solution be:
to call an INSERT followed by an UPDATE in its body
and ignoring the error? (don't know how ignore that error
best though, so that I don't ignore other critical errors)
--
Sent via pgsql-general mailing list (pgsql-general@pos
I've created a function now (below) and can call it as well,
but how can I see it at the psql prompt? Is there a \d command
for that or should I dump the database to see my declarations?
And is my function atomic? I.e. can't it happen, that FOUND
is not true, but then another session calls a INSER
Hello,
I have a card game for each I'd like to introduce weekly tournaments.
I'm going to save the score (virtual money) won by each player into:
create table pref_money (
id varchar(32) references pref_users,
yw char(7) default to_char(current_timestamp, '
rintError => 1,
RaiseError => 1,
FetchHashKeyName => 'NAME_lc',
pg_enable_utf8 => 1
}, {pg_async => PG_ASYNC});
#$dbh->pg_result;
my $sth = $dbh->prepare_cache
Hello Andy and others,
On Mon, Nov 1, 2010 at 3:33 PM, Andy Colson wrote:
> On 11/1/2010 4:29 AM, Alexander Farber wrote:
>> I have a small multiplayer game, a non-forking daemon
>> reading/writing to sockets and running in a IO::Poll loop.
>>
>> I.e. I would like t
Hello,
sorry for the stupid question, but why has the week number changed
from 44 to 45 this night? It is Friday, 2010-11-05 01:10, but I get now:
pref=> SELECT to_char(current_timestamp, '-WW');
to_char
-
2010-45
(1 row)
pref=> SELECT CURRENT_DATE;
date
2010-11-0
On Fri, Nov 5, 2010 at 1:21 AM, Tom Lane wrote:
> WW is defined as starting the first week on the first day of the year.
> 2010 started on a Friday so the week number increments on Fridays.
>
> There are some other format codes with different behavior ...
Thank you, that is what I thought
But is
I will try -IW
On Fri, Nov 5, 2010 at 1:28 AM, Alexander Farber
wrote:
> But is there a format code for a week starting on Sunday or Monday?
>
> Sorry, I can't find it at
> http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html
--
Sent via pgsql-gen
Hello,
I have 2 tables with user infos (please see \d output at the bottom)
and would like to find their rank depending on their "money".
When I select all records, the rank() works fine:
pref=> select u.first_name,
u.city,
m.money,
rank() over (order by money desc)
from pref_users u, pref_money
Sorry Rob, but it fails with:
pref=> select * from (
select u.id,
u.first_name,
u.city,
m.money,
rank() over (order by money desc)
from pref_users u, pref_money m where
m.yw=to_char(current_timestamp, '-IW')
) all_ranks ar
where ar.id='OK
And:
pref=> select * from (
select u.id,
u.first_name,
u.city,
m.money,
rank() over (order by money desc)
from pref_users u, pref_money m where
m.yw=to_char(current_timestamp, '-IW')
) ar
where ar.id='OK138239987797';
works, but deliver
1 - 100 of 345 matches
Mail list logo