4,442 questions
1
vote
1
answer
47
views
Why is this PostgreSQL function throwing an exception, and how can I tell which input is?
I have a table which stores attribute value data, the majority of which are numbers but occasionally strings. When the values are numbers, we are interested in a delta. The following PostgreSQL ...
- 1,021
0
votes
0
answers
26
views
How to use a dynamically created table name in a PL/pgSQL block (Postgres)
I have a block of code in PL/pgSQL that includes this line to update a table...
UPDATE myTable SET processedT = true, messType = 12 WHERE messId = new.messID;
The table myTable is actually a ...
- 847
0
votes
1
answer
43
views
Variables interpreted as values in transaction
CREATE FUNCTION create_user_if_not_exists(_name text, _pass text)
RETURNS void AS
$func$
DECLARE
_dbname TEXT := concat(_name, '_db');
BEGIN
IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE ...
- 3,193
2
votes
1
answer
32
views
How to analyze query with parameters in Postgres
I wanted to analyze and compare following queries:
SELECT from mytable WHERE mytable.TimeStamp < NOW() - MAKE_INTERVAL(DAYS => 1);
and same query, but replace Now() with a variable.
DO $$
...
- 27.5k
1
vote
1
answer
76
views
Delete using subquery vs temp table
I'm deleting data and I want to minimize time when rows or table is locked. Goal is to increase throughput of other queries.
Let's say inserting into temp table takes 10 seconds and delete statement ...
- 27.5k
0
votes
1
answer
20
views
What is wrong in this declare?
When I am running code
DECLARE
c int;
BEGIN
execute 'select count(1) from my_dwh.accounts' into c;
raise notice c;
END;
I am getting error
SQL Error [42601]: ERROR: syntax error at or near "...
- 41
0
votes
1
answer
34
views
How to assign affected row count to a variable? [duplicate]
I'm struggling with a opstgres syntax.
I have a variable and I can get number of affected rows using returning clause
DECLARE deletedRowCount integer;
WITH deleted as
(
DELETE FROM MyTable "...
- 27.5k
2
votes
1
answer
44
views
Parallel safety and exception handling in Postgres
I am using Postgres 15.
Is there any way to handle exception handling and parallel safety in postgres?
I have a function foo() that is immutable and parallel safe but there are cases where it might ...
- 967
0
votes
1
answer
41
views
Postgres INSERT text column accepts only numeric characters convertible to bigint
I have a table and a function for maintaining it, which is called from a client application. Below is a snippet with the table definition and plpgsql function in question. For the sake of brevity in ...
- 113
0
votes
1
answer
62
views
How to create operator if not exists
Query
CREATE OR REPLACE FUNCTION public.concatkeepspaces(left bpchar, right bpchar)
RETURNS bpchar
LANGUAGE sql IMMUTABLE
AS $BODY$
SELECT concat($1,$2);
$BODY$;
CREATE OPERATOR public.+ (
leftarg = ...
- 28k
2
votes
1
answer
95
views
in a trigger, how to use NEW for a dynamic query?
I am trying to write a generic function that uppercase a parametrized column. The function is called by a trigger run before insert or update.
I tried several triggers:
CREATE OR REPLACE FUNCTION ...
1
vote
1
answer
96
views
Update every N rows with an increment of 1
I am an SQL server developer working on a project in a PostgreSQL environment. I am having some PostgreSQL syntax issues. I am working off version 9.3.
In a given table, I am trying to set every 10 ...
- 1,865
0
votes
2
answers
107
views
Why does this multiple "DO" block script work in DBeaver but fail in pgAdmin?
I have created a script to be run on a Postgres DB, and it works perfectly when run in DBeaver as long as Autocommit is turned on, but when the same script is run in pgAdmin with Autocommit turned on, ...
- 981
1
vote
1
answer
51
views
Dynamically Access Columns and Cast Data Types in PostgreSQL Functions
In custom functions in PostgreSQL, I can access the NEW object to retrieve the values of specific columns, for example:
NEW.description
NEW.city
Essentially, this is a static reference at the code-...
- 26k
0
votes
1
answer
63
views
Using "select into" and "Select" statements in a block in Postresql
I see that some select and select into statements can be used in a PL/pgSQL DO block in PostgreSQL, while some cannot(error). What causes this error?
create table countries(
country_id int
,code ...
- 105