Re: [GENERAL] Comparing epoch to timestamp

# EXPLAIN SELECT ARRAY_AGG(hashed) FROM words_nouns WHERE added > to_timestamp(0) UNION SELECT ARRAY_AGG(hashed) FROM words_verbs WHERE added > to_timestamp(0)

Re: [GENERAL] Comparing epoch to timestamp

Sorry, I probably had to call: # EXPLAIN SELECT ARRAY_AGG(hashed) FROM words_nouns WHERE EXTRACT(EPOCH FROM added) > 0 UNION SELECT ARRAY_AGG(hashed) FROM words_verbs WHERE EXTRACT(EPOCH FROM

[GENERAL] Comparing epoch to timestamp

Hello, in PostgreSQL 9.5 I have a table with 67000 records: # \d words_nouns Table "public.words_nouns" Column | Type | Modifiers -+--+--- word| text | not null hashed | text

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

Hello, I appreciate your comments, thank you

[GENERAL] Selecting a daily puzzle record - which type of column to add?

Good evening, In a word game I store all player moves in the table: CREATE TYPE words_action AS ENUM ( 'play', 'skip', 'swap', 'resign', 'ban', 'expire' ); CREATE TABLE words_moves ( mid BIGSERIAL PRIMARY KEY, action

Re: [GENERAL] make postgresql 9.5 default on centos 7

Oops, one correction - instead of systemctl initdb postgresql-9.6 please use /usr/pgsql-9.6/bin/postgresql96-setup initdb as explained at https://wiki.postgresql.org/wiki/YUM_Installation Regards Alex

Re: [GENERAL] make postgresql 9.5 default on centos 7

Hi Steve, On Fri, Aug 18, 2017 at 7:50 PM, Steve Clark wrote: > > I loaded 9.5 on CentOS 7 but by default every thing wants to use the > default > 9.2 version that comes with CentOS 7. > > Is there a simple way to fix this so the 9.5 version of tools and > libraries

[GENERAL] Type cast in PHP PDO (does not work like in Java?)

Good evening, with PostgreSQL 9.6.3 and JDBC 42.1.1.jre7 types can be casted when calling a stored function: final String sql = "SELECT words_buy_vip(?::text, ?::int, ?::text, ?::text, ?::float, ?::inet)"; try (Connection db = DriverManager.getConnection(DATABASE_URL,

Re: [GENERAL] Do not INSERT if UPDATE fails

Hello, I have followed David's suggestion (thank you!) - On Wed, Aug 2, 2017 at 6:40 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Aug 2, 2017 at 8:58 AM, Alexander Farber < > alexander.far...@gmail.com> wrote: > >> However if the user reco

[GENERAL] Do not INSERT if UPDATE fails

Good evening, I have a custom SQL function in PostgreSQL 9.5.7 which adds a "log entry" to the table words_payments and then updates "vip_until" column in another table: CREATE OR REPLACE FUNCTION words_buy_vip( in_sid text, in_social integer, in_tid text, in_item

Re: [GENERAL] number of referencing and referenced columns for foreign key disagree

Thank you, Tom! Should I have the CHECK in the new table written out again as in - On Sat, Jul 29, 2017 at 3:41 PM, Tom Lane wrote: > > You have to use the separate-constraint FK syntax: > > CREATE TABLE words_payments ( > sid textNOT NULL, >

[GENERAL] number of referencing and referenced columns for foreign key disagree

Good afternoon, in 9.5.7 I have the following 2 tables - CREATE TABLE words_users ( uid SERIAL PRIMARY KEY, created timestamptz NOT NULL, visited timestamptz NOT NULL, ip inetNOT NULL, fcm text, apnstext, sns

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

Hi David, On Mon, Jul 10, 2017 at 10:02 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jul 10, 2017 at 7:32 AM, Alexander Farber < > alexander.far...@gmail.com> wrote: > >> >> However there is a problem: I can not use a "single-instance

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

I have tried: FOR _gid, _loser, _winner IN UPDATE words_games SET finished = CURRENT_TIMESTAMP WHERE finished IS NULL AND played1 IS NOT NULL AND played2 IS NOT NULL AND (played1 < CURRENT_TIMESTAMP - INTERVAL '24 hours'

[GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

Good afternoon, in PostgreSQL 9.5 with pgbouncer (having "pool_mode = session" and "server_reset_query = DISCARD ALL") 2-player games are stored in the following table: CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, finished timestamptz,

Re: [GENERAL] ERROR: query returned no rows

Thank you Adrian, with \sf+ words_skip_game(integer, integer) the line 85 was correct

Re: [GENERAL] ERROR: query returned no rows

In my case _opponent was NULL and there are no records in words_users with PK uid being NULL... so that was the reason. Thank you

Re: [GENERAL] ERROR: query returned no rows

Hi again, On Mon, Jun 26, 2017 at 8:21 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > words=> select * from words_skip_game(1, 1); > ERROR: query returned no rows > CONTEXT: PL/pgSQL function words_skip_game(integer,integer) line 85 at > SQL stateme

Re: [SPAM] [GENERAL] ERROR: query returned no rows

On Mon, Jun 26, 2017 at 8:39 PM, Moreno Andreo <moreno.and...@evolu-s.it> wrote: > Il 26/06/2017 20:21, Alexander Farber ha scritto: > >> >> RETURNING >> player1, >> score

[GENERAL] ERROR: query returned no rows

Good evening, with PostgreSQL 9.5 I have extended a larger custom function, which has worked well before and my problem is that the error message returned by the database is rather cryptic: words=> select * from words_skip_game(1, 1); ERROR: query returned no rows CONTEXT: PL/pgSQL function

Re: [GENERAL] ERROR: type " " does not exist

Good evening everyone, I apologize for the delay in replying and that you had to "reverse engineer" my question. This turned out indeed to be a special char problem. On MBA with macOS Sierra 10.12.5 I am using Postgres 9.5.4.1 of postgresapp.com. At the psql prompt I had copy-pasted: words=>

[GENERAL] ERROR: type " " does not exist

Good evening! Why does PostgreSQL 9.5.4 print the error: LOG: execute : SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns, out_sns AS sns, out_note AS note FROM words_resign_game($1::int, $2::int) DETAIL: parameters: $1 = '2', $2 = '1' ERROR: type " " does not exist at character

Re: [GENERAL] Merging records in a table with 2-columns primary key

Hello Andy and others - On Sun, Apr 2, 2017 at 5:13 PM, Andy Colson <a...@squeakycode.net> wrote: > On 04/02/2017 09:26 AM, Alexander Farber wrote: >> >> http://stackoverflow.com/questions/43168406/merging-records- >> in-a-table-with-2-columns-primary-key > >

[GENERAL] Merging records in a table with 2-columns primary key

Good afternoon, I have prepared a simple test case for my question - CREATE TABLE users ( uid SERIAL PRIMARY KEY, name varchar(255) NOT NULL ); CREATE TABLE reviews ( uid integer NOT NULL CHECK (uid <> author) REFERENCES users ON DELETE CASCADE, author integer

[GENERAL] Combining INSERT with DELETE RETURNING

Good afternoon, the doc https://www.postgresql.org/docs/9.6/static/sql-delete.html states: "The syntax of the RETURNING list is identical to that of the output list of SELECT." So I am trying to rewrite the INSERT .. ON CONFLICT DO NOTHING followed by a DELETE: INSERT INTO

Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv

Hi David, On Tue, Mar 21, 2017 at 8:21 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > > On Tuesday, March 21, 2017, Alexander Farber <alexander.far...@gmail.com> wrote: >> >> words=> COPY words_reviews (uid, author, nice, review, updated) FROM std

Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv

Thank you - this has worked: COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH (FORMAT csv); 1,2,1,'1 is nice by 2','2017-03-01' 1,3,1,'1 is nice by 3','2017-03-02' 1,4,1,'1 is nice by 4','2017-03-03' 2,1,1,'2 is nice by 1','2017-03-01' 2,3,1,'2 is nice by 3','2017-03-02'

[GENERAL] COPY ... FROM stdin WITH FORMAT csv

Good evening, I keep rereading https://www.postgresql.org/docs/9.6/static/sql-copy.html but just can't figure the proper syntax to put some records into the table: words=> \d words_reviews; Table "public.words_reviews" Column | Type | Modifiers

[GENERAL] Generating JSON-encoded list of object out of joined tables

Good afternoon, what would be please the best way to generate a list of JSON objects out of an SQL join? I am using jQuery dataTables plugin and initially was performing an SQL join and then in my PHP script was fetching results row by row and finally encoded them to JSON and feeded to the

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Yasin, thank you for this suggestion, but - On Tue, Mar 14, 2017 at 12:07 PM, Yasin Sari wrote: ​​ >> Have you tried with inner block and do nothing on exception; >> > > >> BEGIN >> >> >> .. >> BEGIN >> >> UPDATE words_social >>

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Hi Adrian - On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 03/14/2017 09:00 AM, Alexander Farber wrote: > >> My initial idea has been not to use ON CONFLICT at all, but instead only >> UPDATE the words_reviews records (set &quo

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then DELETE the rest (as I can't update them without a conflict) but I haven't figured

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Yes, Adrian - On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 03/14/2017 07:23 AM, Alexander Farber wrote: > >> >> in _uids array I have all user ids of player. >> >> I want to merge his or her data, including revi

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Hi Adrian, in _uids array I have all user ids of player. I want to merge his or her data, including reviews, to a single user id: out_uid. So I make a copy of related words_reviews records (where this user has been rated or this user has rated someone) and then re-INSERT ON CONFLICT DO NOTHING

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Sorry, missed the last DELETE: DELETE FROM words_reviews WHERE author <> out_uid AND author = ANY(_uids);

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

I have come up with the following (when trying to merge array _uids to a single out_uid): -- try to copy as many reviews of this user as possible INSERT INTO words_reviews ( uid, author, nice,

[GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Good afternoon, I am working on a small game, where the mobile app initially sends social network user data (name, city, gender) to PostgreSQL 9.5 backend and I store that data in a table: CREATE TABLE words_social ( sid varchar(255) NOT NULL, social integer NOT NULL CHECK (0 <=

Re: [GENERAL] DELETE and JOIN

Good morning and thank you for the replies. I've ended up with the following DELETE USING (in order to delete reviews coming from different user id, but same IP address in the last 24 hours): DELETE FROM words_reviews r USING words_users u WHERE r.uid = u.uid

[GENERAL] DELETE and JOIN

Good evening, In a 9.5 database I would like players to rate each other and save the reviews in the table: CREATE TABLE words_reviews ( uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users ON DELETE CASCADE, author integer NOT NULL REFERENCES words_users(uid) ON

Re: [GENERAL] CHECK for 2 FKs to be non equal

Thank you Alban and Francisco - On Sat, Mar 11, 2017 at 11:52 AM, Alban Hertroys <haram...@gmail.com> wrote: > > > On 11 Mar 2017, at 10:41, Alexander Farber <alexander.far...@gmail.com> > wrote: > > uid integer NOT NULL REFERENCES words_users(uid) CHE

[GENERAL] CHECK for 2 FKs to be non equal

Good morning, I am trying to add a table holding player reviews of each other: words=> CREATE TABLE words_reviews ( uid integer NOT NULL REFERENCES words_users(uid) CHECK (uid <> author) ON DELETE CASCADE, author integer NOT NULL REFERENCES words_users(uid) ON DELETE CASCADE,

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

And here is the table definition: words=> \d words_games; Table "public.words_games" Column | Type | Modifiers --+--+--- gid

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

Good morning, it looks that I failed to provide sufficient information in the first mail, sorry. Here again my problem - here is my PHP script: const SQL_GET_BOARD = ' SELECT out_bid AS bid, out_letters AS letters, out_values AS values FROMwords_get_board(?)

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

Yes, as David notices it is SQL function and not pg/PlSQL (you have probably misread this). I wonder what to do with the string in PHP, how to convert it to an (2-dimensional) array.

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

Adrian, but the stored function works, I am just not happy that the results are casted to strings by PHP... and wonder hpw to fix or workaround this. > >

[GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

Good evening! I am calling this stored function - CREATE OR REPLACE FUNCTION words_get_board( in_gid integer ) RETURNS TABLE ( out_bid integer, out_letters varchar[15][15], out_values integer[15][15] ) AS $func$

[GENERAL] Different LEFT JOIN results with and without USING

Good afternoon, why do these queries please return different results? SELECT s.gid, TO_CHAR(g.created, 'DD.MM.'), TO_CHAR(g.finished, 'DD.MM.'), LENGTH(s.word), s.score FROMwords_scores s LEFT JOIN words_games g ON s.gid = g.gid

Re: [GENERAL] Custom shuffle function stopped working in 9.6

At the same time this advice from http://stackoverflow.com/questions/42179012/how-to-shuffle-array-in-postgresql-9-6-and-also-lower-versions works, don't know why though: words=> select array_agg(u order by random()) words-> from unnest(array['a','b','c','d','e','f']) u; array_agg

Re: [GENERAL] Custom shuffle function stopped working in 9.6

words=> select version(); version -- PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Re: [GENERAL] Custom shuffle function stopped working in 9.6

I think ORDER BY RANDOM() has stopped working in 9.6.2: words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random(); unnest a b c d e f (6 rows)

[GENERAL] Custom shuffle function stopped working in 9.6

Good evening, after switching to 9.6.2 from 9.5.3 the following custom function has stopped working: CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[]) RETURNS varchar[] AS $func$ SELECT array_agg(letters.x) FROM (SELECT UNNEST(in_array) x ORDER BY RANDOM())

Re: [GENERAL] Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

Thank you, Rob - On Fri, Dec 2, 2016 at 11:12 AM, Rob Sargent <robjsarg...@gmail.com> wrote: > > > On Dec 2, 2016, at 2:52 AM, Alexander Farber <alexander.far...@gmail.com> > wrote: > > > > CREATE OR REPLACE FUNCTION words_unban_user( > > i

[GENERAL] Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

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

Re: [GENERAL] How to optimize SELECT query with multiple CASE statements?

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

Re: [GENERAL] How to optimize SELECT query with multiple CASE statements?

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

[GENERAL] How to optimize SELECT query with multiple CASE statements?

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,

Re: [GENERAL] WHERE ... IN condition and multiple columns in subquery

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

[GENERAL] WHERE ... IN condition and multiple columns in subquery

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

Re: [GENERAL] Selecting records with highest timestamp - for a join

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

Re: [GENERAL] Selecting records with highest timestamp - for a join

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

Re: [GENERAL] Selecting records with highest timestamp - for a join

to 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 <adrian.kla...@aklaver.com> wrote: > On 10/19/2016 11:35 AM, Alexander Farber wrote: > >> In a table I

Re: [GENERAL] isnull() function in pgAdmin3

Maybe your are after IS NOT DISTINCT FROM NULL https://www.postgresql.org/docs/current/static/functions-comparison.html

Re: [GENERAL] Custom SQL function does not like IF-statement

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,

Re: [GENERAL] Custom SQL function does not like IF-statement

Thank you Vik and others - On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing <v...@2ndquadrant.fr> wrote: > On 09/26/2016 08:22 PM, Alexander Farber wrote: > > > > CREATE OR REPLACE FUNCTION words_get_chat( > > in_uid integer, > &g

[GENERAL] Custom SQL function does not like IF-statement

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,

Re: [GENERAL] Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

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

[GENERAL] Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

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

Re: [GENERAL] Passing varchar parameter to INTERVAL

Thank you, this works well now and comments about IN is appreciated too :-)

Re: [GENERAL] Passing varchar parameter to INTERVAL

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.

[GENERAL] Passing varchar parameter to INTERVAL

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

Re: [GENERAL] Check if there 6 last records of same type without gaps

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

Re: [GENERAL] Check if there 6 last records of same type without gaps

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)

Re: [GENERAL] Check if there 6 last records of same type without gaps

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

Re: [GENERAL] Check if there 6 last records of same type without gaps

me(integer,integer) line 27 at SQL statement On Tue, Sep 6, 2016 at 2:30 PM, Sándor Daku <daku.san...@gmail.com> wrote: > On 6 September 2016 at 14:23, Alexander Farber <alexander.far...@gmail.com > > wrote: > >> >> On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku <d

Re: [GENERAL] Check if there 6 last records of same type without gaps

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

[GENERAL] Check if there 6 last records of same type without gaps

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

Re: [GENERAL] RETURNS TABLE function returns nothingness

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 <pavel.steh...@gmail.com> wrote: > > > 2016-09-02 19:21 GMT+02:00 Alexander Farber <alexander.far...@gmail.com>: > >

[GENERAL] RETURNS TABLE function returns nothingness

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,

Re: [GENERAL] How to retrieve jsonb column through JDBC

On Mon, Aug 29, 2016 at 7:50 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Sat, Aug 27, 2016 at 5:39 AM, Alexander Farber > <alexander.far...@gmail.com> wrote: > > > > List last_tiles = (List) JSON.parse(rs.getString("last_ > tiles"

Re: [GENERAL] a column definition list is required for functions returning "record"

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,

[GENERAL] How to retrieve jsonb column through JDBC

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 =

Re: [GENERAL] a column definition list is required for functions returning "record"

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

[GENERAL] a column definition list is required for functions returning "record"

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

Re: [GENERAL] Forward declaration of table

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.

Re: [GENERAL] Forward declaration of table

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 |

Re: [GENERAL] Forward declaration of table

Hi Igor, On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman <iney...@perceptron.com> wrote: > mailto:pgsql-general-ow...@postgresql.org] *On Behalf Of *Alexander Farber > > https://gist.github.com/afarber/c40b9fc5447335db7d24 > > > > Certain MOVE exists only within particul

[GENERAL] Forward declaration of table

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

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

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

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

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

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

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

[GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

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

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

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

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

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?

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

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,

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

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

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

Thank you for confirming, I supposed I have to use RETURN QUERY and now it works.

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

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

[GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

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

Re: [GENERAL] select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0

Thank you, so should I maybe switch to cardinality then?

[GENERAL] select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0

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

  1   2   3   4   >