26
This is to implement the feature found in the standard. (copied from a draft, date: 2011-12-21):
4.15.11 Identity columns
The columns of a base table BT can optionally include not more than one identity column. The declared type
of an identity column is either an exact numeric type with scale 0 (zero), INTEGER for example, or a distinct
type ...
19
SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);
Result:
col
---
one
two
13
bigserial is not a type. It's a pseudo-type, a notational convenience that is resolved to type bigint internally, plus a sequence, a column default, a dependency and an ownership.
Basic commands to convert an existing bigint column with existing rows to a bigserial:
CREATE SEQUENCE tbl_tbl_id_seq;
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval('...
13
You know how there are OLD and NEW record-variables for FOR EACH ROW triggers?
Transition tables are the FOR EACH STATEMENT equivalents. They're tables with the old and new tuples, so your triggers can see what changed.
13
tid
See the manual page, Chapter 8. Data Types > 8.18. Object Identifier Types. It explains that the data type is Postgres-specific, and known as tid.
A final identifier type used by the system is tid, or tuple identifier (row identifier). This is the data type of the system column ctid. A tuple ID is a pair (block number, tuple index within block) that ...
13
The default json->text coercion outputs with a double quote (") because coercing from text to a json string requires you to double-quote your input. To get rid of the double-quotes, use TRIM
SELECT x, trim('"' FROM x::text)
FROM json_array_elements('["one", "two"]'::json) AS t(x);
x | btrim
-------+-------
"one" | one
"two" | two
(2 rows)
...
answered May 27 '18 at 20:30
I Support The Boycott
50.7k32 gold badges180 silver badges380 bronze badges
13
"pg_wal" cleans itself up. You should almost never touch pg_wal by hand. If it is not cleaning itself up, you need to figure out why and fix the underlying issue.
One possible reason is that you have a replication slot which is holding it back. Either a replica is using a slot and is unable to keep up. Or you have a slot which has no replica ...
12
You need to declare the type of the column, too (INT or BIGINT or
SMALLINT):
ALTER TABLE sourceTable
ADD COLUMN ogc_fid int -- int or bigint or smallint
GENERATED BY DEFAULT AS IDENTITY;
Also be sure to use the latest point release. As the IDENTITY has only been recently added, there were bugs affecting this particular command ...
11
I really like Craig's explanation of the feature. The SQL-2011 Spec defines them in the context of a trigger as "a collection of rows being deleted, inserted or replaced is known as a transition table." A similar explanation is provided in the docs,
While transition tables for AFTER triggers are specified using the REFERENCING clause in the standard way, ...
answered Jun 28 '17 at 6:16
I Support The Boycott
50.7k32 gold badges180 silver badges380 bronze badges
11
Your example suggests duplicate rows in the VALUES clause itself - which would result in:
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
Can be solved by folding duplicates in the input. See:
How to include excluded rows in RETURNING from INSERT … ON CONFLICT
But a related problem affects your outlined query as a whole, and that'...
11
Error: Invalid data directory
This happens when pg_wrapper is unable to figure out the data directory from the configuration. pg_wrapper is the layer on top of postgres that can juggle between several live PostgreSQL instances on debian-based systems. As installed by the postgresql-client package, psql is a link to pg_wrapper on such systems:
/usr/bin/psql ...
10
The key to performance is a matching multicolumn index of the form:
CREATE UNIQUE INDEX ON people (firstname, id);
UNIQUE, since sort order can be ambiguous without it, and you can get arbitrary results from peers.
A UNIQUE or PRIMARY KEY constraint serves as well.
While the first column is checked for equality like in your example (or sorted in the same ...
answered Oct 10 '18 at 23:32
Erwin Brandstetter
134k17 gold badges315 silver badges462 bronze badges
10
You need to do the unnest in a sub-query:
select d."key", array_agg(distinct x.v)
from data d
cross join lateral unnest(d."values") as x(v)
group by d."key";
Set returning functions (like unnest()) should in general be used in the from part of a query. But to be able to reference a column from the table you need a lateral join.
from data cross join ...
answered Jul 3 '19 at 14:23
a_horse_with_no_name
63.8k12 gold badges122 silver badges161 bronze badges
9
From the docs:
Creating a subscription that connects to the same database cluster
(for example, to replicate between databases in the same cluster or to
replicate within the same database) will only succeed if the
replication slot is not created as part of the same command.
Otherwise, the CREATE SUBSCRIPTION call will hang. To make this work,
...
9
I have replaced artful-pgdg with zesty-pgdg in /etc/apt/sources.list.d/pgdg.list and it worked for me
https://wiki.postgresql.org/wiki/Apt/FAQ
I am using a non-LTS release of Ubuntu
Non-LTS releases of Ubuntu are only added to the repository if the packages from the latest LTS release are incompatible with the release in question. Using the latest LTS ...
9
This is generally hard to optimize: no direct operator or index support for jsonb for this kind of test.
EXISTS should at least be faster than what you have, while also avoiding duplicate rows (where multiple array elements match) and the additional (redundant) column pil in the result:
SELECT *
FROM documents d
WHERE EXISTS (
SELECT FROM ...
9
For the purpose of this question, I'll assume employee_details.name to be defined UNIQUE. Else, the whole operation wouldn't make sense.
You cannot nest a data-modifying CTE like you tried (as you already found out the hard way) - and you don't need to. This query would achieve your objective:
WITH e AS (
SELECT name, employee_id
FROM ...
9
Test setup
Your original setup in the fiddle leaves room for improvement. I kept asking for your setup for a reason.
You have these indexes on film_actor:
"film_actor_pkey" PRIMARY KEY, btree (actor_id, film_id)
"idx_fk_film_id" btree (film_id)
Which is pretty helpful already. But to best support your particular query, you would have a multicolumn ...
answered May 21 '18 at 17:52
Erwin Brandstetter
134k17 gold badges315 silver badges462 bronze badges
8
Case insensitive or accent-insensitive collations cannot be used prior to PostgreSQL 12, because internally PostgreSQL considers that strings with a different binary representation are not equal. When the collation-aware comparator says they are equal, it uses the non-collation-aware strcmp() function as a tie-breaker, to obtain what Unicode calls a "...
8
Your problem is related to this point in the documentation:
When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound.
(emphasis added)
So in fact in
CREATE TABLE parsel_2014_10 PARTITION OF parsel_part
FOR VALUES FROM ('2014-10-01') TO ('2014-10-31')...
8
Assuming the central piece of information:
with ~15% of the rows having state = 'open' and closed IS NULL
is supposed to mean the same 15 % of all 1031584 rows meet both these conditions (all details matter!). Both conditions should perform equally - returning around 155k rows (!)
Your query plans show 37346 qualifying rows, ~ 3.6 % not 15 %. Something'...
7
It appears you use the same functions as before:
https://www.postgresql.org/docs/10/static/functions-info.html
pg_get_serial_sequence returns the name of the sequence associated
with a column, or NULL if no sequence is associated with the column.
If the column is an identity column, the associated sequence is the
sequence internally created for the ...
7
postgres user
The user account named postgres (by default) created by the installer is actually a macOS user account.
Apple allows deleting a user account in the more recent versions of macOS: System Preferences > Users & Groups > - button in list, after authenticating with padlock icon in lower corner.
In older macOS versions that do not delete user ...
7
Your ORDER BY clause is on:
engagement(social) DESC NULLS LAST, "users"."created_at" ASC
But I suspect your index is just on:
engagement(social) DESC NULLS LAST
So the index is not capable of fully supporting the ORDER BY.
You can reproduce the same issue without using either JSONB or expression indexes. You might be able to salvage the situation by ...
7
If you have more than one join you could use comma separated USING statements:
DELETE
FROM
AAA AS a
USING
BBB AS b,
CCC AS c
WHERE
a.id = b.id
AND a.id = c.id
AND a.uid = 12345
AND c.gid = 's434sd4'
Reference
7
Quoting the manual:
A foreign key must reference columns that either are a primary key or
form a unique constraint.
So that's not necessarily limited to the PK. But if we start with pg_constraint, we get all FK constraints pointing to the target table automatically. No need to provide any key columns - except if you want to limit to certain FKs.
Using the ...
7
It needs to check if the table already exists and create only if not found?
There is no standards-compliant method at all, but PostgreSQL 9.1+ supports a common extension with the IF NOT EXISTS clause,
CREATE TABLE IF NOT EXISTS foo (
foo_id int PRIMARY KEY
);
If the table (foo in this case) doesn't exist, you'll get only a NOTICE (not an ERROR)
...
answered Jun 17 '18 at 19:33
I Support The Boycott
50.7k32 gold badges180 silver badges380 bronze badges
7
To replicate some specific tables, you can use logical replication
Another option is to not replicate at all and access the remote tables through a foreign table
answered Jul 20 '18 at 19:16
a_horse_with_no_name
63.8k12 gold badges122 silver badges161 bronze badges
7
It looks like pg_ctl wants a real terminal for output, which is not allocated by ssh when you simply ask it to run a command. According to the Postgres manual
On Unix-like systems, by default, the server's standard output and standard error are sent to pg_ctl's standard output (not standard error). The standard output of pg_ctl should then be redirected ...
6
How they're actually implemented in PG 10
You can see how they're actually implemented now using the test suite's expected output.
Some keys to take away from this.
You can specify where to start and how many to skip with a clause on table creation or through ALTER TABLE, START 7 INCREMENT BY 5
Inserting into a table with an identity column can now ...
answered Apr 6 '17 at 17:57
I Support The Boycott
50.7k32 gold badges180 silver badges380 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
