Re: Same condition in the CTE and in the subsequent JOIN using it
On Wed, 25 Apr 2018 17:45:39 +0200
Alexander Farber wrote:
> WITH cte AS (
> SELECT
> DATE_TRUNC('day', m.played) AS day,
> m.mid,
> EXTRACT(EPOCH FROM m.played - LAG(m.played) OVER
> (PARTITION BY m.gid ORDER BY m.played))::int/60 AS diff
> FROMwords_moves m
> JOINwords_games g ON (m.gid = g.gid)
> JOINwords_social s ON (s.uid IN (g.player1, g.player2))
> WHERE s.social = in_social --
> CAN THIS BE REFERRED TO FROM BELOW?
> AND s.sid = in_sid
> AND m.played > CURRENT_TIMESTAMP - interval '1 month'
> )
> SELECT
> TO_CHAR(c.day, 'DD.MM.'),
> ROUND(AVG(c.diff)),
> ROUND(AVG(m.score), 1)
> FROMwords_moves m
> JOINcte c using(mid)
> JOINwords_social s USING(uid)
> WHERE s.social = in_social
> AND s.sid = in_sid
> AND m.action = 'play'
> GROUP BY c.day
> ORDER BY c.day;
>
> $func$ LANGUAGE sql STABLE;
>
> By looking at the above source code, do you think, that the condition being
> used twice (the s.social = in_social AND s.sid = in_sid) is "too much" and
> can be optimized? :-)
I would say so, because as you've already applied the filter in the CTE it
won't have any effect.
But anyway, since you are not using any column from words_social in your main
query, you can do away with it entirely and just remove
> JOINwords_social s USING(uid)
> WHERE s.social = in_social
> AND s.sid = in_sid
--
Bien à vous, Vincent Veyron
https://compta.libremen.com
Logiciel libre de comptabilité générale en partie double
Re: running \copy through perl dbi ?
On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier wrote:
>
> I'm trying to run a PG client side "\copy" command from a perl script. I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "\"
Hi David,
This works for me :
#create file
my $sql = qq {\\copy ( $sub_query ) to '$location' with null as ''
delimiter ';' csv header } ;
my $db_name = 'xx' ;
my @args = ( 'psql', '-c', $sql, $db_name ) ;
system( @args ) == 0 or die "Bad copy: $?" ;
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres
d'assurance
Re: running \copy through perl dbi ?
On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier wrote:
>
> I'm trying to run a PG client side "\copy" command from a perl script. I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "\"
>
> I can do this with a command line approach, attaching to the DB then run
> using...
Duh! I just realized that what I proposed with system() is a command line
approach.
As David Johnston mentionned, you can use the SQL COPY command.
However, you need then to deal with permissions so that the server may write
the file, so I wonder what approach is the most elegant?
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres
d'assurance
--
vv.lists
Re: running \copy through perl dbi ?
On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier wrote:
>
> I'm trying to run a PG client side "\copy" command from a perl script. I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "\"
>
> I can do this with a command line approach, attaching to the DB then run
> using...
Duh! I just realized that what I proposed with system() is a command line
approach.
As David Johnston mentionned, you can use the SQL COPY command.
However, you need then to deal with permissions so that the server may write
the file, so I wonder what approach is cleaner?
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres
d'assurance
Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
On Mon, 15 Jul 2024 20:31:13 +
Dan Kortschak wrote:
> My question is where would be the best place for me to looks to learn
> about how to implement a port of this SQLite? and what would broadly be
> the most sensible approach to take (to narrow down what I need to read
> through in learning)?
>
This is the goto page for anything SQL :
https://www.postgresql.org/docs/current/sql-commands.html
For DateTime types :
https://www.postgresql.org/docs/current/datatype-datetime.html
For JSON types :
https://www.postgresql.org/docs/current/datatype-json.html
If your query works in SQLite, all you have to do is read those, and try to
port; if it fails, read them again. Also search the archives of the
pgsql-general list, many answers in there
--
Bien à vous, Vincent Veyron
https://compta.libremen.com
Logiciel libre de comptabilité générale en partie double
Re: question on audit columns
On Wed, 4 Sep 2024 18:19:47 +0530
yudhi s wrote:
Hi,
> In postgres database , we have all the tables with audit columns like
> created_by_user, created_timestamp,updated_by_user, updated_timestamp. So
> we have these fields that were supposed to be populated by the time at
> which the insert/update operation happened on the database but not at the
> application level. So we are planning to populate the created_by_user,
> created_timestamp columns by setting a default value of "current_timestamp"
> and "current_user" for the two columns, but no such this is available to
> populate while we do the update of the row, so the only option seems to be
> through a trigger.
>
If you can live with the fact that updated_by_user and updated_timestamp get
the same values as created_by_user and created_timestamp when inserting the
record, then you can do :
vv=> create table audit (created_by_user text default current_user,
created_timestamp timestamp default now(), updated_by_user text default
current_user, updated_timestamp timestamp default now(), data text);
CREATE TABLE
vv=> insert into audit (data) values ('abc');
INSERT 0 1
vv=> select * from audit;
created_by_user | created_timestamp | updated_by_user |
updated_timestamp | data
-++-++--
vincent | 2024-09-05 19:17:53.446109 | vincent | 2024-09-05
19:17:53.446109 | abc
(1 row)
--as user postgres
update audit set updated_by_user = DEFAULT, updated_timestamp = DEFAULT, data =
'def';
vv=> select * from audit;
created_by_user | created_timestamp | updated_by_user |
updated_timestamp | data
-++-+---+--
vincent | 2024-09-05 19:17:53.446109 | postgres| 2024-09-05
19:24:01.19186 | def
(1 row)
--
Bien à vous, Vincent Veyron
https://marica.fr/
Logiciel de suivi des contentieux juridiques, des sinistres d'assurance et des
contrats
unexpected character used as group separator by to_char
Hi,
I'm having a problem with to_char() inserting unexpected characters as group
separator.
For the record below, I wish to display 'credit' properly formated in an html
form, using to_char(credit, 'FM999G990D00')
SELECT *, to_char(credit, 'FM999G990D00') as yo from tblprospect_balance where
id_item = 33;
id_prospect | id_item | date_item | libelle | debit | credit | lettrage |
id_facture |yo
-+-++-+---+-+--++--
385 | 33 | 2021-03-09 | yo man | 0.00 | 2345.10 | |
8 | 2 345,10
The numbers are properly displayed in the html form. However, updating the
record requires the user to manually delete the space in '2 345,10', otherwise
the last digit is lost. Typing numbers including a group separator using the
space bar works fine.
I exported the record to a text file and inspected it with emacs in hexadecimal
mode :
3338 3509 0932 3032 312d 3033 2d30 385.33.2021-03-0
0010: 3909 796f 206d 616e 0930 2e30 3009 3233 9.yo man.0.00.23
0020: 3435 2e31 3009 0938 0932 e280 af33 3435 45.10..8.2...345
0030: 2c31 300a,10.
As you can see, the space in field 'libelle' (yo man) is different from the one
in field 'yo' (2...345,10)
The difference is also apparent in a link built using the record's fields as
parameters :
/base/prospect?id_prospect=385&balance=0&update_item=0&id_item=33&id_facture=8&date_item=2021-03-09&libelle=yo%20man&debit=0,00&credit=2%E2%80%AF345%2C10&lettrage=
^
What can I do to get a standard space as group separator for my numbers?
system information
I use postgresql 11.9 on Debian buster
My settings are :
show lc_numeric;
lc_numeric
-
fr_FR.UTF-8
locale
LANG=C.UTF-8
LANGUAGE=
LC_CTYPE="C.UTF-8"
LC_NUMERIC="C.UTF-8"
LC_TIME="C.UTF-8"
LC_COLLATE="C.UTF-8"
LC_MONETARY="C.UTF-8"
LC_MESSAGES="C.UTF-8"
LC_PAPER="C.UTF-8"
LC_NAME="C.UTF-8"
LC_ADDRESS="C.UTF-8"
LC_TELEPHONE="C.UTF-8"
LC_MEASUREMENT="C.UTF-8"
LC_IDENTIFICATION="C.UTF-8"
LC_ALL=
\d tblprospect_balance
Table "public.tblprospect_balance"
Column| Type | Collation | Nullable |
Default
-+---+---+--+--
id_prospect | integer | | not null |
id_item | integer | | not null |
nextval('tblprospect_balance_id_item_seq'::regclass)
date_item | date | | not null | CURRENT_DATE
libelle | text | | |
debit | numeric(10,2) | | not null | 0
credit | numeric(10,2) | | not null | 0
lettrage| text | | |
id_facture | integer | | not null | 0
--
Bien à vous, Vincent Veyron
https://marica.fr/
Logiciel de gestion des sinistres assurances, des dossiers contentieux et des
contrats pour le service juridique
Re: unexpected character used as group separator by to_char
On Tue, 9 Mar 2021 13:33:19 -0700 "David G. Johnston" wrote: > > By using "G" you are giving up control and letting your locale settings > decide what gets output. You can continue to use to_char but take control > back by being explicit, or pass actual numbers into and out of the database > and let your front-end deal with presentation concerns. I suggest the > latter. Well, that was a fast answer :-) Indeed, replacing "G" with \s works. However, the application is destined to be multi-lingual, and number formats vary widely between countries : 1,000.25 (US) 1'000,25 (CH) 1 000,25 (FR) 1.000,25 (GER) etc... So, I intended to follow this advice by Tom Lane : https://www.postgresql-archive.org/GENERAL-setting-LC-NUMERIC-td1857521.html Having a database with the proper lc_numeric setting for each country, and using to_char/to_number to manipulate numbers is much more appealing than writing my own parser in my front end. But this weird space is getting in my way. -- Bien à vous, Vincent Veyron https://marica.fr/ Logiciel de gestion des sinistres assurances, des dossiers contentieux et des contrats pour le service juridique
Re: unexpected character used as group separator by to_char
On Tue, 09 Mar 2021 16:22:07 -0500 Tom Lane wrote: > The point here is that 'G' and related format codes act as specified by > your LC_MONETARY locale. If you don't like the results, you need to use > a different locale. This is a numeric(10,2) type field though. I tried casting it to money type, with lc_monetary set to "fr_FR.UTF-8", same weird space > > (I suppose you could also use regexp_replace to convert random forms > of whitespace to plain ASCII space.) No dice. 'G' formatting looks like a whitespace, but is different (it appears to be slightly narrower when displayed in html, too) : select regexp_replace(to_char(1234.56, 'FM999 990D00'), E'[\\s]', 'x'); regexp_replace 1x234,56 (1 row) select regexp_replace(to_char(1234.56, 'FM999G990D00'), E'[\\s]', 'x'); regexp_replace 1 234,56 (1 row)
Re: unexpected character used as group separator by to_char
On Wed, 10 Mar 2021 02:24:57 -0300 Alvaro Herrera wrote: > > That space (0xe280af) is U+202F, which appears to be used for French and > Mongolian languages (exclusively?) On Tue, 09 Mar 2021 18:57:05 -0500 Tom Lane wrote: > In any case, you could force the issue with a pattern like '[\s\u]' > for whatever the code point of that character is. On Wed, 10 Mar 2021 09:41:19 +0100 Laurenz Albe wrote: > SELECT replace(to_char(2345.10, 'FM999G990D00'), E'\u202F', ' '); Thank you all for the helpful answers, I can work with that. Strange that to_char() and to_number() would use a different separator, though : select to_number(to_char(1234.56, 'FM999G990D00'), 'FM999G990D00'); to_number --- 1234.5 select to_number(replace(to_char(1234.56, 'FM999G990D00'),E'\u202F', ' '), 'FM999G990D00'); to_number --- 1234.56 -- Bien à vous, Vincent Veyron https://compta.libremen.com Logiciel libre de comptabilité générale en partie double
Re: How to implement expiration in PostgreSQL?
On Thu, 1 Apr 2021 09:51:38 +0800 Glen Huang wrote: Hi, If you are in a position to modify your design, I believe your problem comes from this part : > WHERE paid OR join_time > now() - ‘1h’::interval which suggests that there is a 'paid' column being updated. I learned that the proper way to structure a database to maintain membership and save a lot of grief is the following : create table seated_member( name text not null, ..., join_time date not null default now, validity integer not null default 1 ); where validity is a number of whatever unit is appropriate; typically 'year' for a club membership (newspapers use 'issue number' instead of join_time to account for strikes, when no paper is issued). In your case, 'hour' I suppose. All you need to do when the member pays is to update the 'validity' field with the proper amount of units. This makes for very simple and efficient queries to retrieve the data, and you only need to write : WHERE now() < join_time + 'validity hours'::interval to retrieve valid accounts. Accounts expire automatically, deleting them can wait; it also makes it easier to send reminders before the expiration date -- Bien à vous, Vincent Veyron https://marica.fr Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance
Re: Who altered the database?
On Fri, 9 Apr 2021 13:14:17 +0200
Durumdara wrote:
>
> What can we do to get info about the change (who did it, from what IP, when
> it happened) in the future?
>
> As I see we can't define precisely what we want to log... All or nothing.
>
Create a web interface to apply the changes and have the web server log the
info in its own logs?
Apache has this directive :
https://httpd.apache.org/docs/2.4/mod/mod_log_config.html#logformat
--
Bien à vous, Vincent Veyron
https://marica.fr/
Logiciel de suivi des contentieux juridiques, des sinistres d'assurance et des
contrats
Re: How to allow null as an option when using regexp_matches?
On Wed, 8 Dec 2021 17:07:27 + Shaozhong SHI wrote: > > Any good example? > Plenty of them in the fine documentation : https://www.postgresql.org/search/?q=coalesce -- Bien à vous, Vincent Veyron https://compta.libremen.com Logiciel libre de comptabilité générale en partie double
Re: When Update balloons memory
On Tue, 14 Dec 2021 08:16:08 +
Klaudie Willis wrote:
> CREATE INDEX partindx ON public.part_main USING btree ((actiondate)::date);
> -- mem bug?
Nope, syntax error
ERROR: syntax error at or near "::"
LINE 1: ...indx_1 ON public.part_main USING btree ((actiondate)::date);
^
> -- CREATE INDEX partindx ON public.part_main USING btree (actiondate); -- no
> bug
> -- mem runaway follows
> update part_main set txid = txid + 1;
>
> Hope you can replicate it.
>
Can't replicate on my Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz with 2Go of RAM
time psql -c 'update part_main set txid = txid + 1' vv
UPDATE 3100
real24m39.594s
user0m0.121s
sys 0m0.036s
--
Bien à vous, Vincent Veyron
https://marica.fr
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance
Re: When Update balloons memory
On Tue, 14 Dec 2021 11:18:07 -0500
Tom Lane wrote:
>
> This leak is new in v14, possibly that's why Vincent didn't reproduce it.
Indeed, I'm on v11
--
Bien à vous, Vincent Veyron
https://marica.fr
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance
Re: Windows XP to Win 10 migration issue
On Sat, 9 Dec 2017 10:11:42 -0600 Dale Seaburg wrote: > No Go! Would not start. Any error message in your logs? I would certainly second Scott's suggestion to check the processors. I've had to do what you describe once, and it took me four machines before I got one that would start postgresql with my rescued data folder. You might have better luck finding an old XP machine similar to your deceased one, installing postgresql on it, and using that to start your cluster. -- Bien à vous, Vincent Veyron https://marica.fr/ Logiciel de gestion des sinistres assurances, des dossiers contentieux et des contrats pour le service juridique
Re: Retrieve filename within a script
On Thu, 14 Nov 2024 08:42:36 +
Patrick FICHE wrote:
> I was wondering if it was possible to get the filename provided as an
> argument to psql.
> psql -f /tmp/test.sql
>
Since you know it already, you could pass it as a variable :
psql -f /tmp/test.sql -v my_path='/tmp/test.sql'
and retrieve it with SELECT :'my_path';
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres
d'assurance
Re: psql command line editing
On Wed, 12 Feb 2025 16:21:21 -0500
Tom Lane wrote:
> I do have
>
> export EDITOR=emacs
>
> Possibly libreadline is reacting to that, but I've not checked
> into it.
>
Emacs combinations work on my machine without it.
--
Bien à vous, Vincent Veyron
https://compta.libremen.com
Logiciel libre de comptabilité générale en partie double
