22
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 ...
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.
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 ...
9
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('...
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
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
SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);
Result:
col
---
one
two
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
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,
...
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
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
109k12 gold badges237 silver badges370 bronze badges
7
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 ...
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)
...
6
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 ...
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
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 ...
6
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 ...
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 ...
6
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'...
6
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)
...
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
I would argue that this is a bug in the hash index code. When you create an index on an already-populated table, it tries to pre-size the index to hold all the data so that it doesn't have to keep splitting buckets as the index is created. But the code for doing this does not take the NULL fraction of the column nor (apparently) the selectivity of the ...
6
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 ...
5
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 ...
5
You need to put the INSERT INTO "name-job" into another CTE. You could also put the last insert into "name-city" into a CTE as well, and add a SELECT to get all the inserted ids (and anything else you need from the inserted rows) back.
WITH
x AS
(INSERT INTO public.name VALUES (DEFAULT) RETURNING id),
y AS
(INSERT INTO public.job VALUES (DEFAULT) ...
5
I cannot reproduce this with current Postgres 10.3. (This error should never occur.) Simple test:
dbfiddle here
I suspect you are running an outdated point release. Postgres 10.2 fixed some bugs that fit your case exactly. The manual:
Fix assorted failures to apply the correct default value when
inserting into an identity column (Michael Paquier, ...
answered Mar 13 '18 at 20:59
Erwin Brandstetter
109k12 gold badges237 silver badges370 bronze badges
5
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 ...
5
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
50.3k10 gold badges98 silver badges131 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
