> "Durumdara" == Durumdara writes:
Durumdara> I have to make a virtual table which is minute based.
Durumdara> I thought I would make a generated temp table
Durumdara> (generate_series) and then join these values based on minue.
Durumdara> 03:00 10
Durumdara> 03:01 NULL
Durum
> "Dominique" == Dominique Devienne writes:
Dominique> Is it possible to maintain $1's order directly in SQL?
>> This is the correct way:
>>
>> SELECT ... FROM unnest($1) WITH ORDINALITY AS u(id,ord)
>> JOIN yourtable t ON t.id=u.id
>> ORDER BY u.ord;
Dominique> Thanks Andrew, for s
> "Dominique" == Dominique Devienne writes:
Dominique> Hi. With an integer identity primary key table,
Dominique> we fetch a number of rows with WHERE id = ANY($1),
Dominique> with $1 an int[] array. The API using that query must return
Dominique> rows in the input int[] array order, and
> "Gabriel" == Gabriel Furstenheim Milerud writes:
Gabriel> Hi,
Gabriel> I'm writing a Postgres native extension and I would like to
Gabriel> check that a numeric is zero.
Gabriel> My problem is that all exported methods like numeric_eq or
Gabriel> numeric_sign require me to have a num
> "Evgeny" == Evgeny Morozov writes:
Evgeny> Indeed, I cannot get that far due to the same error. I read
Evgeny> about ignore_system_indexes, but...
Evgeny> # sudo -u postgres psql -w -p 5434 -c "set ignore_system_indexes=on";
Evgeny> ERROR: parameter "ignore_system_indexes" cannot be s
> "TalGloz" == TalGloz writes:
TalGloz> Which is good, my seal_mean_cxx_v2.so being created and copied
TalGloz> to /usr/pgsql-12/lib/. But right after that I get this and it
TalGloz> doesn't seem to effect my seal_mean_cxx_v2.so library:
What's happening here is that it's attempting to b
> "Matthias" == Matthias Apitz writes:
Matthias> i.e. 0xc3 is translated to 0xc383 and the 2nd half, the
Matthias> 0xbc to 0xc2bc, both translations have nothing to do with
Matthias> the original split 0xc3bc, and perhaps in this case it
Matthias> would be better to spill out a bl
> "Rory" == Rory Campbell-Lange writes:
Rory> Or:
Rory> iconv -f WINDOWS-1252 -t UTF-8 -c < tempfile2 > tempfile3
No. That's just a conversion of win1252 to utf8 without regard for any
UTF8 that might already be present in the input. Any such input will end
up double-encoded, requirin
> "Thomas" == Thomas Munro writes:
Thomas> Something like this approach might be useful for fixing the CSV file:
Thomas>
https://codereview.stackexchange.com/questions/185821/convert-a-mix-of-latin-1-and-utf-8-to-proper-utf-8
Or:
perl -MEncode -pe '
use bytes;
sub c { decode("UTF-8",s
> "Paula" == Paula Kirsch writes:
Paula> I'm just trying to understand the trade-offs between sending
Paula> everything always as text, all integer parameters as binary,
Paula> floats as binary, etc.
For passing data from client to server, there's no particular reason not
to use the binar
> "Gerald" == Gerald Britton writes:
Gerald> That leads me to ask:
Gerald> If (and under what circumstances) PostgreSQL evaluates
Gerald> functions lazily (returning rows as requested by the caller) or
Gerald> eagerly (evaluation all rows before returning the first one)?
This isn't triv
> "Peter" == Peter Eisentraut writes:
Peter> max_locks_per_transactions only affects relation locks (also
Peter> known as heavy weight locks), but pg_locks also shows other
Peter> kinds of locks.
pg_locks shows exactly two types of locks: "heavy" locks (which are not
merely relation locks
> "Rich" == Rich Shepard writes:
Rich> I doubt that my use will notice meaningful differences. Since
Rich> there are only two or three databases in UTF8 and its collation
Rich> perhaps I'll convert those to LATIN1 and C.
Note that it's perfectly fine to use UTF8 encoding and C collation (
>>>>> "Tom" == Tom Lane writes:
Tom> Andrew Gierth writes:
Tom> I think the "official" name of that zone is America/Los_Angeles.
Tom> But initdb might seize on the US/Pacific alias, if available,
>> And now you know why I have been sayin
> "Tom" == Tom Lane writes:
>>> Ugh. It doesn't have the old backward compatibility names like
>>> US/Pacific installed by default, which is a problem if that's what
>>> initdb picked for your cluster (or you've stored references to any
>>> of those names in other ways).
>> One quick fi
> "stan" == stan writes:
stan> I suspect this may be because the SELECT in the values clause
stan> returns multiple rows?
Understand this: VALUES is really just a special form of SELECT that
returns only the specific rows that you tell it to construct. Every
single row returned by a VALUE
> "Matthias" == Matthias Apitz writes:
Matthias> Hello,
Matthias> I can SELECT && print a column in hex with:
Matthias> pos71=# select d02name::bytea from d02ben where d02bnr =
'1048313' ;
If your intention is to see the raw bytes of the stored text value, for
example to check the
> "Abraham" == Abraham, Danny writes:
Abraham> Thanks for the clarification.
Abraham> The problem is still this:
Abraham> select date('20191001') - date('20190101') ;
Abraham> in my servers it is always '273'.
Abraham> In the customer's DB it is '273 days';
Then you need to establish wh
> "Abraham" == Abraham, Danny writes:
Abraham> The problematic code is:
Abraham> select date(cm_curr_date) - date(CM_DATE) into diff_days from
CMS_SYSPRM;
This will always return an integer, unless either the date() cast or the
-(date,date) operator have been redefined or modified.
Abra
> "Abraham" == Abraham, Danny writes:
Abraham> Hi
Abraham> A question on day interval
Abraham> select date('20191001') - date('20190923');
Abraham> Will provide sometimes '8' - an integer , but sometimes '8
Abraham> day' - a string
No, it'll always return an integer. You will only get
> "Thomas" == Thomas Kellerer writes:
Thomas> The table size with jsonb was bigger in general, but the one
Thomas> with the "integer" value was even bigger than the one with the
Thomas> "string" storage.
jsonb stores numeric values as "numeric", not as integers or floats, so
the storage n
> "David" == David Salisbury writes:
David> I didn't specify the real problem as it's all wrapped up in
David> layers and I didn't want to post a "can someone write the query
David> for me". The real problem was I have a table with a string
David> holding comma separated numbers, and need
> "Tom" == Tom Lane writes:
>> Here is a draft patch along those lines; the intent of this one is
>> that no existing walker or mutator should need to change (the change
>> to the dependency code is basically cosmetic I believe, just avoids
>> walking some things twice).
Tom> Hmm. I th
>>>>> "Andrew" == Andrew Gierth writes:
Andrew> We could minimize the chance of breakage in a back-patched fix
Andrew> by having query_tree_walker/mutator iterate the windowClause
Andrew> list itself
Here is a draft patch along those lines; the intent of t
> "Tom" == Tom Lane writes:
Tom> However, we need to fix this in all active branches, and I
Tom> definitely agree with minimizing the amount of change to back
Tom> branches. The fact that the minimal change breaks (or exposes an
Tom> oversight in) assign_collations_walker makes it very pl
> "Tom" == Tom Lane writes:
Tom> It looks to me that the reason is that query_tree_mutator
Tom> (likewise query_tree_walker) fails to visit query->windowClause,
I noticed this too. I spent some time looking at what might break if
that was changed (found two places so far, see attached draf
> "Alastair" == Alastair McKinley writes:
Alastair> Hi all,
Alastair> I noticed this strange behaviour whilst trying to write a
Alastair> function for Postgres 11.5 (PostgreSQL 11.5 on
Alastair> x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
Alastair> (Red Hat 4.8.5-36), 64-b
> "rob" == rob stone writes:
rob> You can EXTRACT timezone, timezone_hour and timezone_minute from a
rob> timestamp. Using 'timezone' returns a value representing seconds
rob> from UTC.
Yes, but this always outputs a value representing the server timezone;
there's no way (other than the s
> "Paul" == Paul McGarry writes:
> On Wed, Sep 25, 2019 at 5:44 AM Adrian Klaver
> wrote:
>>
>> "Therefore whatever renders the offset needs to be capable of doing
>> it per row, independently of the server/session time zone."
The key point here is that storing the timestamp as WITHOUT
> "Adrian" == Adrian Klaver writes:
Adrian> This has come up before and the general suggestion has been to
Adrian> have a column for a naive(timestamp w/o tz) timestamp and a
Adrian> column for the timezone.
No, that's usually (not always) backwards, and in any event wouldn't
solve this p
> "Paul" == Paul McGarry writes:
Paul> Hi there,
Paul> Does anyone have a good way of doing:
Paul> =
Paul> select '2020-04-04 15:00:00+00'::timestamp with time zone AT TIME ZONE
Paul> 'Australia/Sydney';
Paul> timezone
Paul> -
Paul> 2020-04-05 02:00:00
> "Thiemo" == Thiemo Kellner writes:
Thiemo> Hi all
Thiemo> I try to create a function (code at
Thiemo> https://pastebin.com/mTs18B90)
Paste sites are for IRC, on the mailing list you should always attach
the necessary details to your message.
Thiemo> using 'get diagnostics' to retrieve
> "Vladimir" == Vladimir Soldatov writes:
Vladimir> Hi guys,
Vladimir> I am new to PostgreSQL, so sorry for maybe stupid question. I
Vladimir> am working on some application implementing Frontend/Backend
Vladimir> PG protocol and one of the goals - having only "admin" users
Vladimir> cr
> "Tom" == Tom Lane writes:
Tom> Two I'd particularly draw your attention to are
Tom> join_collapse_limit and from_collapse_limit --- if you set both to
Tom> 1, that'll effectively disable searching for a good join order,
Tom> causing the join order to match the syntactic structure of the
> "Tom" == Tom Mercha writes:
Tom> Hi All
Tom> As we know, a query goes through number of stages before it is
Tom> executed. One of these stages is query optimization (QO).
That's not really true at all. One of the stages is query _planning_,
which takes the (rewritten) query as input an
> "Wells" == Wells Oliver writes:
Wells> Hi guys, hoping you can help clarify what the 'hierarchy' of
Wells> casts might be in function arguments.
In terms of which casts will be selected in some given context, there
isn't exactly a "hierarchy", but some types are designated as
"preferred"
> "rihad" == rihad writes:
rihad> There's a weird problem, even when the time is initially set by
rihad> openntpd it keeps lagging by one second every few seconds:
rihad> $ sudo /usr/local/etc/rc.d/openntpd restart
What OS is this?
I've seen this kind of thing with FreeBSD where the ke
> "Rob" == Rob Sargent writes:
>>> local all all trust
Rob> That line has four values and the header has 5.
That's standard for "local" lines, which lack an ADDRESS field.
--
Andrew (irc:RhodiumToad)
> "Evaldas" == Evaldas Užpalis writes:
Evaldas> Hello PostgresSQL users and admins,
Evaldas> I need some help with my problem.
Evaldas> I looked at postgresql logs and it is littered with error messages
like:
Evaldas> ERROR: found xmin 3875696185 from before relfrozenxid 1599104090
E
> "Matthias" == Matthias Apitz writes:
Matthias> it is exported into the file as
Matthias> cmd \| lpr ...
Matthias> because the export delimiter is set to '|';
Matthias> the COPY in text mode was complaining because it interpreted
Matthias> the | of the sequence \| as an delimite
> "Matthias" == Matthias Apitz writes:
Matthias> sed 's/|/\v/g' < table-from-Sybase | sed 's/\\\v/|/g' >
table-for-copy
>> What on earth is this supposed to achieve?
Matthias> It first translates any char '|' to vtab and then any '\vtab'
Matthias> (i.e. a backslash followed by a vtab
> "Matthias" == Matthias Apitz writes:
>> That sounds like the file is not actually a CSV - why do you think
>> it is?
Matthias> Well, it's not strictly CSV,
In other words it's not a CSV at all.
Matthias> I do load this now with COPY in mode TEXT and modify the data
Matthias> before
> "Matteo" == Matteo Beccati writes:
Matteo> https://wiki.php.net/rfc/pdo_escape_placeholders
Matteo> One question that I'm supposed to answer now is: is there any
Matteo> known usage in the wild of some custom "??" operator that would
Matteo> require funny escaping like ""?
I don't
> "Matthias" == Matthias Apitz writes:
Matthias>... WITH DELIMITER '|', FORMAT CSV ;
I think you misread the docs; the new-style syntax would be
COPY ... WITH (DELIMITER '|', FORMAT CSV);
where the parens are not optional. The old-style syntax with no parens
after WITH is what you wer
> "Ivan" == Ivan Voras writes:
Ivan> Since AFAIK temp tables are very close to unlogged ordinary
Ivan> tables, what would stop this feature from being implemented?
The key difference between temp tables and other tables is that temp
table data does not use the shared_buffers but is buffere
> "Alastair" == Alastair McKinley writes:
Alastair> Hi all,
Alastair> I recently experienced a performance degradation in an
Alastair> operational system that I can't explain. I had a function
Alastair> wrapper for a aggregate query that was performing well using
Alastair> the expected
> "Guntry" == Guntry Vinod writes:
Guntry> Hi Team,
Guntry> We are using the below command
Guntry> COPY <> from 'C:\Data_Dump\ABC.txt' DELIMITER '|';
COPY in postgresql expects one of two data formats; since you did not
specify CSV, in this case it's expecting the default postgresql fo
> "Adrian" == Adrian Klaver writes:
>> yeesh. that's a very long-winded way to write current_date + 7
Adrian> Yeah, I was just working of the OP's original DEFAULT:
Adrian> site_mode_date timestamp NOT NULL DEFAULT date_trunc('day',
Adrian> LOCALTIMESTAMP)+7,
Right, but since all these
> "Adrian" == Adrian Klaver writes:
Adrian> Or cast to a date:
Adrian> test=> select date_trunc('day', localtimestamp)::date + 7;
yeesh. that's a very long-winded way to write current_date + 7
--
Andrew (irc:RhodiumToad)
> "Matthias" == Matthias Apitz writes:
Matthias> There is no cmd 'pg_config'. Can I compile this from source?
Some distros separate out a 'libpq' package, and have a 'libpq-devel'
package with pg_config in it. Did you look for that?
--
Andrew (irc:RhodiumToad)
> "Steven" == Steven Schlansker writes:
Steven> I figured I'd end up with significantly better storage and
Steven> performance characteristics if I first compute a uuid[] value
Steven> and build the GIN over that, and use the array operator class
Steven> instead. Additionally, this elimin
> "Thomas" == Thomas Kellerer writes:
Thomas> Hello,
Thomas> some time ago someone published a website where it was possible
Thomas> to select two arbitrary Postgres version and then see a list of
Thomas> Bugfixes (and features) that are missing in the older version
Thomas> of the two.
> "Rich" == Rich Shepard writes:
Rich> Tried this and did not do it correctly. Should there be two
Rich> 'order by', one in the sub-query, the other in the top-level
Rich> query?
Yes.
Rich> This does not return the desired order:
Rich> select p.person_id, p.lname, p.fname, p.direct_ph
> "Karl" == Karl Denninger writes:
Karl> "cc" on FreeBSD is now (and has been for quite some time) clang:
Karl> If it will compile Postgres now it definitely would not when it
Karl> was first shifted to;
People have been building PG with clang since at least as far back as
clang 3.1, and
> "Karl" == Karl Denninger writes:
>> That's a configure line that has never worked on FreeBSD:
>>
>> % ./configure --with-openssl --with-perl
>> [...]
>> configure: error: readline header not found
>> If you have libedit already installed, see config.log for details on the
>> failure
> "Karl" == Karl Denninger writes:
Karl> That was on a bare, new grab of the source from your repository and a
Karl> straight-up run of "./configure --with-openssl --with-perl", then
"gmake".
That's a configure line that has never worked on FreeBSD:
% ./configure --with-openssl --with-pe
> "Karl" == Karl Denninger writes:
Karl> Both 10.7 and 11.2 fail on FreeBSD 12-STABLE if --with-openssl is
Karl> part of the configure string.
Nope, they work fine for me, running under 12-stable as of a couple of
weeks ago.
--
Andrew (irc:RhodiumToad)
> "T" == T L writes:
T> Below is my test. It prints a strange character instead of "a"; and
T> says that the value isn't numeric.
Yeah, there's plenty else wrong with your code.
Did you look at how JsonbToCStringWorker does it? that looks like the
best example I can find on a quick scan.
> "T" == T L writes:
T> //problem lines!!! //either elog crashes pg server
T> char *buf = pnstrdup(ptr->key.val.string.val,
T> ptr-> key.val.string.len);
T> elog(NOTICE, "print_kv_pair(): k = %s",
T> (ptr->key).val.string.val); //debu
> "T" == T L writes:
T> //Problem line!!!
T> //elog(NOTICE, "print_kv_pair(): k = %s, v = %s",
T> ptr-> key.val.string.val, numeric_out(ptr->value.val.numeric));
string.val isn't a C string (notice the "not null terminated" comment in
the structure definition), an
> "Morris" == Morris de Oryx writes:
Morris> UUIDs as a type are an interesting case in Postgres. They're
Morris> stored as a large numeric for efficiency (good!), but are
Morris> presented by default in the 36-byte format with the dashes.
Morris> However, you can also search using the da
> "Ron" == Ron writes:
Ron> Arrays are -- by definition -- not atomic, and so they
Ron> fundamentally break the model that relational databases are
Ron> founded upon. If you want to be a good database designer, don't
Ron> use arrays.
"In theory there is no difference between theory and
> "Thomas" == Thomas Kellerer writes:
[intarray woes]
Thomas> Is this expected behaviour? Is this caused by the Postgres core
Thomas> (e.g. the optimizer to taking the opclass into account) or is
Thomas> it a "problem" in the way the intarray module defines its
Thomas> operators?
It's b
> "Martin" == Martin Mueller writes:
Martin> I run Postgres 10.5. I understand that there is something
Martin> called tablefunc and it includes a crosstab function. On Stack
Martin> Overflow I learn that you import this function. But from where
Martin> and how? The Postgres documentation
> "Jitendra" == Jitendra Loyal writes:
Jitendra> I find that the RECURSIVE can be used only once in a CTE.
RECURSIVE can be specified only once, but it applies to all CTEs at that
level. That is to say, RECURSIVE qualifies the preceding WITH, _not_ the
following CTE.
Note that just specify
> "Chuck" == Chuck Martin writes:
Chuck> I am trying to create a query that returns all transactions for
Chuck> each person who has a balance over a given amount. I thought
Chuck> HAVING was the answer, but if so, I'm mis-using it. This query
Chuck> returns only transactions exceeding the
> "Rich" == Rich Shepard writes:
Rich> Using LIMIT 1 produces only the first returned row. This
Rich> statement (using max() for next_contact) produces no error
Rich> message, but also no results so I killed the process after 30
Rich> seconds. Without a syntax error for guidance I don't k
> "Rich" == Rich Shepard writes:
Rich> I found a couple of web pages describing the lateral join yet
Rich> have not correctly applied them. The manual's page did not help
Rich> me get the correct syntax, either. Think I'm close, however:
Rich> select p.person_id, p.lname, p.fname, p.dire
> "Jeremy" == Jeremy Finzel writes:
Jeremy> Yes Linux. This is very helpful, thanks. A follow-up question -
Jeremy> will it take postgres a really long time to crash (and
Jeremy> hopefully recover) if I have say 1T of RAM because it has to
Jeremy> write that all out to a core file first?
> "Adrian" == Adrian Klaver writes:
Adrian> Close to your last posted query. person_id 2 and 3 have NULL
Adrian> values for activities data as there is no record for 2 and 3 is
Adrian> out of the date range.:
Adrian> select
Adrian>p.person_id,
Adrian>p.desc_fld,
Adrian>a.
> "Rich" == Rich Shepard writes:
Rich> Will probably try DISTINCT ON, too, if that makes it simpler or
Rich> faster.
You want LATERAL.
--
Andrew (irc:RhodiumToad)
> "Tom" == Tom Lane writes:
> Hannes Erven writes:
>> I've just had a "VACUUM FULL " crash due to 100% disk usage.
>> Clearly my fault, I was expecting the new table to be small enough.
Tom> What do you mean by "crash" exactly? A normal transactional
Tom> failure should've cleaned up o
> "Bruce" == Bruce Momjian writes:
Bruce> Oh, right, you want date, so use:
Bruce> SELECT date_trunc('week', CURRENT_DATE) + '6 days';
Three major things wrong with this:
1. If you do this on Sunday, it gives you the current day not the _next_
Sunday.
2. If you try and do this for other
> "Ron" == Ron writes:
Ron> Hi,
Ron> v9.6.6
Ron> Is there a built in function to calculate, for example, next
Ron> Sunday?
No, but such things aren't hard to calculate using the available
primitives.
To get "next Xday", for example, you can add 7 days and then do
"previous or current
> "Hengky" == Hengky Lie writes:
Hengky> Hi,
Hengky> I want to create a function to update my table (flag='9') and
Hengky> insert new record (flag='0') with the rfidnumber specified in a
Hengky> parameter.
rfidnumber is stored as text/varchar? if it's something else, change the
suggesti
> "pabloa98" == pabloa98 writes:
pabloa98> I found this article:
pabloa98>
https://manual.limesurvey.org/Instructions_for_increasing_the_maximum_number_of_columns_in_PostgreSQL_on_Linux
Those instructions contain obvious errors.
pabloa98> It seems I should modify: uint8 t_hoff;
pablo
> "pabloa98" == pabloa98 writes:
pabloa98> I did not modify it.
Then how did you create a table with more than 1600 columns? If I try
and create a table with 1765 columns, I get:
ERROR: tables can have at most 1600 columns
--
Andrew (irc:RhodiumToad)
> "pabloa98" == pabloa98 writes:
pabloa98> the table baseline_denull has 1765 columns,
Uhh...
#define MaxHeapAttributeNumber 1600/* 8 * 200 */
Did you modify that?
(The back of my envelope says that on 64bit, the largest usable t_hoff
would be 248, of which 23 is fixed overhead lea
> "Susan" == Susan Hurst writes:
Susan> What is the trick for displaying column comments in views? The
Susan> query below works as expected when the table_schema includes
Susan> tables, however it shows nothing when the table_schema contains
Susan> only views. I tried putting the query in
> "Michel" == Michel Pelletier writes:
Michel> Replying to my own problem here, I guess this was a situation
Michel> where explaining it in detail revealed the problem to me. By
Michel> specifying my type is 'passedbyvalue'
That cannot possibly be appropriate.
Michel> CREATE TYPE matrix
> "andyterry" == andyterry writes:
andyterry> Hi,
andyterry> Using a procedure for the first time to run some processing
andyterry> for each row in a table, generating output to a target
andyterry> table. The following works without COMMIT the example below
andyterry> gives:
andyterr
> "Guillaume" == Guillaume Lelarge writes:
>> I will see about fixing this, somehow.
Guillaume> Thanks a lot.
I've committed a fix (to all supported branches, since this bug actually
precedes the addition of GROUPING SETS and can be triggered with a
simple GROUP BY if you try hard enough)
> "Egashira" == Egashira, Yusuke writes:
Egashira> Hi,
Egashira> I have a question about array_to_string().
Egashira> I think array_to_string() concatenates always array elements
Egashira> from the "beginning" of the array elements and this rule will
Egashira> not be changed on the fu
>>>>> "Andrew" == Andrew Gierth writes:
>>>>> "Guillaume" == Guillaume Lelarge writes:
Guillaume> CASE grouping(to_char(b, 'MM'))
Guillaume> ERROR: arguments to GROUPING must be grouping expressions of the
Guillaume&g
> "Guillaume" == Guillaume Lelarge writes:
Guillaume> CASE grouping(to_char(b, 'MM'))
Guillaume> ERROR: arguments to GROUPING must be grouping expressions of the
Guillaume> associated query level
Guillaume> LINE 3: CASE grouping(to_char(b, 'MM')) WHEN 1 THEN 'some date'
...
> "Олег" == Олег Самойлов writes:
Олег> Hi, all.
Олег> I got some mystic behaviour of PostgreSQL, perhaps this is a bug.
Feature, actually.
Олег> But things begin be strange if I add validation by time.
Олег> => explain select * from node as parent left join link on
Олег> parent.node_
> "Alexander" == Alexander Farber writes:
Alexander> even though I wonder what is the (VALUES
Alexander> ('20.20.20.20'::inet)) v(ip) construct there, some temporary
Alexander> table which is then LEGT JOINed to the geoip table?
The SQL spec calls it a . The VALUES clause
behaves like a S
> "Alexander" == Alexander Farber writes:
Alexander> However the following query does not work as intended and
Alexander> does not update any fields, because there is no matching
Alexander> block in the geoip table found:
Alexander> UPDATE users u SET
Alexander> visited = now(),
> "Alexander" == Alexander Farber writes:
Alexander> Good afternoon,
Alexander> for each visitor of my website I generate a JSON list of 30
Alexander> top players ( https://slova.de/words/top.php ), who played
Alexander> in the past week, with their average scores and average time
Alexa
> "Alexander" == Alexander Farber writes:
Alexander> Good evening, thank you for the useful hints!
Alexander> With the further help of the IRC folks the query has been
Alexander> optimized (7-10 seconds -> 0.3 second)
0.3 MILLIseconds, actually.
(You chanced not to catch me around on IR
> "AF" == Alexander Farber writes:
AF> Here are the only modified settings in postgresql.conf:
AF> max_connections = 120 # (change requires restart)
AF> work_mem = 8MB # min 64kB
AF> maintenance_work_mem = 128MB # min 1MB
AF> 90% of the backend source code are JSON-emitting
> "Ken" == Ken Tanzer writes:
>> If you absolutely can't change the column type, then one option
>> would be to do your own fixed-format date parsing function (and
>> label it immutable), e.g.
>>
>> create function iso_timestamp(text)
>> returns timestamp without time zone
>> as $$ se
> "Ken" == Ken Tanzer writes:
Ken> Hi. I've got a text field in a table that holds this style of
Ken> timestamp:
Ken> 2014-10-23T00:00:00
You can't make this a field of type "timestamp" rather than text?
Casts from text to either date or timestamp are mutable because they
depend on the
> "Zexuan" == Zexuan Luo writes:
Zexuan> For instance:
Zexuan> ```
Zexuan> with t as (
Zexuan> select set_config('blah', '1', false)
Zexuan> )
Zexuan> select current_setting('blah');
A CTE containing a SELECT query which is not referenced anywhere will
not be executed, even if
> "Mark" == Mark Mikulec writes:
Mark> To be honest this whole affair with COPY FROM program seems like
Mark> a bug to me though.
Perhaps you're misunderstanding what COPY FROM PROGRAM is actually for.
Its purpose is to do exactly what COPY does, that is to say, take as
input a file in eit
>>>>> "Andrew" == Andrew Gierth writes:
>>>>> "Rory" == Rory Campbell-Lange writes:
Rory> and data like this:
Rory> j
Rory> --
Rory> {"a": 1,
> "Rory" == Rory Campbell-Lange writes:
Rory> and data like this:
Rory> j
Rory> --
Rory> {"a": 1, "people": [{"id": 2002}, {"id": 2004}]}
Rory> {"a": 3, "people": [{"id": 20
> "Bret" == Bret Stern writes:
Bret> My statement below updates the pricing no problem, but I want it
Bret> to be formatted with 2 dec points eg (43.23).
UPDATE ...
SET suggested_retail_price = round(suggested_retail_price*1.13, 2)
WHERE ...
assuming suggested_retail_price is already
> "Johann" == Johann Spies writes:
Johann> How can I transform the following definition to index pubyear
Johann> as integer and not text?
Johann> CREATE INDEX pubyear_idx
Johann> ON some_table_where_data_field_is_of_type_jsonb USING btree
Johann> ((data -> 'REC'::text) -> 's
> "Mike" == Mike Martin writes:
Mike> Hi
Mike> For a particular sequence I needed to do (schedule 2nd monday in
Mike> month for coming year) I created the following query
That doesn't look like the best way - you're generating and discarding a
lot of rows.
"second monday in month X" can
1 - 100 of 120 matches
Mail list logo
