27
you can check if the functions are defined using
select pg_get_functiondef(to_regproc('gen_random_bytes'));
select pg_get_functiondef(to_regproc('gen_random_uuid'));
or:
select * from pg_proc where proname like 'gen_random_%';
if both functions are not defined then you probably had an error with the extension creation - just drop it and recreate:
drop ...
25
Multiple problems.
Your setup, extended:
CREATE TABLE a (
pk_a int PRIMARY KEY
, a int
, comment text -- added column to make effect clear
);
CREATE TABLE b (
pk_b int PRIMARY KEY
, b int
, comment text
);
INSERT INTO a VALUES (1, 11, 'comment from a')
, (2, 22, 'comment from a');
INSERT INTO b VALUES (1, 77, 'comment from b');
...
22
On 18th of November, 1883 at 12:00 (new time), standard time was adopted by the American railroads.
This means that before that time, Los Angeles used actual local time, based on mean solar time. After that, it was moved to its local time zone, which, being an integral offset of hours from the Greenwich Mean Time, was slightly different from the previous ...
22
You don't have to drop the database, it should be enough to drop all the objects in the database. This can be done using
drop owned by adminuser
If you then create the SQL dump including the create table statements (so without the --data-only option) everything should be fine.
You can also remove the --column-inserts then, which will make the import a ...
answered Nov 2 '16 at 18:40
a_horse_with_no_name
51.3k10 gold badges99 silver badges133 bronze badges
17
You need to add extra parentheses around the CASE expression:
CREATE UNIQUE INDEX test_index
ON test ((CASE WHEN i=1 THEN j END)) ;
As the docs state in CREATE INDEX:
The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses.
Consider also using a filtered index, which is equivalent in ...
15
Clarify ON CONFLICT DO UPDATE behavior
Consider the manual here:
For each individual row proposed for insertion, either the insertion
proceeds, or, if an arbiter constraint or index specified by
conflict_target is violated, the alternative conflict_action is taken.
Bold emphasis mine. So you do not have to repeat predicates for columns included in ...
15
Variant 1
Since all you need is a single column with standard = true, set standard to NULL in all other rows. Then a plain UNIQUE constraint works, since NULL values do not violate it:
CREATE TABLE taxrate (
taxrate int PRIMARY KEY
, standard bool DEFAULT true
, CONSTRAINT standard_true_or_null CHECK (standard) -- yes, that's the whole constraint
, ...
answered Feb 10 '18 at 19:02
Erwin Brandstetter
111k13 gold badges243 silver badges375 bronze badges
14
You can use the generate_series() set returning function:
INSERT INTO bins (id)
SELECT g.id
FROM generate_series(1, 10000) AS g (id) ;
13
This does not work because it's trying to cast a jsonb value to integer.
select data->'name' as name from persons where cast(data->'age' as int) > 25
This would actually work:
SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25;
Or shorter:
SELECT data->'name' AS name FROM persons WHERE (data->>'age')::int > 25;
...
12
This turns out to be due to a somewhat obscure error message which happens when the schema you are dumping from with pg_dump does not exist on the target database/machine. I will leave this question, in the hope it might help someone else.
12
My wild guess: "more efficient" means "less time is required to perform the check" (time advantage). It may also mean "less memory is required to perform the check" (space advantage). It might also mean "has less side effects" (such as not locking something or locking it for shorter periods of time)... but I don't have a way to know or check that "extra ...
12
The function needs to return a SETOF RECORD instead of RECORD and have one RETURN NEXT per row instead of a single RETURN, as in:
CREATE FUNCTION test() RETURNS SETOF RECORD AS $$
DECLARE
rec record;
BEGIN
select 1,2 into rec;
return next rec;
select 3,4 into rec;
return next rec;
END $$ language plpgsql;
Caller:
=> select * from test() as x(a ...
12
Thanks to suggestion from Daniel Vérité I was able to fix it without dumping the entire db server. My understanding of locales on Windows is very limited but from what I've learned it seems like Microsoft changed region name of czech locale from "Czech Republic" to "Czechia" during Fall Creators update (presumably so it conforms to ISO 3166-1).
Postgres ...
11
These are 16 MB WAL segments by default. The manual:
The system physically divides this sequence into WAL segment files,
which are normally 16MB apiece (although the segment size can be
altered when building PostgreSQL)
So, it's just default values as advertised:
select name, setting, setting::int * 16 || 'MB' AS setting_in_mb
from pg_settings
...
11
You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here.
CREATE OR REPLACE FUNCTION insert_records_for_notification(
_username text
, _state text
, _district text
, _bloodgroup text
, _status text
, _approverejectstatus text
, _emailsubject text
, ...
10
No,
You can't control a transaction inside a plpgsql function (or anonymous block).
The only option that you have its creating a transaction outside the block, eg:
BEGIN;
DO $$
-- function stuff
-- but if you use a exception, you will force a rollback
RAISE EXCEPTION 'message';
$$ LANGUAGE 'plpgsql';
COMMIT; -- OR ROLLBACK
BTW, DO BLOCKS have ...
10
Debunking
If you are doing all your updates in the same transaction, each of them will have to work an increasingly bigger set of (physical) tuples. See the following example:
CREATE TABLE explode (id integer, something text);
INSERT INTO explode SELECT i, md5(i::text) FROM generate_series(1, 100000) t(i);
\dt+ explode -- done in psql
...
10
If you are using psql 9.6+, there is a very convenient command called \gexec:
Sends the current query input buffer to the server, then treats each
column of each row of the query's output (if any) as a SQL statement
to be executed. [...] The generated queries are executed in the order
in which the rows are returned, and left-to-right within each row ...
10
As a_horse_with_no_name said in a comment:
No, that's not possible. You need some kind of scheduler that runs refresh materialized view e.g. pg_cron or something on the operating system level – a_horse_with_no_name
Alternatively, if you need a MATERIALIZED VIEW that refreshes when you run SELECT, just remove MATERIALIZED and use a regular VIEW. ...
10
Isn't the implicit syntax just rewritten to be the same as the explicit syntax?
Not necessarily. You are building on slightly incorrect assumptions. Like I explained under the referenced question:
What does [FROM x, y] mean in Postgres?
Comma-separated items in the FROM list are almost, but not quite identical to explicit CROSS JOIN notation. Explicit ...
9
Yes, use the statistics collector. The manual:
PostgreSQL's statistics collector is a subsystem that supports
collection and reporting of information about server activity. [...]
It can also count calls to user-defined functions and the total time
spent in each one.
[...]
The parameter track_functions enables tracking of usage of
user-...
9
The problem is that your 2nd query:
SELECT datasets.id
FROM datasets
GROUP BY datasets.id
ORDER BY date_added
LIMIT 25 ;
does not mean what you expect. It does give you the first 25 rows ordered by date_added only because the id is the primary key of the table, so the GROUP BY can be removed without changing the result.
It seems however that the ...
9
You can use a Filtered index
create table test
(
id int primary key,
foo bool
);
CREATE UNIQUE INDEX only_one_row_with_column_true_uix
ON test (foo) WHERE (foo); --> where foo is true
insert into test values (1, false);
insert into test values (2, true);
insert into test values (3, false);
insert into test values (4, false);
insert into ...
8
When doing upserts in PostgreSQL 9.5+ you must refer to the excluded data (that which failed to insert) by the alias excluded. Also, the on conflict option must refer to the key: (pk_b) rather than (b). Eg.
insert into table_b (pk_b, b)
select pk_a,a from table_a
on conflict (pk_b) do update set b=excluded.b;
For more information refer to the official ...
answered Apr 6 '16 at 9:02
Ziggy Crueltyfree Zeitgeister
4,4381 gold badge10 silver badges20 bronze badges
8
This will work (as far as I tested) in all 3 cases, if the to-be-inserted values are all new or all already in the table or a mix:
WITH
val (name) AS
( VALUES -- rows to be inserted
('foo'),
('bar'),
('zzz')
),
ins AS
( INSERT INTO
tag (name)
SELECT name FROM val
ON ...
8
Use setof record and return next rec if you want to return multiple records from a function, example:
create or replace function test_function()
returns setof record
language plpgsql as $$
declare
rec record;
begin
for rec in
select i, format('str%s', i), i/2*2 = i
from generate_series(1, 3) i
loop
return next ...
7
You put the cast outside the array constructor - use ::varchar[] instead. The current query will return you the array literal (which is of type varchar) instead of an actual array - see the example SQL Fiddle.
But overall, if you only need the output as an array downstream, it would make your life easier if you returned proper columns upstream - easier to ...
7
Like I commented, this would be more efficient with a normalized DB layout, with a table like this
CREATE TABLE task_packets (
task_id int PRIMARY KEY
, state text NOT NULL
-- or: state_id int NOT NULL REFERENCES state(state_id) ...
);
Among other things, we can have a PK constraint enforcing unique task_id numbers. And the UPDATE you want is trivial.
...
7
If you use use_remote_estimate be sure to run ANALYZE the foreign table (I see estimations pretty close with the returned, you'd probably did it). Also, the pushdown improvements are not available in <9.5 version. I also assume that you have the same table structure on the remote server either (including indexes). If a bitmap is needed due to the low ...
7
I think the problem is that you don't have a partial index and the ON CONFLICT syntax doesn't match the test_upsert_upsert_id_idx index but the other unique constraint.
If you define the index as partial (with WHERE test_field IS NULL):
CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", ...
Only top voted, non community-wiki answers of a minimum length are eligible
