24
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 ...
13
SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);
Result:
col
---
one
two
12
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.
11
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('...
11
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 ...
10
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 ...
10
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)
...
10
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 ...
9
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, ...
8
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,
...
8
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 ...
8
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
120k16 gold badges269 silver badges410 bronze badges
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
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 "...
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
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
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 ...
7
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'...
7
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
120k16 gold badges269 silver badges410 bronze badges
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)
...
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
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 ...
6
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')...
6
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 ...
6
You just need to remove the parentheses around the columns in group by (col1, col2). This works in version 9.4 and previous as well:
delete from mytable
where (col1, col2) in (
select col1, col2 from mytable
group by col1, col2 -- <-- changed
having count(distinct col3) >1) ;
The reason that it fails (I think) is ...
6
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 ...
6
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
56.4k11 gold badges109 silver badges139 bronze badges
6
There are no global variables per se in Postgres. But we have "customized options" that can be streched for the puropose.
SET myvars.test TO '0.1';
Persists for the duration of the session (not just transaction). Use with:
SELECT current_setting('myvars.test')::float;
Be aware that values are stored as text. So you may need to cast as demonstrated.
To ...
6
After several hours of research and examining the current situation I think I managed to solve the issue. (Many thanks to fellow user ypercube for the inspiration and for Erwin Brandstetter who in parallel came to the same solution.)
So there were several layers of the problem.
1.) UPGRADE
Upgrading with pg_upgrade 9.3.2 --> 10.5 should be made in two ...
6
When I paste the value 1527012834506374 into https://www.epochconverter.com/ I see the warning:
Assuming that this timestamp is in microseconds (1/1,000,000 second):
Postgres' to_timestamp() assumes an epoch with seconds, not microseconds, so you need to use:
select to_timestamp(1527012834506374 / 1000000.0)
answered Dec 19 '18 at 15:39
a_horse_with_no_name
56.4k11 gold badges109 silver badges139 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
