44
votes
UPSERT with ON CONFLICT using values from source table in the UPDATE part
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
, ...
- 150k
34
votes
Accepted
psql 9.5: gen_random_uuid() not working
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 ...
- 1,128
33
votes
Accepted
Delete all data in Postgres database
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 ...
- 70.5k
30
votes
Accepted
PostgreSQL UPSERT issue with NULL values
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
...
- 150k
24
votes
Querying JSONB in PostgreSQL
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' ...
- 150k
22
votes
Accepted
Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?
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. ...
- 544
22
votes
Accepted
How to create an index on CASE expression in Postgres
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 ...
- 92.6k
22
votes
Constraint - one boolean row is true, all other rows false
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 ...
- 150k
21
votes
Inserting 10000 new rows?
You can use the generate_series() set returning function:
INSERT INTO bins (id)
SELECT g.id
FROM generate_series(1, 10000) AS g (id) ;
- 92.6k
19
votes
Accepted
How can I return multiple rows of records in PL/pgSQL
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;...
- 27k
17
votes
Accepted
Understanding "max_wal_size" and "min_wal_size" parameters default values from postgresql.conf file
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 ...
- 150k
17
votes
Constraint - one boolean row is true, all other rows false
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 ...
- 22.7k
16
votes
What is the best way to create Primary Key on a large table in Postgresql 9.5?
You can create a unique index with the option concurrently which will allow read and write access to the table while the index is created. However, building an index concurrently will take much longer ...
- 70.5k
16
votes
Postgres: How is SET NOT NULL "more efficient" than CHECK constraint
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 ...
- 12.4k
16
votes
Accepted
How to use function parameters in dynamic SQL with EXECUTE?
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
...
- 150k
15
votes
Accepted
Permission denied to create "pg_catalog.tablename" during pg_dump targetting another machine
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, ...
- 1,378
14
votes
Accepted
How to get the ID of the conflicting row in upsert?
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 ...
- 92.6k
13
votes
Refresh a PostgreSQL materialized view automatically without using triggers
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 – ...
- 55.7k
12
votes
UPSERT with ON CONFLICT using values from source table in the UPDATE part
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)...
- 4,798
12
votes
How can I return multiple rows of records in PL/pgSQL
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 $$
...
- 1,924
12
votes
Accepted
PostgreSQL 9.5 won't start after Windows 10 fall update
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 ...
- 251
10
votes
Accepted
ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist
The syntax you use is not valid for a unique index because a unique index does not create a constraint. You need to remove the ON CONSTRAINT and use the index expression instead.
This works:
INSERT ...
- 92.6k
10
votes
Accepted
How to compare xmin and txid_current() after transactions ID wraparound?
You can strip the added epoch to match the value in xmin, i.e. extract the 4-byte integer from the bigint. Since xmin is type xid and not (signed!) integer, we compare the text representation instead:
...
- 150k
10
votes
Accepted
Find which functions are used
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 ...
- 150k
10
votes
Accepted
Why do my queries take longer together than when I run them separately?
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 (...
- 28.7k
10
votes
Accepted
Execute a series of commands in PSQL
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)...
- 28.7k
10
votes
Accepted
Aggregated column causes full table scan even though the right index is present
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 ...
- 92.6k
10
votes
Accepted
Why does this implicit join get planned differently than an explicit join?
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:
...
- 150k
10
votes
Accepted
Find rows in which the column doesn't contain a "space"
I suggest you provide explicitly the characters you want to be considered as "white space" and excluded to a regex:
where name !~ '[ \t\v\b\r\n\u00a0]'
Characters:
\s white space (space, \r,...
- 92.6k
9
votes
Accepted
Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?
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
...
- 835
Only top scored, non community-wiki answers of a minimum length are eligible