Re: Syntax on BEFORE Trigger - Cascade?

On Thursday, June 13, 2024, David Barbour wrote: > > When a record in *import_job* is deleted, the child records (file > records) in *import_file* need to be deleted first. > > The constraint in both Oracle and Postgres is similar (Postgres version): > *ALTER TABLE IF EXISTS idev.import_file* > >

Re: Syntax on BEFORE Trigger - Cascade?

David Barbour writes: > The files are appropriately deleted in Oracle, but Postgres is returning > the following: > *ERROR: Attempt to suppress referential action with before trigger. > CONTEXT: SQL statement "DELETE FROM ONLY "idev"."import_file" WHERE $1 > OPERATOR(pg_catalog.=) "import_job_oid"

Re: Syntax

On Thu, Dec 7, 2023 at 3:01 AM arun chirappurath wrote: > Hi All, > > What is the difference or use case for below syntaxes? > > do $$ > declare d int; > begin > RAISE INFO 'Script started at %', CURRENT_TIMESTAMP; > update employees set first_name = 'g' where employee_id = 1; get > diagnostics d

Re: syntax pb

Thanks for the explanation. Crystal clear, thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, May 30, 2023 at 7:31 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Tue, May 30, 2023 at 8:53 AM Marc Millas > wrote > >> Too my understanding it looks like the parser

Re: syntax pb

Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, May 30, 2023 at 7:12 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, May 30, 2023 at 8:53 AM Marc Millas wrote > > >> This comes from a prod environment and even casting NULLs (which is more >> than strange, B

Re: syntax pb

Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, May 30, 2023 at 3:51 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, May 30, 2023, Marc Millas wrote: > >> >> I get: >> ERROR: column "d" is of type numeric but expression is of type text >> LINE 2: Selec

Re: syntax pb

On 5/30/23 8:53 AM, Marc Millas wrote: Thanks Adrian, but if the query becomes more complex, for example with a few joins more, then even casting doesn't work. This comes from a prod environment and even casting NULLs (which is more than strange, BTW) generates absurd errors. Too my underst

Re: syntax pb

On 5/30/23 10:31 AM, Tom Lane wrote: "David G. Johnston" writes: On Tue, May 30, 2023 at 8:53 AM Marc Millas wrote Too my understanding it looks like the parser did not parse the select distinct as we think he does. The DISTINCT clause doesn't really come into play here at all, so if you t

Re: syntax pb

"David G. Johnston" writes: > On Tue, May 30, 2023 at 8:53 AM Marc Millas wrote >> Too my understanding it looks like the parser did not parse the select >> distinct as we think he does. > The DISTINCT clause doesn't really come into play here at all, so if you > think it does you indeed have a

Re: syntax pb

On Tue, May 30, 2023 at 8:53 AM Marc Millas wrote > This comes from a prod environment and even casting NULLs (which is more > than strange, BTW) generates absurd errors. > If you want an input to be anything other than plain text (numbers partially exempted) you need to cast it. Sure, some li

Re: syntax pb

On Tue, May 30, 2023 at 05:53:30PM +0200, Marc Millas wrote: > Thanks Adrian, but if the query becomes more complex, for example with a > few joins more, then even casting doesn't work. > This comes from a prod environment and even casting NULLs (which is more > than strange, BTW) generates absurd

Re: syntax pb

The above should have been: > > Insert into t2 (a, b, c, d) > Select distinct test1.t, 'abc' as b, NULL::text, NULL::numeric > From t1 test1; > > > > > which results in: > > > > select * from t2; > > a| b | c | d > > +-+--+-- > > azerty | abc | NULL | NULL > > >

Re: syntax pb

On 5/30/23 07:38, Adrian Klaver wrote: On 5/30/23 06:45, Marc Millas wrote: Hi, I always have had difficulties to understand syntax. So... If I have: create table t1 (t text); create table t2 (a text, b text, c test, d numeric); Is c supposed to be text? Or are you indeed referring to some u

Re: syntax pb

On 5/30/23 06:45, Marc Millas wrote: Hi, I always have had difficulties to understand syntax. So... If I have: create table t1 (t text); create table t2 (a text, b text, c test, d numeric); Is c supposed to be text? Or are you indeed referring to some unspecified type? insert into t1('azert

Re: syntax pb

On 30/05/2023 14:45, Marc Millas wrote: and , now, if I want to insert that: Insert into t2 (a, b, c, d) Select distinct test1.t, 'abc' as b, NULL as c, NULL as d From t1 test1; I get: ERROR:  column "d" is of type numeric but expression is of type text LINE 2: Select distinct test1.t, 'abc' a

Re: syntax pb

On Tuesday, May 30, 2023, Marc Millas wrote: > > I get: > ERROR: column "d" is of type numeric but expression is of type text > LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d > > HINT: You will need to rewrite or cast the expression. > > Can someone give a short SQL syntax hi

Re: Syntax error when combining --set and --command has me stumped

On 7/29/22 04:05, Gianni Ceccarelli wrote: If you can use bash, or set up some redirections from whatever you're using to execute ``psql``, you can do:: $ psql somedb --set num=42 <<<'select :num' Timing is on. Expanded display is used automatically. Line style is unicode. Border

Re: Syntax error when combining --set and --command has me stumped

If you can use bash, or set up some redirections from whatever you're using to execute ``psql``, you can do:: $ psql somedb --set num=42 <<<'select :num' Timing is on. Expanded display is used automatically. Line style is unicode. Border style is 2. ┌──┐ │ ?column? │ ├─

Re: Syntax error when combining --set and --command has me stumped

On 7/28/22 12:40, Ron wrote: $ alias psql12 alias psql12='/usr/lib/postgresql/12/bin/psql -p5433' This works ask expected: $ psql12 --set num=42 -ac "\echo :num" echo :num 42 And so does this: $ psql12 --set num=42 psql (12.11 (Ubuntu 12.11-1.pgdg18.04+1)) Type "help" for help. postgres=# s

Re: Syntax error when combining --set and --command has me stumped

On 7/28/22 14:47, David G. Johnston wrote: On Thu, Jul 28, 2022 at 12:40 PM Ron wrote: What secret sauce am I missing to get this to work? Given that the documentation says: "command must be either a command string that is completely parsable by the server (i.e., it contains no psql-spe

Re: Syntax error when combining --set and --command has me stumped

On Thu, Jul 28, 2022 at 12:40 PM Ron wrote: > What secret sauce am I missing to get this to work? > Given that the documentation says: "command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command.

Re: syntax question

no pb: I am french, so quite skilled on that topic :-) there is only 50 bottles of various malt on the presentoir close to my desk so I must stay reasonnable :-) Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Jun 3, 2021 at 11:17 PM Adrian Klaver wrote: > On 6/3/21 1:01 PM

Re: syntax question

On 6/3/21 1:01 PM, Marc Millas wrote: thanks Adrian, exactly what I was missing :-) about knowing if I should... We have to create a set of triggers (insert, update, delete) within a huge set of tables. and that list of tables, and structure of them  can be customized, maintained, ... so we we

Re: syntax question

I know it would be non-standard, but I would love to see Postgres support the likes of nested functions. I know that would be non-standard, but Postgres has lots of non-standard features that make it more like a real programming language and considerably more productive. On Jun 3, 2021, 12:34 -

Re: syntax question

I take note of this. thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Jun 3, 2021 at 10:23 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Jun 3, 2021 at 1:02 PM Marc Millas wrote: > >> about knowing if I should... >> We have to create a set of trigg

Re: syntax question

On Thu, Jun 3, 2021 at 1:02 PM Marc Millas wrote: > about knowing if I should... > We have to create a set of triggers (insert, update, delete) within a huge > set of tables. and that list of tables, and structure of them can be > customized, maintained, ... > so we were looking for a standard s

Re: syntax question

thanks Adrian, exactly what I was missing :-) about knowing if I should... We have to create a set of triggers (insert, update, delete) within a huge set of tables. and that list of tables, and structure of them can be customized, maintained, ... so we were looking for a standard script to automa

Re: syntax question

good reading, thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Jun 3, 2021 at 9:21 PM Tom Lane wrote: > Adrian Klaver writes: > > On 6/3/21 12:01 PM, Bruce Momjian wrote: > >> On Thu, Jun 3, 2021 at 08:58:03PM +0200, Marc Millas wrote: > >>> within a function, I want

Re: syntax question

On Thu, Jun 3, 2021 at 03:21:15PM -0400, Tom Lane wrote: > Adrian Klaver writes: > > On 6/3/21 12:01 PM, Bruce Momjian wrote: > >> On Thu, Jun 3, 2021 at 08:58:03PM +0200, Marc Millas wrote: > >>> within a function, I want to create another function. > > >> You can't create functions inside of

Re: syntax question

Adrian Klaver writes: > On 6/3/21 12:01 PM, Bruce Momjian wrote: >> On Thu, Jun 3, 2021 at 08:58:03PM +0200, Marc Millas wrote: >>> within a function, I want to create another function. >> You can't create functions inside of functions; same for procedures. > Sure you can: Yeah. The actual p

Re: syntax question

On 6/3/21 12:01 PM, Bruce Momjian wrote: On Thu, Jun 3, 2021 at 08:58:03PM +0200, Marc Millas wrote: Hi, within a function, I want to create another function. no pb. but if I write: declare bidule text; begin bidule:='myfunc'; create function bidule() ... it does create a function named bidu

Re: syntax question

On Thu, Jun 3, 2021 at 08:58:03PM +0200, Marc Millas wrote: > Hi, > > within a function, I want to create another function. > no pb. > but if I write: > declare bidule text; > begin > bidule:='myfunc'; > create function bidule() ... > > > it does create a function named bidule and not myfunc. >

Re: Syntax checking DO blocks and ALTER TABLE statements?

pá 19. 2. 2021 v 6:09 odesílatel Kevin Brannen napsal: > >From: Ron > > > >How does one go about syntax checking this? > > > >do $$ > >begin if exists (select 1 from information_schema.table_constraints > >where constraint_name = 'error_to_web_service_error') then > > raise n

RE: Syntax checking DO blocks and ALTER TABLE statements?

>From: Ron > >How does one go about syntax checking this? > >do $$ >begin if exists (select 1 from information_schema.table_constraints >where constraint_name = 'error_to_web_service_error') then > raise notice 'EXISTS error_to_web_service_error'; > else > ALTER TAB

Re: Syntax checking DO blocks and ALTER TABLE statements?

On 2/16/21 6:19 PM, Tim Cross wrote: Ron writes: On 2/16/21 5:44 PM, Tim Cross wrote: Given the number, I think I would do the same. A good example of why being 'lazy' can be a virtue. Faster and easier to write a procedure to generate dynamic SQL than write out all those alter statements man

Re: Syntax checking DO blocks and ALTER TABLE statements?

Ron writes: > On 2/16/21 5:44 PM, Tim Cross wrote: >> Given the number, I think I would do the same. A good example of why >> being 'lazy' can be a virtue. Faster and easier to write a procedure to >> generate dynamic SQL than write out all those alter statements manually >> or even write it us

Re: Syntax checking DO blocks and ALTER TABLE statements?

On 2/16/21 5:44 PM, Tim Cross wrote: Given the number, I think I would do the same. A good example of why being 'lazy' can be a virtue. Faster and easier to write a procedure to generate dynamic SQL than write out all those alter statements manually or even write it using a scripting language and

Re: Syntax checking DO blocks and ALTER TABLE statements?

David G. Johnston writes: > On Tue, Feb 16, 2021 at 4:28 PM Tim Cross wrote: > >> >> David G. Johnston writes: >> >> > On Tue, Feb 16, 2021 at 3:43 PM Ron wrote: >> > >> >> >> >> How does one go about syntax checking this? >> >> >> >> (There are 222 ALTER TABLE ADD FOREIGN KEY statements tha

Re: Syntax checking DO blocks and ALTER TABLE statements?

On Tue, Feb 16, 2021 at 4:28 PM Tim Cross wrote: > > David G. Johnston writes: > > > On Tue, Feb 16, 2021 at 3:43 PM Ron wrote: > > > >> > >> How does one go about syntax checking this? > >> > >> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping > in > >> similar DO blocks

Re: Syntax checking DO blocks and ALTER TABLE statements?

David G. Johnston writes: > On Tue, Feb 16, 2021 at 3:43 PM Ron wrote: > >> >> How does one go about syntax checking this? >> >> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in >> similar DO blocks, and want to make sure the statements are clean.) >> >> > Begin a tra

Re: Syntax checking DO blocks and ALTER TABLE statements?

On Tue, Feb 16, 2021 at 3:43 PM Ron wrote: > > How does one go about syntax checking this? > > (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in > similar DO blocks, and want to make sure the statements are clean.) > > Begin a transaction, execute the DO, capture an error

Re: Syntax question about returning value from an insert

On Wed, Dec 25, 2019 at 09:17:22PM -0800, Adrian Klaver wrote: > On 12/25/19 4:48 PM, Rob Sargent wrote: > > > > > > > On Dec 25, 2019, at 3:10 PM, stan wrote: > > > There is more that that. There is a project number, so the actuall key > > > represents the combination of project number, and c

Re: Syntax question about returning value from an insert

On 12/25/19 3:09 PM, stan wrote: On Wed, Dec 25, 2019 at 02:34:43PM -0800, Adrian Klaver wrote: On 12/25/19 12:39 PM, stan wrote: Other approaches have been suggested, e.g. use a traditional FK relationship. The big unknown in you present system is what: SELECT cost_category_key from t2 w

Re: Syntax question about returning value from an insert

On 12/25/19 4:48 PM, Rob Sargent wrote: On Dec 25, 2019, at 3:10 PM, stan wrote: There is more that that. There is a project number, so the actuall key represents the combination of project number, and cost category, Thire is a constraint on T2 that assures that these combinations will be uni

Re: Syntax question about returning value from an insert

> On Dec 25, 2019, at 3:10 PM, stan wrote: > There is more that that. There is a project number, so the actuall key > represents the combination of project number, and cost category, Thire is a > constraint on T2 that assures that these combinations will be unique. > > When the 1st record for

Re: Syntax question about returning value from an insert

On Wed, Dec 25, 2019 at 06:09:55PM -0500, stan wrote: > On Wed, Dec 25, 2019 at 02:34:43PM -0800, Adrian Klaver wrote: > > On 12/25/19 12:39 PM, stan wrote: > > > > > > On Wed, Dec 25, 2019 at 11:55:51AM -0800, Adrian Klaver wrote: > > > > On 12/25/19 11:08 AM, stan wrote: > > > > > > > > > > O

Re: Syntax question about returning value from an insert

On Wed, Dec 25, 2019 at 02:34:43PM -0800, Adrian Klaver wrote: > On 12/25/19 12:39 PM, stan wrote: > > > > On Wed, Dec 25, 2019 at 11:55:51AM -0800, Adrian Klaver wrote: > > > On 12/25/19 11:08 AM, stan wrote: > > > > > > > > On Wed, Dec 25, 2019 at 08:28:45AM -0800, Adrian Klaver wrote: > > > >

Re: Syntax question about returning value from an insert

On 12/25/19 12:39 PM, stan wrote: On Wed, Dec 25, 2019 at 11:55:51AM -0800, Adrian Klaver wrote: On 12/25/19 11:08 AM, stan wrote: On Wed, Dec 25, 2019 at 08:28:45AM -0800, Adrian Klaver wrote: On 12/25/19 7:26 AM, stan wrote: I am writing a trigger/function to make certain a default item,

Re: Syntax question about returning value from an insert

> On Dec 25, 2019, at 11:56 AM, Adrian Klaver < >> First of all, thanks to both of you for your fast response . >> Let me clarify. >> I have a table that records will be inserted into. Several of the columns >> in this table must be non NULL, and they are actually keys from other >> tables. Lik

Re: Syntax question about returning value from an insert

On Wed, Dec 25, 2019 at 11:55:51AM -0800, Adrian Klaver wrote: > On 12/25/19 11:08 AM, stan wrote: > > > > On Wed, Dec 25, 2019 at 08:28:45AM -0800, Adrian Klaver wrote: > > > On 12/25/19 7:26 AM, stan wrote: > > > > I am writing a trigger/function to make certain a default item, and its > > >

Re: Syntax question about returning value from an insert

On 12/25/19 11:08 AM, stan wrote: On Wed, Dec 25, 2019 at 08:28:45AM -0800, Adrian Klaver wrote: On 12/25/19 7:26 AM, stan wrote: I am writing a trigger/function to make certain a default item, and its key exist when an insert is called. EG The trigger gets called on insert to T1 If column c1

Re: Syntax question about returning value from an insert

On Wed, Dec 25, 2019 at 08:28:45AM -0800, Adrian Klaver wrote: > On 12/25/19 7:26 AM, stan wrote: > > I am writing a trigger/function to make certain a default item, and its key > > exist when an insert is called. EG > > > > The trigger gets called on insert to T1 If column c1 is NULL in the NEW

Re: Syntax question about returning value from an insert

On 12/25/19 7:26 AM, stan wrote: I am writing a trigger/function to make certain a default item, and its key exist when an insert is called. EG The trigger gets called on insert to T1 If column c1 is NULL in the NEW structure, I need to check table t2 to get the key associated with the default f

Re: Syntax question about returning value from an insert

Hi st 25. 12. 2019 v 16:26 odesílatel stan napsal: > I am writing a trigger/function to make certain a default item, and its key > exist when an insert is called. EG > > The trigger gets called on insert to T1 If column c1 is NULL in the NEW > structure, I need to check table t2 to get the key a

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

On 12/3/19 8:24 AM, Alexander Farber wrote: Thanks for your replies! Tom has hinted that STRICT is pl/pgSQL syntax and not SQL I finally read the full function and see you declared the LANGUAGE as sql. Now things make sense:) Regards Alex -- Adrian Klaver adrian.kla...@aklaver.com

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

Thanks for your replies! Tom has hinted that STRICT is pl/pgSQL syntax and not SQL Regards Alex >

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

On 12/3/19 3:37 AM, Alexander Farber wrote: Thank you Patrick - On Tue, Dec 3, 2019 at 11:49 AM Patrick FICHE > wrote: It seems that STRICT is the issue. But why does your function return a table of boolean in this case ? As it only upda

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

Alexander Farber writes: > why does not PostgreSQL 10.11 please like the - I think you are confusing plpgsql syntax with sql syntax. regards, tom lane

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

Thank you Patrick - On Tue, Dec 3, 2019 at 11:49 AM Patrick FICHE wrote: > > It seems that STRICT is the issue. > > But why does your function return a table of boolean in this case ? > > As it only updates one record, it would probably be easier to return a > boolean only. > > CREATE OR REPLACE

RE: Syntax error for UPDATE ... RETURNING INTO STRICT

Hi Alexander, It seems that STRICT is the issue. But why does your function return a table of boolean in this case ? As it only updates one record, it would probably be easier to return a boolean only. CREATE OR REPLACE FUNCTION words_toggle_puzzle( in_mid bigint ) RET

Re: syntax error with v12

On 10/10/19 5:51 AM, Ivan Kabaivanov wrote: Hi, everyone. First time poster here. This snippet (from ovirt-engine-dwh) used to work fine with postgresql-11.x, but emits a syntax error with postgresql-12.0: CREATETABLEhistory_configuration ( var_name VARCHAR(50) NOTNULL, var_value VARCHAR(255

Re: syntax error with v12

Thanks! ivanK. On Thu, Oct 10, 2019 at 3:56 PM Félix GERZAGUET wrote: > Hi, > > On Thu, Oct 10, 2019 at 2:51 PM Ivan Kabaivanov > wrote: > >> psql:/usr/share/ovirt-engine-dwh/dbscripts/create_tables.sql:7: ERROR: >> syntax error at or near "OIDS" >> LINE 7: ) WITH OIDS; >> > > Indeed, OID wer

Re: syntax error with v12

Hi, On Thu, Oct 10, 2019 at 2:51 PM Ivan Kabaivanov wrote: > psql:/usr/share/ovirt-engine-dwh/dbscripts/create_tables.sql:7: ERROR: > syntax error at or near "OIDS" > LINE 7: ) WITH OIDS; > Indeed, OID were removed from 12 release as described in: https://www.postgresql.org/docs/release/12.0/

Re: syntax error with v12

Hello, I think this change is mentioned in release notes https://www.postgresql.org/docs/release/12.0/. čt 10. 10. 2019 v 14:51 odesílatel Ivan Kabaivanov < ivan.kabaiva...@gmail.com> napsal: > Hi, everyone. > > First time poster here. > > This snippet (from ovirt-engine-dwh) used to work fine wi

RE: syntax error with alter type

Tom Lane wrote: > Kevin Brannen writes: > > So, can you (or anyone) help me understand what "alter type drop > > attribute" is meant to do? I don't see "attribute" on the page you > > reference. > IIRC, that drops a column from a composite type; it's more or less a variant > spelling of ALTE

Re: syntax error with alter type

Kevin Brannen writes: > So, can you (or anyone) help me understand what "alter type drop > attribute" is meant to do? I don't see "attribute" on the page you reference. IIRC, that drops a column from a composite type; it's more or less a variant spelling of ALTER TABLE DROP COLUMN.

RE: syntax error with alter type

On 12/7/18 3:17 PM, Kevin Brannen wrote: > I'm running Pg 9.6.5 if it matters... > > I'm trying to drop a value from an ENUM (type) and it seems like I'm > following the fine manual yet I still get an error. For example: > > nms=# create type alphabet as enum ('a', 'b', 'c', 'd'); > > CREATE TYPE >

Re: syntax error with alter type

On 12/7/18 3:17 PM, Kevin Brannen wrote: I’m running Pg 9.6.5 if it matters… I’m trying to drop a value from an ENUM (type) and it seems like I’m following the fine manual yet I still get an error. For example: nms=# create type alphabet as enum ('a', 'b', 'c', 'd'); CREATE TYPE nms=# alter