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.
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, ...
9
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 ...
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 ...
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
102k10 gold badges214 silver badges339 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)
...
6
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,
...
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
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
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
Case insensitive or accent-insensitive collations cannot be used consistently 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.
It must do that, at least because of hashing. ...
5
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 ...
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
102k10 gold badges214 silver badges339 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
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'...
5
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)
...
5
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 ...
5
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 ...
5
Aren't enums already ordered?
No.
This commit, from 2014, implies that BRIN indexes should work for ENUM types.
That's actually not what that commit says. From the commit-message on the link you provided
This type of operator class we call "Minmax", and we
supply a bunch of them for most data types with B-tree opclasses.
Since the BRIN code is ...
5
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
45.8k9 gold badges90 silver badges122 bronze badges
5
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'...
5
That's because from version 10 it has been renamed to pg_resetwal. And the binary is located in /usr/lib/postgresql/<version>/bin.
The pg_xlog directory was renamed to pg_wal in version 10 and several functions, tools and options as well, to reflect that change. See Postgres 10 release notes:
Rename write-ahead log directory pg_xlog to pg_wal, ...
5
Both do the same and you won't be able to measure any difference in performance.
Details
This is a string literal or string constant: '5'
The manual:
A string constant in SQL is an arbitrary sequence of characters
bounded by single quotes ('), for example 'This is a string'. To
include a single-quote character within a string constant, write two
...
Only top voted, non community-wiki answers of a minimum length are eligible
