On Tue, Dec 4, 2018 at 1:29 PM Martin Mueller
wrote:
> I have asked this question before and apologize for not remembering it. How
> do you do simple division in postgres and get 10/4 with decimals?
> This involves cast and numeric in odd ways that are not well explained in the
> documentation.
On Tue, Dec 4, 2018 at 1:38 PM Joshua D. Drake wrote:
> I may be misunderstanding the question but:
Indeed...
> select cast(x/y as numeric(10,4));
Your answer is 2.0 instead of the correct 2.5 - you need to cast
before the division, not after.
David J.
On Tue, Dec 4, 2018 at 1:57 PM Martin Mueller
wrote:
>
> I didn't formulate my question properly, because the query went like
> "select alldefects /wordcount"
> where alldefects and wordcount are integers. But none of the different ways
> of putting the double colon seemed to work.
IDK...the
On Sunday, December 9, 2018, bhargav kamineni wrote:
>
> What happens if we create and insert/update the data in TEMP tables , Does
> that data really gets inserted at disk level or at buffer level
>
Disk
> and what happens to this data after completion of the transaction ?
>
Your choice. See
On Mon, Dec 17, 2018 at 1:20 PM Rich Shepard wrote:
>
> On Mon, 17 Dec 2018, Melvin Davidson wrote:
>
> > Yes, you must drop then add the revised constraint. However, from your
> > statement above, it sounds to me as if you would be better off using A
> > FOREIGN kEY CONSTRAINT. It makes things a
On Tue, Dec 18, 2018 at 2:44 PM Steven Lembark wrote:
> > there is a \if statement
>
> Q: What is the "abort cycle command"?
[...]
> \if :lacks_rows
>
> \echo foobar lacks rows to process.
> \echo goodnight :-)
>
> ?<--- what goes here to stop execution?
\q
On Saturday, December 29, 2018, Glenn Schultz wrote:
> All,
>
> I need to initialize a variable and then use it in query. Ultimately this
> will part of a recursive CTE but for now I just need to work this out. I
> followed the docs and thought I needed something like this. But does not
> work
On Saturday, December 29, 2018, Ray O'Donnell wrote:
>
> A couple of things off the top of my head:
Sorry but, no.
>
> (i) I think you need "language plpgsql" (or whatever) after the DO block.
As the docs state plpgsql is the default for a DO block lacking a language
specifier.
> (ii) That
On Friday, December 28, 2018, Mitar wrote:
>
> On Fri, Dec 28, 2018 at 9:36 PM Adrian Klaver
> wrote:
> > When you create the temporary function it is 'pinned' to a particular
> > session/pg_temp_nn. Running the trigger in another session 'pins' it to
> > that session and it is not able to see th
On Monday, December 31, 2018, Mark Mikulec wrote:
>
> Does anyone know how to make COPY FROM PROGRAM take the output literally?
>
Not that I can think of. I’d avoid COPY FROM PROGRAM and move the logic to
psql. Roughly: \set varname `cmd`; select :’varname’;
David J.
On Tuesday, January 1, 2019, Chuck Martin wrote:
> results wanted-all transactions where account total >= 50
>
> id. amount. accountid.name
> 1. 50. 1 bill
> 3. 75. 1 bill
> 4 25.
On Tuesday, January 1, 2019, David G. Johnston
wrote:
> On Tuesday, January 1, 2019, Chuck Martin
> wrote:
>
>> results wanted-all transactions where account total >= 50
>>
>> id. amount. accountid.name
>> 1. 50. 1
On Tuesday, January 1, 2019, Adrian Klaver
wrote:
> On 1/1/19 11:26 AM, John W Higgins wrote:
>>
>> with accounts_over_total as (
>> select accountid from transactions where sum(amount) >= 50 group by
>> accountid)
>>
>
> Unfortunately there is a hitch in the above:(
>
> select p_item_no from pro
On Wednesday, January 2, 2019, Rich Shepard
wrote:
>
> CREATE DOMAIN state_code AS char(2)
> DEFAULT '??'
> CONSTRAINT valid_state_code
> CHECK (value IN ('AL', 'AK', 'AZ', ...));
>
> This applies to all tables each having a column named state_code.
>
There is no magic name logic involved. A do
On Wednesday, January 2, 2019, Rich Shepard
wrote:
> On Wed, 2 Jan 2019, David G. Johnston wrote:
>
> There is no magic name logic involved. A domain is just a type with
>> inherent constraints that are user definable. You make use of it like any
>> other type.
&
On Wednesday, January 2, 2019, Rich Shepard
wrote:
> On Wed, 2 Jan 2019, David G. Johnston wrote:
>
> I'm not following you. I have two tables each with a column,
>>> state_code char(2) NOT NULL.
>>>
>>
> That is a char(2) column for which ‘??’ is a v
On Thursday, January 3, 2019, Glenn Schultz wrote:
> All,
>
> I am writing a query to select * from where ~ '[regx] an example of the
> sting that I am matching is below
>
> FHLG16725
>
> The first two alpha characters to match are FN, FH, GN any alpha
> characters between those and the numeric
On Friday, January 4, 2019, Rob Sargent wrote:
>
> CHECK(end_date is null or start_date <= end_date)
>
The is null expression is redundant since check constraints pass when the
result is unknown.
David J.
On Friday, January 4, 2019, Rich Shepard wrote:
> On Fri, 4 Jan 2019, David G. Johnston wrote:
>
> The is null expression is redundant since check constraints pass when the
>> result is unknown.
>>
>
> David,
>
> I wondered about this since NULL can be missing
On Friday, January 4, 2019, Rich Shepard wrote:
> On Fri, 4 Jan 2019, David G. Johnston wrote:
>
> I wondered about this since NULL can be missing, unknown, or otherwise
>>> defined. Are there benefits to allowing an empty value in that column
>>> when
>>&g
On Fri, Jan 4, 2019 at 2:21 PM Rich Shepard wrote:
>Thinking more about duration perhaps I'm seeing a problem that really does
> not exist: it's a single column for both dates in the table while the UI
> needs separate date data entry widgets. Unless I use middleware code when a
> project row
On Fri, Jan 11, 2019 at 5:01 PM Rich Shepard wrote:
> On Fri, 11 Jan 2019, Ken Tanzer wrote:
> > \copy my_test FROM test.csv WITH CSV HEADER
> >
> > ERROR: invalid input syntax for type date: "''"
> > CONTEXT: COPY my_test, line 4, column my_date: "''"
Right problem wrong solution since it appe
On Fri, Jan 11, 2019 at 3:56 PM Rich Shepard wrote:
>
> A table has this column definition:
>
> next_contact date DEFAULT '2020-11-06'
> CONSTRAINT valid_next_date
> CHECK (next_contact >= CURRENT_DATE),
>
> (and I don't know that it needs a default).
The default d
On Fri, Jan 11, 2019 at 4:25 PM Rob Sargent wrote:
> We don't have more context in "activities.sql" but if your OP was
> verbatim, it's keeling over on the comma ending the long text string.
> Something syntactically askew I think.
If the problem was where you described the parser would never hav
On Sat, Jan 12, 2019 at 6:43 AM Rich Shepard wrote:
>
> On Sat, 12 Jan 2019, Ricardo Martin Gomez wrote:
>
> > Hi, one question.
> > Do you put explicit "NULL" in the column value?
> > Other option is.
> > You have to delete the column "next_contact" in your INSERT clause.
> > So, if the column ha
On Sat, Jan 12, 2019 at 9:01 AM Rich Shepard wrote:
>
> On Sat, 12 Jan 2019, David G. Johnston wrote:
>
> > Actually, you didn't ask about the check constraint, which is actually
> > horribly broken since current_date is not an immutable function.
>>
>I kno
On Sat, Jan 12, 2019 at 10:08 AM Ricardo Martin Gomez
wrote:
>
> Hi,
> In MYSQL Null date is equal '01/01/1900' or '01/01/1970', I don't remember
> but you can also use the same logic for the check_constraint.
> Regards
Why? PostgreSQL doesn't need hacks around this...
David J.
On Sat, Jan 12, 2019 at 9:54 AM Rich Shepard wrote:
> > The problem is that check constraints are only applied at time of data
> > change.
>I thought that the check constraint applied at data entry, too.
Inserting new data into a table qualifies as "data change" in my mind...
David J.
On Tue, Jan 15, 2019 at 8:48 AM Andreas Joseph Krogh
wrote:
> Hi all.
>
> I'm trying to use a psql variable in a DO-block, but it fails:
>
[...]
> Any hints?
>
Don't do that. The body of a DO block is a string literal and psql won't
and shouldn't mess with its contents. You'll need to use an
On Tue, Jan 15, 2019 at 9:42 AM Alexander Farber
wrote:
> When I am trying
>
> WITH diffs AS (
> SELECT
> gid,
> uid,
> played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
> FROM moves
> WHERE uid = 1
> )
> UPDATE users SET
> avg_time = TO_CHAR(AVG(diff), 'H
On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber
wrote:
>> So calculate the average somewhere else, put the result in a column,
>> and then reference that column in the SET clause.
>>
>
> do you suggest to add a second CTE?
That would qualify as "somewhere else" - as would a simple subquery in FR
On Tue, Jan 15, 2019 at 12:42 PM Alexander Farber
wrote:
>
> Last question please - how to run the query for all users?
>
> I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL
> way?
>
> How to refer to the outside "uid" from inside the CTE in the query below?
>
> WITH dif
On Tue, Jan 15, 2019 at 12:42 PM Ramamoorthi, Meenakshi
wrote:
>
> Dear folks:
> 1) Can someone please send me a link of all companies using PostgreSQL ?
>
> 2) Both government and private companies using PostgreSQL
>
> 3) Any security issues found earlier and the steps taken for reso
On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu wrote:
>
> Hello,
> I am a bit confused about the settings in pgbouncer
>
> What's exactly the pool_size?
Roughly, the number of open connections pgbouncer will keep to PostgreSQL.
> If I set 3, and I tried to connect from 4 shells, I am still able to
On Thu, Jan 17, 2019 at 8:39 AM Rich Shepard wrote:
>
> On Thu, 17 Jan 2019, Rich Shepard wrote:
>
> > I want only to confirm that I can use /* ... */ for multiline comments in my
> > DDL and DML scripts.
>
> Oops! Section 4.1.5 tells me that I can.
>
You could also just try it...
select 1 /* co
On Thu, Jan 17, 2019 at 9:06 AM Fabio Pardi wrote:
> Are you sure? The behaviour I experienced is different from what you
> described.
85%...
>
> On 17/01/2019 16:32, David G. Johnston wrote:
> > On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu wrote:
> >>
> >
On Thu, Jan 17, 2019 at 9:14 AM Rich Shepard wrote:
> ORDER BY c.contact_id, a.next_contact DESC;
> The WHERE clause needs to exclude a contact_id where the most current row in
> Activities has NULL for the next_contact column.
Why is next_contact allowed to be null?
Your concept of "most curre
On Thu, Jan 17, 2019 at 9:47 AM Rich Shepard wrote:
> What I want is a list of contacts to make today. This includes ones that
> should have been made earlier but weren't and excludes earlier contacts that
> have no scheduled next contact (therefore, the nulls.).
>
> Does this clarify what I'm ask
On Thu, Jan 17, 2019 at 10:07 AM Rich Shepard wrote:
>The direct answer is that a completed activity has a row with either a
> future next-activity date or a null (which is the case when the status of
> that organization or contact is 'no further contact'.)
Off the top of my head (and this is
On Thu, Jan 17, 2019 at 9:19 AM Ben Snaidero wrote:
> Any ideas as to why this is happening?
Not really, I would expect roughly double execution time, not an
exponential increase. Still not experienced enough to diagnose with
what has been provided but I will suggest you provide the version that
On Thu, Jan 17, 2019 at 3:44 PM Rich Shepard wrote:
> FROM People AS p
> JOIN Organizations AS o ON p.org_id = o.org_id
> JOIN Contacts AS c ON c.person_id = p.person_id
I would advise changing Contacts to "Activities" as the former can
readily be interpreted (and is in the wild) as b
On Thu, Jan 24, 2019 at 4:04 AM Alexandre GRAIL
wrote:
> But you *cannot* use 1 or 0 as valid input for boolean type when
> inserting or updating :
>
> test=# CREATE TABLE test1 (a boolean);
> CREATE TABLE
> test=# INSERT INTO test1 VALUES (1);
> ERROR: column "a" is of type boolean but expressio
On Friday, February 1, 2019, rob stone wrote:
> Hello,
>
> On Fri, 2019-02-01 at 14:01 +, Brad Leupen wrote:
> > CREATE TABLE foo (
> > tenant TEXT REFERENCES tenant NOT NULL DEFAULT user,
>
> > This script winds up failing because the “user” default value on
> > foo.tenant evaluates to t
On Sun, Feb 3, 2019 at 10:35 PM Syed Jafri wrote:
> · Receiver names are of the type (a-z, 1-5, .)
>
> · 95% of all queries currently look like this: SELECT * FROM table
> WHERE Receiver = Alpha, with the new format this would be SELECT * FROM table
> WHERE receivers @> '"Alpha"'
On Thursday, February 7, 2019, Steve Wampler wrote:
>
>(1) the table already exist and the immediately doesn't exist?
>(2) report ERROR on UPDATE when there are no UPDATES in the input file
>
Most likely the first attempt was schema qualified and so found the
existing targets table whil
On Mon, Feb 11, 2019 at 12:29 PM Steve Wampler wrote:
> Thanks - but I thought the search_path update was a PG 10 change and so
> shouldn't reflect on 9.5.15 behavior. Did it
> get back-ported?
Yes, it was deemed a security vulnerability and thus back-patched.
Release notes will indicate when t
On Mon, Feb 11, 2019 at 3:44 PM Rich Shepard wrote:
> Now I know to replace no dates with null I'll do so but I'm curious why this
> is needed.
Same reason you needed it about a month ago when you were dealing with
a check constraint question with the same error message.
David J.
On Tue, Feb 12, 2019 at 3:24 PM Rich Shepard wrote:
> psql:next_contact_date.sql:7: ERROR: syntax error at or near "select"
> LINE 4: A.next_contact = select (max(A.next_contact) from A)
> ^
> and I fail to see what I've done incorrectly.
You put the open pa
On Tuesday, February 19, 2019, Rob Nikander wrote:
> Are procedures not allowed to commit/rollback if they are called within
> in an outer transaction?
>
https://www.postgresql.org/docs/11/sql-call.html
Also, I tried putting a `start transaction` command in the procedure. I got
> another erro
On Wednesday, February 27, 2019, Andy Fan wrote:
>
> The following way works with 2 commands:
>
> zhifan=# create table t1 (a jsonb);
> CREATE TABLE
> zhifan=# create unique index t1_a_name on t1 ((a->'name'));
> CREATE INDEX
>
> but know I want to merge them into 1 command, is it possible?
>
> z
On Thursday, February 28, 2019, Rob Sargent wrote:
> but this fails in one of two ways: either the create function call fails
> lacking a definition of "segment" or, if I create a public.segment table,
> create the function, set search_path to include a project's schema then
> drop public.segmen
On Thu, Feb 28, 2019 at 1:56 PM Rob Sargent wrote:
> HINT: No function matches the given name and argument types. You might need
> to add explicit type casts.
Pretty sure you need to heed the advice to make it work.
select pvr(s::public.segment) from mm.segment AS s limit 5;
David J.
On Thu, Mar 14, 2019 at 4:07 PM Steve Haresnape
wrote:
>
> I'm porting a sql server database to postgresql 9.6. My camelCase identifiers
> are having their humps removed. This is disconcerting and sad.
>
> Is there a cure for this?
No
>I don't want to quote my identifiers unless I have to.
Pos
On Thursday, March 14, 2019, M Tarkeshwar Rao
wrote:
>
> Facing issue in using special characters. We are trying to insert records
> to a remote Postgres Server and our application not able to perform this
> because of errors.
>
> It seems that issue is because of the special characters that has b
On Wed, Mar 27, 2019 at 8:58 AM Mike Martin wrote:
> Scenario being two tables with a linking pair of fields, where one table
> is a superset of the other and key is generated by the second table
>
> I would like to have the record in table two deleted when I delete the
> record in table 1
>
Sin
On Thu, Mar 28, 2019 at 3:59 PM Rich Shepard
wrote:
> 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_id = o.org_id
> cross join
> lateral
> (select a.next_contact
>
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:
>>
>
> ...
> a.next_contact is not null
> limit 1) sq
> order by sq.next_contact DESC;
>
>
The fact that the subquery h
On Thu, Mar 28, 2019 at 4:21 PM Rich Shepard
wrote:
> This does not return the
> desired order:
>
It returns something at least. If you put the output you get into a
spreadsheet are you able to manually sort it the way you desire?
David J.
On Wed, Apr 17, 2019 at 3:04 PM Souvik Bhattacherjee
wrote:
> Hello,
>
> I'm trying to pass some values between queries in a multi-statement
> transaction. For example, consider the following representative
> multi-statement transaction:
>
> begin;
> select * from table1 t1, table2 t2 where t1.ci
On Thursday, April 18, 2019, Vikas Sharma wrote:
>
> The above was run without where clause.
>
There was no where clause thus every row has to be updated by
definition...null and bool evaluates to either false or null since if bool
is false the null doesn’t matter and if bool is true the result
On Thu, Apr 18, 2019 at 9:03 AM Souvik Bhattacherjee
wrote:
> Thanks Michel.
>
> However this only works if a is an unique attribute in the table that
> would help us to identify tuples that participated in the join. Consider
> the following join:
>
> insert into table3 (id, level, empname, salar
On Wed, Apr 24, 2019 at 1:17 PM pabloa98 wrote:
> Hello
>
> Sadly today we hit the 1600 columns limit of Postgresql 11.
>
> How could we add more columns?
>
Add a second table and relate them together via a one-to-one-required
relationship?
I'm unsure whether a final result set is limited or ju
On Fri, Apr 26, 2019 at 2:25 PM Steven Schlansker <
stevenschlans...@gmail.com> wrote:
> How can I efficiently implement the feature I've described? It seems
> difficult to use computed indexing with GIN.
>
Don't use a computed index? Add a trigger to populate a physical column
and index that.
On Wed, May 1, 2019 at 9:27 AM Chuck Martin
wrote:
> I need help figuring out why a query is not returning the records I expect
> it to. I'm searching on a DateTime column (timestamp without time zone -
> not nullable). The query includes:
>
> AND event.Primaryresp_fkey = 511 AND event.DateT
On Thursday, May 9, 2019, Chuck Martin wrote:
> I have several columns that were created as "timestamp without time zone",
> but I changed them in 2014 to "timestamp with time zone". Recently, when I
> got notified that times had suddenly changed, I checked and found the
> columns had reverted to
On Fri, May 10, 2019 at 3:46 PM Susan Hurst
wrote:
> Why would schemas/tables/columns be missing from
> information_schema.tables/information_schema.columns?
>
The user you are using to check information_schema doesn't have permissions
on the objects in question?
David J.
On Mon, May 13, 2019 at 11:24 PM magodo wrote:
> I found when running command like `# echo "xxx" | psql postgres
> postgres`, the return code is always 0 even though the command ("xxx")
> here is of invalid syntax. While the `psql -c` way handled exit code
> correctly.
>
Its only required to han
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura
wrote:
> However, since I have modified template1 (and most likely, those databases
> were copied from it in the past), it changed default_parameter for them as
> well.
>
This seems unlikely to be the case - changing template1 should not be
affecti
On Thu, May 16, 2019 at 8:31 AM Daulat Ram
wrote:
> url_hash| bigint | | not null |
>
Change the type of url_hash; make it text instead of bigint.
As a bonus:
Use text instead of arbitrary varchar(4000) fields and add, e.g., check
(length(url) < 4000) or some
On Thu, May 16, 2019 at 9:36 AM wrote:
> Hi,
>
> I've just inherited an ancient install of 9.1.17 after our tech guy left,
> on what turns out to be a rapidly dying server and being a total newb to
> PostgreSQL (and not much more advanced on Linux) I'm a little stuck on the
> way ahead.
>
> I've
On Thu, May 16, 2019 at 9:50 AM Susan Hurst
wrote:
> What are the correct permissions to give to a role so that all objects
> in the information_schema (and pg_catalog) are visible to a user?
>
Have you determined that using the underlying pg_catalog schema is not
viable.
David J.
On Thu, May 16, 2019 at 10:38 AM Fabio Ugo Venchiarutti <
f.venchiaru...@ocado.com> wrote:
> On 16/05/2019 18:20, Ron wrote:
> > On 5/16/19 4:36 AM, nigel.ander...@gmx.com wrote:
> >> but I wonder whether that might be an easier/more reliable option from
> >> an install and point of view and certa
On Saturday, May 18, 2019, Peter J. Holzer wrote:
> On 2019-05-16 08:48:51 -0700, David G. Johnston wrote:
> > On Thu, May 16, 2019 at 8:31 AM Daulat Ram
> wrote:
> >
> >
> > url_hash| bigint | | not null |
> >
> >
On Wed, May 22, 2019 at 2:33 PM PegoraroF10 wrote:
> We forgot FOR EACH ROW/STATEMENT when created our trigger.
>
> On docs it´s written {ROW/STATEMENT} between {}, so it´s needed. But why
> did
> it accepted our command to create that trigger ?
>
You only partially absorbed the syntax doc for t
On Wed, May 22, 2019 at 3:21 PM PegoraroF10 wrote:
> Try to create exactly what I sent you and you´ll see null values on that
> pk.
>
You should probably submit self-contained examples if you need the user to
do this.
> And that occurs just because that trigger was created without EACH
> ROW/S
On Wed, May 22, 2019 at 3:41 PM PegoraroF10 wrote:
> I´m not saying it should inspect function code, but I think it should deny
> when I try to create a trigger missing a needed argument.
>
Guessing you missed my earlier response...
> When I do ...
> create table MyTable(integer);
> gives me a
On Tue, Jun 4, 2019 at 3:30 PM Lou wrote:
> Is it possible to convert a boolean yes or no field to hold a one letter
> string? For example, the strings: 's' 'f' 'p' 'e'
>
Something like the following should work:
ALTER TABLE ... ALTER COLUMN ... TYPE text USING (CASE WHEN ... THEN 's'
ELSE 'f' E
On Tue, Jun 4, 2019 at 4:01 PM Rich Shepard
wrote:
> On Tue, 4 Jun 2019, Lou wrote:
>
> > To start off, I just need to convert true to 's'. false will have to be
> > manually changed to 'f' or 'p' or 'e' as appropriate.
>
> Second, if you need to display to the user something other than 't' and '
On Mon, Jun 10, 2019 at 8:32 PM Rob Sargent wrote:
> > On Jun 10, 2019, at 6:40 AM, Igor Korot wrote:
> >
> > Hi, Adrian,
> >
> >> On Mon, Jun 10, 2019 at 7:03 PM Adrian Klaver <
> adrian.kla...@aklaver.com> wrote:
> >>
> >>> On 6/10/19 9:30 AM, Igor Korot wrote:
> >>>
> >>> According to
> https
On Thu, Jun 20, 2019 at 9:17 AM Tom Lane wrote:
> Karsten Hilbert writes:
> > Sure, but we are currently exploring whether the database
> > reflects any values that it had not been given by the same
> > user beforehand.
>
> > There might be another scenario:
> > user enters value for colum
On Thu, Jun 20, 2019 at 2:13 PM Harry Ambrose
wrote:
> Hi,
>
> I noticed some weird update behaviour today in one of our development
> environments. In my opinion this appears to be a bug, but before reporting
> it I thought I should seek the opinions of others in the community. Maybe
> this is k
On Mon, Jun 24, 2019 at 4:11 PM Rob Sargent wrote:
>
> On 6/24/19 4:46 PM, Alex Magnum wrote:
>
> Yes, they are.
>
> On Tue, Jun 25, 2019 at 4:33 AM Rob Sargent wrote:
>
>>
>>
>> On Jun 24, 2019, at 2:31 PM, Alex Magnum wrote:
>>
>> Hi,
>> I have two arrays which I need to combine based on the
On Mon, Jun 24, 2019 at 7:31 PM Igal @ Lucee.org wrote:
> If a function select data from a table, and the rows in the table may
> change, would that function qualify for Immutable or does it have to be
> Stable? I'm asking because according to the docs [1]: "An IMMUTABLE
> function cannot modify
On Tue, Jun 25, 2019 at 2:58 PM David Gauthier
wrote:
> I need to create a constraint on a column of a table such that it's value
> is found in another table but may not be unique in that other table.
> Example...
>
This requires a trigger
>
> Let's say the DB is about students and the grades t
On Thu, Jul 4, 2019 at 6:18 AM Thomas Kellerer wrote:
> Why does
>
>select jsonb_set('{"foo": 1}'::jsonb, '{bar}', to_jsonb(null::int),
> true)
>
> return NULL when all it should do is to add a second key?
>
Both functions involved are defined as being STRICT (null on null input).
You need t
On Thursday, July 4, 2019, Gianni Ceccarelli wrote:
> Some experimentation:
>
> > \pset null '((null))'
>
> > select jsonb_set('{"foo":1}'::jsonb,'{bar}','null'::jsonb,true);
> ┌─┐
> │jsonb_set│
> ├─┤
> │ {"bar": null, "foo": 1} │
>
On Thu, Jul 4, 2019 at 2:09 PM David G. Johnston
wrote:
> On Thursday, July 4, 2019, Gianni Ceccarelli
> wrote:
>
>>
>> > select to_jsonb('null'::text);
>> ┌──┐
>> │ to_jsonb │
>> ├──┤
>> │ "null" │
>&g
On Mon, Apr 24, 2023 at 8:37 AM Siddharth Jain wrote:
> Hi All,
>
> i understand when writing application code, we should rollback a
> transaction that fails to commit. this is typically done in the catch block
> of a try-catch exception handler. but what if the developer does not
> rollback the
On Mon, Apr 24, 2023 at 12:56 PM David Wheeler
wrote:
>
>
> On 25 Apr 2023, at 1:47 am, David G. Johnston
> wrote:
>
>
> There isn't anything special about a failed transaction compared to any
> other transaction that you leave open.
>
>
> Now I’m cu
On Tue, May 2, 2023 at 2:38 AM Michael J. Baars <
mjbaars1977.pgsql.hack...@gmail.com> wrote:
> I have already tried to set the PostgreSQL 'dynamic_shared_memory_type'
> configuration option to 'mmap', but this does not help.
>
>
Of course it doesn't, that is a server-side configuration.
"Specifi
The convention on these lists is to inline or, at worse, bottom-post.
On Wed, May 3, 2023 at 7:34 AM J.A.
wrote:
> now select * from a _number_ of tables and return a -multi recordsets-
> from this single query. I'm not sure if that is the same terminology, in
> pgsql?
>
> So is this possible?
>
On Thu, May 4, 2023 at 7:04 AM Justin wrote:
>
>
> On Thu, May 4, 2023 at 9:49 AM DAVID ROTH wrote:
>
>> 1) Can I create a trigger on a view?
>> 2) Do triggers cascade?
>>
>> Say I have an insert trigger on a table.
>> And, I have an insert trigger on a view that references this table
>> If I do
On Thu, May 4, 2023 at 6:00 PM Wen Yi wrote:
> Hi team,
> I am a newbie to the postgres.
> When I am studying the compiler,the text book tell me there is there type
> of compiler.
>
>1. Assembly Language Format
>2. Relocatable Binary Format
>3. Memory-Image (Load-and-Go) Format
>
>
>
On Fri, May 5, 2023 at 6:55 AM Sky Lendar wrote:
> Hi there ans thx for reading and answering this post if you can:
>
> Let's regard an example of a table (stars) containing a code for a star
> (symb)
> and its index (nb) in a file.
>
>
> nb |symb
> +
> 0 | alTau
> 1 | al
On Fri, May 12, 2023, 08:35 Thorsten Glaser wrote:
> On Fri, 12 May 2023, Nathaniel Sabanski wrote:
>
> >I believe most users would anticipate a CREATE TABLE statement that aligns
> >with the currently installed version- this is the practical solution for
>
> The currently installed version of wh
On Thu, May 18, 2023 at 10:06 PM Raymond Brinzer
wrote:
> How do I turn arrays into composite typed values?
>
Using just SQL syntax and no string munging:
(array_val[1]::col1_type, array_val[2]::col2_type)::composite_type
> While the second point is rather far-reaching and idealistic, the fir
On Thursday, May 18, 2023, Raymond Brinzer wrote:
>
> scratch=# select row(2,3)::test_type;
>
Unknown typed value, immediately converted to a known concrete instance of
test_type. It is never actually resolved as record.
All of the others must concretely be resolved to record to escape their
qu
The convention here is to inline or, at worse, bottom post, trimming
irrelevant context.
On Friday, May 19, 2023, Raymond Brinzer wrote:
>
> I'm guessing that row() isn't really a function, then? And even so,
> assuming this is the important difference, how is the ability to change row
> struct
On Fri, May 26, 2023 at 8:02 PM jian he wrote:
>
> I still feel confused about "the key values".
>
The "key" here is the Foreign Key relationship. In short, FOR NO KEY
UPDATE, promises that PK/FK values on the table will not be changed. Only
non-FK/PK columns can be changed. In neither case m
On Sunday, May 28, 2023, 文一 <896634...@qq.com> wrote:
> Hi team,
> when I use the generate_series,as you can see
> (Fedora Linux 37, PGSQL 15.3)
>
> postgres=# SELECT x FROM generate_series(1, 25, 1) As x;
> x
>
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
> 11
> 12
> 13
>
901 - 1000 of 1639 matches
Mail list logo
