Hi. Thanks so much for your assistance. This is definitely getting the
results I was looking for. It is still syntacticallly more cumbersome than
I might have hoped, but I can work with it. So I've got two follow
questions/issues:
1) I can see there are many, more complex, options for aggrega
On Fri, Mar 2, 2018 at 4:41 PM, Adrian Klaver
wrote:
> On 03/02/2018 04:36 PM, Dale Seaburg wrote:
>
>> My mind is drawing a blank. Not sure where to go to find the answer.
>> Here is the statement in C#:
>>
>> sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE ";
>>
>> To finish off t
On Thu, Apr 19, 2018 at 12:21 PM, Tom Lane wrote:
> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= writes:
> > 2018-04-19 15:57 GMT-03:00 Tom Lane :
> >> (I'm not sure that this issue is adequately documented, though.
> >> I'd have expected to find something about it in triggers.sgml and/or
> >> creat
On Fri, May 4, 2018 at 1:03 PM, George Neuner wrote:
> On Thu, 3 May 2018 11:02:00 -0700, Adrian Klaver
> wrote:
>
> >On 05/03/2018 09:47 AM, George Neuner wrote:
> >>
> >> ..., I would not discount the possibility that Microsoft really
> >> has patented some variation of CSV. They absolutely d
On Sun, May 6, 2018 at 10:22 PM, George Neuner wrote:
> >> That's the patent database. Microsoft tried to get a *copyright*. I
>
>I don't think in general you can copyright a file format.
>
> And you absolutely can *patent* use of any data format for a given
> purpose [assuming the purpose it
On Fri, May 18, 2018 at 12:45 PM, Melvin Davidson
wrote:
>
>
> > I have used triggers to keep audit-logs of changes to certain columns in
> a table
> Another good use for triggers is to maintain customer balance..EG: An
> INSERT, UPDATE or DELETE involving a customer payment
> (or in the case of
On Tue, Jul 10, 2018 at 5:45 PM Adrian Klaver
wrote:
> select add_trigger('trg_test');
>
> test=> \d trg_test
>
> Table "public.trg_test"
>
>
> Column | Type| Collation | Nullable | Default
>
>
> +---+---+--+-
>
>
On Thu, Jul 19, 2018 at 11:35 AM Melvin Davidson
wrote:
> >> Politely tell them to buy some of the many well written books that are
> available on these very topics...
> >Fair enough but what about those that cant afford it? I think us in the
> Western World tend to forget that by >far the majori
On Thu, Jul 19, 2018 at 5:43 PM Melvin Davidson
wrote:
>
> > Then again people might use shared, university or library computers
> Would you please be so kind as to inform us which university or library
> allows users to install software on a _shared_ computer.
>
> Well, just sticking to a quick
Hi. As background/context, I'm working on a script to take a series of
databases and make them timezone-aware. This basically involves saving all
the view definitions, dropping all the views, changing all the timestamp
columns without time zones to TS with TZ, and then recreating all the
views.
On Mon, Jul 30, 2018 at 4:10 PM Adrian Klaver
wrote:
> On 07/30/2018 02:26 PM, Ken Tanzer wrote:
>
> > This doesn't impair the view's functionality, so I can't necessarily
> > complain. But it does make it harder for me to know if the views were
> > recre
On Mon, Jul 30, 2018 at 4:52 PM Adrian Klaver
wrote:
> On 07/30/2018 04:29 PM, Ken Tanzer wrote:
> > On Mon, Jul 30, 2018 at 4:10 PM Adrian Klaver
> > Thanks Adrian. I do have what are supposed to be the original view
> > definitions, but I'm less than 100% confi
Hi. I was recently troubleshooting a function, and realized it had
incorrectly been declared as Immutable, when it should have been declared
Stable. When I changed it to Stable, the query I was running ran
dramatically faster. Digging into this a little more, this is what I found:
I've got a fun
On Mon, Aug 6, 2018 at 4:11 PM Tom Lane wrote:
> Ken Tanzer writes:
> > Hi. I was recently troubleshooting a function, and realized it had
> > incorrectly been declared as Immutable, when it should have been declared
> > Stable. When I changed it to Stable, the qu
On Mon, Aug 6, 2018 at 4:36 PM Adrian Klaver
wrote:
>
> What is the definition for target_date()?
>
Hi Adrian. Happy to provide this info. Though on a side note, I don't
understand why it should matter, if functions are black box optimization
fences. But here are the definitions:
CREATE OR R
On Mon, Aug 6, 2018 at 7:42 PM Tom Lane wrote:
> Ken Tanzer writes:
> > Hi Adrian. Happy to provide this info. Though on a side note, I don't
> > understand why it should matter, if functions are black box optimization
> > fences.
>
> They aren't, at
On Mon, Aug 6, 2018 at 11:05 PM Laurenz Albe
wrote:
> Ken Tanzer wrote:
> > On Mon, Aug 6, 2018 at 4:11 PM Tom Lane wrote:
> > > Ken Tanzer writes:
> >
> > spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT
> client_id,si_i
On Tue, Aug 7, 2018 at 12:05 AM Ken Tanzer wrote:
> I'm definitely not understanding why or how auto-explain would help here.
> (Also, don't overlook the fact that both si_stable and si_imm have the
> exact same definition (except for stable v. immutable), and are calling
On Tue, Aug 7, 2018 at 9:10 AM Tom Lane wrote:
> I was confused about that too, so I set up a simple test case similar
> to Ken's and poked into it a bit, and what I found out is that nested
> SQL functions are just about completely broken performance-wise,
> unless one or the other gets inlined
Hi. My question is similar to one that was asked but apparently never
answered a couple of years ago on this list. (
https://www.postgresql.org/message-id/20160112023419.GA30965%40moraine.isi.edu
)
Basically, I'm wondering whether materialized views are likely to ever
support row-level security.
If I correctly understood what you wanted based on the SO description
("Something like: select position ('/s/' or '/b/' or '/t/' in URL)"),
you could do that by combining SUBSTRING with position. Something like:
SELECT position(substring(URL FROM '/(s|b|t)/') IN URL);
Cheers,
Ken
On Sun, Aug
>
>
> Gives 5. It's wrong.
>
>
> True. Though your SO example didn't have the https in it.
> For some reason, substring() returns the parenthesised subexpression
> rather than the top level..
>
> The comment in testregexsubstr does say that it does this, but it's not
> clear from the documentat
On Tue, Aug 14, 2018 at 5:24 PM Ken Tanzer wrote:
> Hi. My question is similar to one that was asked but apparently never
> answered a couple of years ago on this list. (
> https://www.postgresql.org/message-id/20160112023419.GA30965%40moraine.isi.edu
> )
>
> Basically, I&#
Hi David, and thanks for taking the time to respond.
On Mon, Aug 27, 2018 at 3:29 PM David Rowley
wrote:
>
> You might think there's some master project-wide list of things that
> are to implement that we all work from, but there's not.
>
>
I suppose I might have thought that, but I didn't. Tha
On Mon, Aug 27, 2018 at 4:19 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Aug 27, 2018 at 4:06 PM, Ken Tanzer wrote:
>
>> People on this list generally seem pretty generous in spirit and sharing
>> of their knowledge, insights and opinions. It se
On Mon, Aug 27, 2018 at 4:23 PM David Rowley
wrote:
>
> If I had to guess what's going on here then I'd say that nobody has
> been sufficiently motivated to work on this yet. If that's the case,
> everyone who reads your email is not the person working on this
> feature, so can't answer your ques
On Wed, Sep 19, 2018 at 6:34 AM Gabriel Furstenheim Milerud <
furstenh...@gmail.com> wrote:
Maybe that is not possible with numbers? To say in a format something like
> "my numbers have comma as decimal separator and no thousands separators" or
> "my numbers are point separated and have comma as t
Hi. I'm trying to update some databases from 9.6 to 11, and they use
table_log for tracking changes to tables.
I started with the most recent version I could find, a fork labeled version
0.5. [1]
This version compiles on both 9.6, and 10, but on 11 it fails with these
errors:
table_log.c: In f
On Fri, Jul 12, 2019 at 1:42 PM Adrian Klaver
wrote:
> On 7/12/19 1:19 PM, Ken Tanzer wrote:
> > Hi. I'm trying to update some databases from 9.6 to 11, and they use
> > table_log for tracking changes to tables.
> >
> > I started with the most recent vers
On Fri, Aug 16, 2019 at 7:24 AM rob stone wrote:
> Hello,
>
> On Fri, 2019-08-16 at 07:39 -0400, stan wrote:
> > What am I doing wrong here?
> >
>
>
> Your view assumes that all three "streams" contain all the proj_no's
> whereas your test data for expense_report_cost_sum_view has no proj_no
> =
On Fri, Aug 16, 2019 at 5:54 PM stan wrote:
>
> On Fri, Aug 16, 2019 at 12:30:33PM -0700, Ken Tanzer wrote:
> > On Fri, Aug 16, 2019 at 7:24 AM rob stone wrote:
> >
> > > Hello,
> > >
> > > On Fri, 2019-08-16 at 07:39 -0400, s
Hi. Using 9.6.14, I was setting up a table with this:
EXCLUDE using gist (EXCLUDE using gist (grant_number_codes with &&)
Where grant_numbers is a varchar[]. I get this error:
ERROR: data type character varying[] has no default operator class for
access method "gist"
HINT: You must specify a
On Fri, Aug 30, 2019 at 12:59 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:
> On 30/8/19 10:20 π.μ., Achilleas Mantzios wrote:
>
> Maybe take a look at
> https://dba.stackexchange.com/questions/185174/error-data-type-text-has-no-default-operator-class-for-access-method-gist
>
>
> So
On Wed, Sep 18, 2019 at 3:20 PM Ron wrote:
> Charging for *installing* PostgreSQL is not the same as charging for
> PostgreSQL.
>
> Bottom line: you charge for *services** you provide* not for software
> that other people provide.
>
> That's just really not true. There is nothing that prohibits
On Wed, Sep 18, 2019 at 5:55 PM Ron wrote:
> On 9/18/19 6:03 PM, Ken Tanzer wrote:
>
>
>
> On Wed, Sep 18, 2019 at 3:20 PM Ron wrote:
>
>> Charging for *installing* PostgreSQL is not the same as charging for
>> PostgreSQL.
>>
>> Bottom line: you c
On Wed, Sep 18, 2019 at 6:35 PM Ron wrote:
> On 9/18/19 8:26 PM, Ken Tanzer wrote:
>
> On Wed, Sep 18, 2019 at 5:55 PM Ron wrote:
>
>> On 9/18/19 6:03 PM, Ken Tanzer wrote:
>>
>>
>>
>> On Wed, Sep 18, 2019 at 3:20 PM Ron wrote:
>>
>>>
>
> Em qua, 5 de fev de 2020 às 23:55, Vik Fearing
> escreveu:
>
>>
>> Please answer +1 if you want or don't mind seeing transaction status by
>> default in psql or -1 if you would prefer to keep the current default.
>
>
+1
I liked the idea just reading about it, but thought it would be good form
Hi. Regardless of how I specify a daterange, it is converted to inclusive
lower bound, exclusive upper bound ('[)'):
SELECT daterange('2019-01-01','2020-01-01','(]') AS range;
range
-
[2019-01-02,2020-01-02)
So here's my question. Will the upper_inc function a
On Tue, Jul 28, 2020 at 2:24 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Jul 28, 2020 at 2:19 PM Ken Tanzer wrote:
>
>> So here's my question. Will the upper_inc function always return false
>> for a non-null daterange? And if so, wha
>
> > How to find what the primary key (or UNIQUE identifier) value is
> > for row 5 in the recordset?
>
> You're missing the point: as mentioned before, there is no "row 5". To
> update the 5th record that you've fetched, you increment a counter each
> time
> you fetch a row, and when you read #5,
On Fri, Sep 18, 2020 at 1:26 PM Ron wrote:
> On 9/18/20 3:18 PM, Igor Korot wrote:
>
Thank you for the info.
> My problem is that I want to emulate Access behavior.
>
> As I said - Access does it without changing the query internally (I
> presume).
>
> I want to do the same with PostgreSQL.
>
> I
On Fri, Sep 18, 2020 at 3:09 PM Igor Korot wrote:
>
> Now one other little thing: could you point me to the documentation that
> explains the meaning of the "window function"?
>
Can I point you to Google instead?
https://www.google.com/search?q=postgresql+window+functions
Cheers,
Ken
--
AG
Hello. This is probably simple, but I'm having a hard time making use of
some json data, and hoping someone can help.
Given some data that looks like this (I added a couple of carriage returns
for readability):
SELECT _message_body->'Charges' FROM message_import_court_case where
_message_exchan
On Mon, Dec 7, 2020 at 3:12 PM Steve Baldwin
wrote:
> Try:
>
> select _message_body->'Charges'->>'Name' from ...
>
>
Hi Steve. I tried that again, and that returns a NULL value for me. I
believe that is because Charges holds an array of two elements, each of
which has a Name element. Though my
On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston
wrote:
> On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin
> wrote:
>
>> Try:
>>
>> select _message_body->'Charges'->>'Name' from ...
>>
>
> Not so much..."Charges" is an array so "->>" doesn't do anything useful.
>
> The OP needs to use "json_array_el
On Mon, Dec 7, 2020 at 4:00 PM David G. Johnston
wrote:
> On Mon, Dec 7, 2020 at 4:49 PM Ken Tanzer wrote:
>
>>
>>
>> On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Mon, Dec 7, 202
On Mon, Dec 7, 2020 at 5:20 PM Rob Sargent wrote:
>
> On 12/7/20 6:17 PM, David G. Johnston wrote:
>
> On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent wrote:
>
>>
>> postgres=# select id, array_agg(fa) from (select id,
>> (jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id;
>>
On Mon, Dec 7, 2020 at 7:12 PM Steve Baldwin
wrote:
> How about this:
>
> b2bcreditonline=# select f.id, array_agg(t.key2) from foo as f,
> jsonb_to_recordset(js) as t(key2 text) group by f.id;
> id | array_agg
> +
> 2 | {r2k2val,r2k2val2}
> 1 | {r1k2val,r1k2val2}
On Mon, Dec 7, 2020 at 7:33 PM Ken Tanzer wrote:
>
> But this has a big advantage in that you can just add other fields to the
> query, thusly:
>
> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f,
> jsonb_to_recordset(js) as t(key2 text) group by f.id;
>
On Mon, Dec 7, 2020 at 8:16 PM Tom Lane wrote:
> Ken Tanzer writes:
> >> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f,
> >> jsonb_to_recordset(js) as t(key2 text) group by f.id;
>
> > After a little more thought and experimenting, I'm not so su
On Mon, Dec 7, 2020 at 8:45 PM Tom Lane wrote:
> Ken Tanzer writes:
> > On Mon, Dec 7, 2020 at 8:16 PM Tom Lane wrote:
> >> If foo.id is a primary key, it knows that the "group by" doesn't really
> >> merge any rows of foo, so it lets you get away
On Mon, Dec 7, 2020 at 9:54 PM Tom Lane wrote:
> Ken Tanzer writes:
> > There's one last piece of this query I'm clearly not getting though.
> Where
> > it says:
>
> > from foo as f, jsonb_to_recordset(js) as t(key2 text)
>
> > what is actually g
On Thu, Jan 21, 2021 at 5:52 AM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:
> create database is to create a new database. If we switch to new
> database, we need to change the new databasename in the program(s) that
> access this database.
>
> If you are just concerned about having t
On Thu, Jan 21, 2021 at 11:12 PM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:
> So the solution to the issue will be
>
> 1. Backup a DB using PGDUMP from 1st server.
> 2. Restore the DB in 2nd server.
> 3. Make required changes in the 2nd server.
> 4. Backup that DB using PGDUMP fro
Hi. I'm trying to do a data dump with pg_dump using RLS and --set-role,
but am getting an error, and I'm not understanding why. With this command,
run as postgres:
pg_dump -p 5433 -O --role=rcafe_TACOMA --enable-row-security
--column-inserts -a -f ~/ag_tacoma_data.pg_dump ag_rcafe
I get
[Multipl
On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent wrote:
>
> >
> > There is only one schema, public.
> >
> >
>
> I suspect it is because "set role" doesn't "set search_path"
>
>
> I'm not sure what you mean or are suggesting by that. Is there something
I'm supposed to do to set the search path? Is th
On Thu, Feb 18, 2021 at 8:44 PM Tom Lane wrote:
> Ken Tanzer writes:
> > I'm not sure what you mean or are suggesting by that. Is there something
> > I'm supposed to do to set the search path? Is that a known bug in
> > pg_dump? Something else? As ment
On Fri, Feb 19, 2021 at 3:22 PM Tom Lane wrote:
> Ken Tanzer writes:
> > On Thu, Feb 18, 2021 at 8:44 PM Tom Lane wrote:
> >> There was a security change to pg_dump a few years ago to make it
> >> put "set search_path = pg_catalog" into the dump script. Th
On Fri, Feb 19, 2021 at 4:21 PM Tom Lane wrote:
>
> Yeah, that would be slightly safer. If the public schema is
> world-writable, though, you're in big trouble anyway ...
>
>
Sorry, you lost me with the last sentence. My scenario is that public
_isn't_ world-writable. But everyone can set thei
On Thu, Mar 10, 2022 at 12:12 AM Scott Macri wrote:
I've verified the data a 100 times and am totally perplexed. Thanks.
>
> SELECT *
> FROM public.map_table
> WHERE company_letters = 'abcdefg';
>
>
Not sure what kinds of verification you did, but did you check to make sure
that there's no trail
On Thu, Sep 1, 2022 at 4:09 PM Peter wrote:
>
> ! It produces failures:
> !
> ! could not change directory to "/home/jwalton/godojo": Permission
> denied
>
> ... this appears to me as rather a sudo issue. Because certainly
> psql cannot execute /as user postgres/ in a directory where user
>
On Thu, Nov 17, 2022 at 2:30 PM Alejandro Baeza Rangel <
jlabaezaran...@gmail.com> wrote:
> Buenas tardes, tengo esta tabla
> [image: image.png]
> como puedo en un report, representar la columna tiempo
> ya que le pongo directo un print y me sale:
> [image: image.png]
>
> alguna sugerencia?
>
As
On Sat, Nov 19, 2022 at 2:39 AM Alban Hertroys wrote:
>
>
> > don't fully understand it. But what really confuses me is the example
> below. How can these two intervals be equal and still yield different
> output in the to_char function? And as a practical matter, and for the OPs
> question, h
On Fri, Jan 6, 2023 at 3:32 PM Brad White wrote:
> I can generate ALTER statements, per David's sensible suggestion, but they
> fail because you have to drop all the views.
>
> Altering the defaults seems safe because the default value
> shouldn't affect the view at all.
>
Are you sure those a
On Thu, Jan 12, 2023 at 7:08 AM Tom Lane wrote:
What Postgres actually stores for an interval is three fields:
> months, days, and microseconds.
Is there a way to view/extract this raw data for any given interval?
(I'm asking because of an issue that came up about intervals that were
"equal bu
(resending--Martin didn't realize you hadn't sent to the list too.)
On Fri, Jan 13, 2023 at 2:28 PM Martin L. Buchanan <
martinlbucha...@gmail.com> wrote:
> Dear Ken:
>
> You can extract individual subfields of interval as described here:
>
>
> https://www.postgresql.org/docs/14/functions-datetim
On Fri, Jan 13, 2023 at 2:41 PM Adrian Klaver
wrote:
> > (I'm asking because of an issue that came up about intervals that were
> > "equal but not identical.")
>
> 1) Can you provide an example?
>
>
Here's an example. Note that they come out formatted differently with
to_char, but evaluate as e
On Fri, Jan 13, 2023 at 3:03 PM Adrian Klaver
wrote:
>
> WITH inters AS (
> SELECT
> '1 day 2 hours'::interval AS i1,
> '26 hours'::interval AS i2,
> justify_interval('1 day 2 hours'::interval) AS ij1,
> justify_interval('26 hours'::interval) AS ij2
> )
>
On Fri, Jan 13, 2023 at 3:44 PM Adrian Klaver
wrote:
> If I am following what you want is to_char(,'HH24:MM:SS') to
> be equal, correct?
>
Not really. My original question was:
[since intervals are stored internally as months, days and microseconds...]
> What Postgres actually stores for an i
On Fri, Jan 13, 2023 at 4:57 PM Tom Lane wrote:
>
> Given what extract() provides,
>
> stored months = years * 12 + months
>
> stored days = days
>
> stored usec = reconstruct from hours+minutes+seconds+microseconds
>
> Perhaps it wouldn't be a bad idea to provide a couple more extract()
> keywor
On Fri, Jan 13, 2023 at 9:27 PM Adrian Klaver
wrote:
> On 1/13/23 17:24, Ken Tanzer wrote:
>
> > -[ RECORD 1 ]-+---
> > i1| 1 day 02:00:00
> > i2| 26:00:00
> > i1_months | 0
> > i1_days | 1
> > i1_
On Thu, Feb 16, 2023 at 9:43 AM Dominique Devienne
wrote:
> Hi. I have a large "legacy" code base that write information necessary for
> Row-Level-Security in a highly denormalized custom had-hoc text format for
> values, in key-value pairs in a table, which would be either impossible or
> too sl
On Thu, Nov 1, 2018 at 5:08 PM Rich Shepard
wrote:
> On Thu, 1 Nov 2018, Rich Shepard wrote:
>
> > I'll use gawk to extract the relevant fields from the text file in which
> > they reside (in the correct order), then use emacs keyboard macros to add
> > the appropriate update text to each line. M
Hi. Building on the [type]_larger and _smaller functions (and lifting from
the documentation), I put together a couple of functions that will take any
number of arguments:
CREATE FUNCTION largest(VARIADIC anyarray) RETURNS anyelement AS $$
SELECT max($1[i]) FROM generate_subscripts($1, 1) g(i);
$
On Wed, Nov 7, 2018 at 2:46 PM Ondřej Bouda wrote:
> Hi,
>
> > 2) Is there any particular reason functions like that aren't built
> > into Postgres? They seem like they would be useful. (Or maybe I
> > missed them?)
>
> LEAST() and GREATEST() expressions do the same thing as yours smallest()
On Wed, Nov 7, 2018 at 9:48 PM Pavel Stehule
wrote:
>
> You can pass variadic arguments as a array
>
> postgres=# \sf smallest
> CREATE OR REPLACE FUNCTION public.smallest(VARIADIC anyarray)
> RETURNS anyelement
> LANGUAGE sql
> IMMUTABLE
> AS $function$
> SELECT min($1[i]) FROM generate_s
On Wed, Nov 7, 2018 at 10:10 PM Andrew Gierth
wrote:
>
> But you don't need to create more functions, because you can do this:
>
> select largest(variadic array[1,2,3]);
> largest
> -
>3
>
>
> As already pointed out, greatest() and least() exist (though they were
> added before
On Thu, Nov 8, 2018 at 7:01 AM Pavel Stehule
wrote:
> postgres=# select smallest(VARIADIC ARRAY[1,2,3]);
>>> ┌──┐
>>> │ smallest │
>>> ╞══╡
>>> │1 │
>>> └──┘
>>> (1 row)
>>>
>>>
>> That's very helpful and good to know. It's too bad that doesn't work with
>> LEAST/
On Fri, Dec 7, 2018 at 5:21 AM Thomas Kellerer wrote:
> Thomas Kellerer schrieb am 07.12.2018 um 13:48:
> > Chris Wilson schrieb am 07.12.2018 um 13:39:
> >> However, if we try to invert it by using the != operator, then we get
> unexpected results:
> >>
> >> select * from foo where id NOT IN (1,
On Tue, Dec 18, 2018 at 5:51 AM Adrian Klaver
wrote:
> On 12/17/18 11:14 PM, Bret Stern wrote:
> > My statement below updates the pricing no problem, but I want it to be
> > formatted with 2 dec points eg (43.23).
> >
> > Started playing with to_numeric but can't figure it out. Lots of
> > exampl
Hi. I've got a text field in a table that holds this style of timestamp:
2014-10-23T00:00:00
I'd like to be able to create an index on the date portion of this field
(as a date), because I have lots of queries that are searching for
particular dates or ranges.
I've tried various ways of getting
On Fri, Jan 4, 2019 at 2:27 PM Adrian Klaver
wrote:
> On 1/4/19 2:21 PM, Ken Tanzer wrote:
> >
> > I've tried various ways of getting to a date (::date, LEFT(x,10)::date,
> > etc.), but all of them throw the error "functions in index expression
> > must be
On Fri, Jan 4, 2019 at 2:54 PM Andrew Gierth
wrote:
> >>>>> "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 mak
On Fri, Jan 11, 2019 at 3:25 PM Rob Sargent wrote:
>
> On 1/11/19 4:21 PM, Rich Shepard wrote:
> > On Fri, 11 Jan 2019, Rob Sargent wrote:
> >
> >>> psql:activities.sql:2: ERROR: invalid input syntax for type date: ""
> >>> LINE 2: ...reaction they''ve experienced environmental issues.','','');
>
>
> Ron,
>
> All of you who responded drove home my need to explicitly enter null when
> there are no data for a column.
>
> Thanks,
>
> Rich
>
>
Just in case you miss this little nuance, you don't necessarily _have_ to
specify a NULL for that column, depending how you're doing your inserts.
You
>
>
> Ken,
>
> Well, you've succeeded in confusing me. :-)
>
> And this is the framwork for adding rows:
>
> insert into Activities (person_id,act_date,act_type,notes,next_contact)
> values
> (
>
> I add values for each column, but if there's no scheduled next_contact date
> I left that off. To me,
On Tue, Feb 12, 2019 at 2:48 PM Rich Shepard
wrote:
> On Tue, 12 Feb 2019, Rich Shepard wrote:
>
> > A.next_contact = (select (max(A.next_contact)) from Activities as A)
>
> Errata:
>
> The parentheses around the max aggregate are not necessary.
>
> A.next_contact now displays at the end of
>
>
> Ken,
>
> Yes, cheers indeed. A bit of thinking and re-organizing resulted in a
> working statement that's close to what I want:
>
> select (P.person_id, P.lname, P.fname, P.direct_phone, O.org_name,
> (select max(A.next_contact)))
> from People as P, Organizations as O, Activities as
Hi. I recently installed PG 11.2 on Centos 7, following the excellent
directions at https://www.postgresql.org/download/linux/redhat/.
I wanted to enable data-checksums. I at first tried appending
--data-checksums to the doc-specified command:
/usr/pgsql-11/bin/postgresql-11-setup initdb --data
On Sun, Feb 24, 2019 at 5:03 PM Adrian Klaver
wrote:
> On 2/24/19 2:39 PM, Ken Tanzer wrote:
> I'm just wondering if there is a more preferred way to do this,
Not seeing anything responsive to this question, I'll assume that
PGSETUP_INITDB_OPTIONS
is the preferred method.
On Mon, Feb 25, 2019 at 1:35 PM Adrian Klaver
wrote:
> If I am following correctly, what you want is something
> like the below from pg_ctl, correct?:
>
> https://www.postgresql.org/docs/11/app-pg-ctl.html
>
> pg_ctl init[db] [-D datadir] [-s] [-o initdb-options]
...
> > > checksums are go
On Fri, Mar 15, 2019 at 11:59 AM Adrian Klaver
wrote:
> On 3/15/19 11:54 AM, basti wrote:
> > this is a dns database, and the client is update the _acme-challenge for
> > LE certificates. I don't want that the client can insert "any" txt
> record.
> > the client should only insert data if the hos
On Fri, Mar 15, 2019 at 4:42 PM Adrian Klaver
wrote:
> > Just curious, but wanted to follow up on whether rules are
> > across-the-board discouraged? I've seen disparaging comments about
> > them, but I don't see any indication of that on the create rule page.
>
> See here:
> https://www.postgre
On Thu, Mar 28, 2019 at 3:59 PM Rich Shepard
wrote:
>
> After working with this query I modified it slightly to return only the
> next_contact date:
>
> select p.person_id, p.lname, p.fname, p.direct_phone, p.active,
> o.org_name, sq.*
> from people as p
> join organizations as o on p.org_i
On Thu, Mar 28, 2019 at 4:07 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Mar 28, 2019 at 4:05 PM Ken Tanzer wrote:
>
>>
>> (And I don't think the one inside the lateral join is doing you any
>>> good). Try:
>>>
>&g
On Thu, Mar 28, 2019 at 4:14 PM Rich Shepard
wrote:
> On Thu, 28 Mar 2019, Ken Tanzer wrote:
>
> > You need the ORDER BY in the outer join.
>
> Ken,
>
> I thought so. But, ...
>
> > (And I don't think the one inside the lateral
On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver
wrote:
> On 4/19/19 11:14 AM, Rich Shepard wrote:
> > On Fri, 19 Apr 2019, Adrian Klaver wrote:
> >
> >> If it is working for you now I see no reason to switch.
> >
> > Adrian,
> >
> > It does work. I just learned about the SQL identity type and want
On Fri, Apr 19, 2019 at 12:02 PM Adrian Klaver
wrote:
> On 4/19/19 11:32 AM, Ken Tanzer wrote:
> > On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 4/19/19 11:14 AM, Rich Shepard wrote:
> > >
On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver
wrote:
> On 4/19/19 12:35 PM, Ken Tanzer wrote:
>
> >
> > Thanks Adrian. You are as usual correct. (I had a bunch of tables
> > created by a function that I assumed were serial, but were not.)
> > Identity colu
On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver
wrote:
> On 4/19/19 1:02 PM, Ken Tanzer wrote:
> > On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 4/19/19 12:35 PM, Ken Tanzer wrote:
> >
> >
1 - 100 of 126 matches
Mail list logo
