New answers tagged postgresql
0
In addition to basic dump/restore, to have an automated robust refresh process, you can use Database Lab Engine by Postgres.ai https://postgres.ai/docs/, https://gitlab.com/postgres-ai/database-lab. It takes the data from the source in some way (in addition to "logical" copying, dump/restore, you can copy the data directory physically with ...
0
I found the solution to my problem.
I've added ODBC to the client machine with IP server + allowing port 5432 and every thing ran fine.
0
My first instinct is to use the metric system, but if you have to use imperial measurements, I suggest that you store the weights as ounces but have [Computed | Calculated | Virtual | Derived] columns - actually called Generated in PostgreSQL - see this wiki article about Virtual columns - which says that the "The industry standard" is:
column_name ...
0
There are three good reasons for normalizing your tables.
It minimizes redundancy of the data. In your example AAPL wouldn't need to be repeated for every record it belongs to in the main data table. It only exists in one record in the CompanyProfile table.
As you see, instead the CompanyID in your main data table is repeated, and that's ok, it's much ...
0
The referencing columns don't need to be indexed at all. If not, then some operations on the referenced table might be extremely slow (e.g. verifying that a row to be deleted on the referenced table has no referencing rows, or if it does than cascading actions to them) but if you never do those operations it wouldn't matter.
0
I think that lateral join (other than for function calls) just doesn't support parallel execution. Not everything that conceivably could support it has actually been implemented yet (and probably never will be).
Also, the DISTINCT feature also doesn't support parallel execution currently. But changing your example to get rid of that still does not show ...
0
You can create an ordering number rn as:
select x, row_number() over (partition by x) as rn
from t
order by 2
Nothing guarantees that they will be as far as possible apart, but it will at least distribute each x over the result set
1
One of the neat things with SQL is that it is closed under relational operators. I.e the result of a query is a derived table that you can query again. So you just apply avg to the result of your count query.
In addition, you can get both the counts and the average in a single query by using grouping sets. I'm using group by cube, since there is only one ...
1
Using a sub-select query you can apply the AVG function on top of your COUNT query to get the single average like so:
SELECT AVG(InvoiceCounts.total_invoices)
FROM
(
SELECT
COUNT (h_id) AS total_invoices
FROM maintenance GROUP BY house_id
) InvoiceCounts
0
Use AVG instead of COUNT
SELECT h_id AS house_id,
AVG (h_id) AS total_invoices
FROM maintenance GROUP BY house_id
ORDER BY total_invoices
DESC
9
If multiple processes are trying to seq scan the same table, it will try to line them all up so that all read the same pages at about the same time, to maximize cache hits. It does this by having the late comers start in the middle (where ever the existing ones happen to be at), and wrap around the end to finish the table at the page before where it started....
0
Your example which redirects the output of psql is not going to produce the same format, so that will be one obvious difference. A less obvious one may be that it reads the entire data set into memory before iterating over it to export, so is slower and more likely to crash with memory problems.
1
PostgreSQL doesn't remember the times when queries are run.
If you need that information, set log_statement = 'all' and include %m in log_line_prefix. Then the queries and their time are written to the log file.
0
An attacker can try to enumerate which users exist by attempting to log in as a bunch of different user names and seeing what error message is reported, or how long it took to report the error message (or in some cases, how much power the server used before reporting the error or how much noise it made or things like that) for each username. This can be ...
3
The secret is in the Rows Removed by Filter: 10115028:
It takes the sequential scan 17 seconds to find and return the first result row.
The optimizer has no idea how long it takes until the first row passes the filter. Since it doesn't make any difference for the quality of the plan, it just sets the startup cost to 0.
Both plans work the same: each of the ...
0
I got it to work with some help from nbk. Many thanks
CREATE TABLE temp (elapsed_time DATE);
INSERT INTO temp(elapsed_time)
SELECT MIN(current_table) FROM current_table;
SELECT AGE(MIN(elapsed_time)) FROM temp
0
You can combine pg_dump with a batch script that iterates over all databases:
I use something like this to dump my development databases:
@echo off
set PGBINDIR=c:\Program Files\PostgreSQL\13\bin
rem avoid the password and user prompt from pg_dump
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=********
set PGPORT=5432
rem create a text file ...
0
To backup a separate database, use the pg_dump utility
SET PGPASSWORD = my_password
pg_dump -U postgres database_name > database.sql
to restore a PostgreSQL database
SET PGPASSWORD = my_password
psql -U postgres database_name < database.sql
pg_dump is a utility for performing logical database backups, it is installed along with the PostgreSQL server ...
1
You can do a AGEfrom you MIN date
CREATE tABLE "table1" (date_column timestamp)
INSERT INTO "table1" VALUES ( NOW())
SELECT AGE(MIN(date_column)) FROM "table1"
| age |
| :--------------- |
| -12:07:06.199423 |
db<>fiddle here
0
With COPY, the server formats the data, and there is no formatting effort on the client.
You should test it – that should be simple to benchmark.
1
Since parallel_leader_participation is at its default value off, the leader participates in the sequential scan. The I/O times of the worker processes are listed individually, but the I/O time of the leader can only be found by subtracting the workers' times from the total time.
The parallel sequential scan took 65026.980 milliseconds, almost all of the time....
1
The immediate error is that you have num where it would have to be NEW.num. But there are more problems ...
I add : In fact, whatever I do (insert or update) the max num by gidTbl1 (tbl2) always has to have the pm corresponding in tbl1.
Trying to get this right with triggers is a pain, even without possible concurrent writes.
With possible concurrent ...
4
Excellent corner case examples. Both of these syntax variants are "explicit type casts", doing exactly the same. It just so happens that some special locations in SQL code only allow functional notation to avoid ambiguities.
As for your second observation:
One other location at which an explicit CAST is required:
CREATE INDEX ON ... ( CAST(<...
4
It is a bit weird, yes, but the grammar will only accept something syntactically similar to a function call in a function-in-FROM expression.
So that is indeed a trick you can use if you want an arbitrary expression in a FROM clause: surround it with an unnecessary CAST expression.
PostgreSQL will happily treat anything that looks like a function as a table ...
1
This is more a business process / UI race. It is often solved by deprecating / hiding values for a period before deleting and/or not accepting them as valid anymore.
One approach would be to add a DateDeprecated column to toys. Update it to the current date when you check and nobody has this toy as a favorite. Clear the field if/when the toy is selected as a ...
-1
Well, I don't know if this would help but using negative logic like "NOT EXISTS" means that every row has to be examined (locked) to determine if it meets the criteria. If you can find a way to make it positive logic like "EXISTS", it should only evaluate the rows that qualify both speeding up the query and reducing the locks. Just a ...
0
You have forgotten to show us the hint you got:
ERROR: unsafe use of new value "DONE_BY_PASSED" of enum type activity_state
HINT: New enum values must be committed before they can be used.
You also forgot to mention that you ran the ALTER TYPE and the ALTER TABLE statements in the same transaction.
Like the hint says, you have to commit the ...
1
That's exactly what pg_rewind was written for. It allows you to undo any transactions that have happened on node1 after node2 was promoted. It can be seen as a fast version of pg_basebackup in this case.
There is no guarantee that pg_rewind will succeed. It depends on whether you have all the WAL since the last common checkpoint of node1 and node2. If there ...
1
That may not be the most elegant solution, but it works:
with activity_cte (day, user_id, act1, act2) as (
values
('2020-01-01'::date, 1, 0, 1),
('2020-01-01'::date, 3, 1, 0),
('2020-01-02'::date, 1, 3, 2),
('2020-01-02'::date, 2, 0, 2),
('2020-01-02'::date, 5, 0, 1),
('2020-01-03'::date, 1, 1, 2),
(...
0
I believe a query like this is what you're looking for, in one transaction / one query batch:
WITH CTE_Users_GroupByFirstName AS
(
SELECT first_name, COUNT(1) AS first_name_count
FROM users2
GROUP BY first_name
)
INSERT INTO users
SELECT 'john', 'doe'
FROM users AS U
LEFT JOIN CTE_Users_GroupByFirstName AS C
ON U.first_name = C....
2
I suggest you to create a new cluster in your external drive. You can use initdb
to create a new PostgreSQL database cluster.
Here is a sample command;
C:\Program Files\PostgreSQL\13\bin\initdb --pgdata="F:\MyProject\pgdata\13\main"
After that you even can register your new cluster as a new postgresql server service by using pg_ctl like the one ...
1
You can do something with triggers
Or you do somewhat complexer INSERT
Which allows only 5 John in your table
CREATE TABLE "user" ("first_name" varchar(16),"last_name" varchar(50) )
✓
insert into "user"
select 'john', 'doe'
where (select count(*) from "user" where "first_name" = 'john') < 5
...
2
Just run the script with autocommit turned on (the default in psql) and use an explicit transaction block for those statements that should run in a single transaction:
DROP DATABASE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????
begin;
ALTER DEFAULT privileges IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES ...
1
I finally found out how to display a bytea column as a text!
I can use the function convert_from like so:
SELECT event_type, convert_from(metadata, 'UTF8') as metadata
FROM public.events
ORDER BY created_at DESC
LIMIT 100
Then I'll have the metadata column in a human-readable format.
2
There is no priority to choose because they have different purposes.
password_encryption in postgresql.conf tells how to hash a new password when it's changed or a new user is created with a password.
the field in pg_hba.conf tells what kind of authentication scheme should be used when a client that matches the rules attempts to connect.
When connecting ...
0
I'd say that the problem is that during the Gather phase, PostgreSQL has to move about 1400 rows between the parallel workers and the leader instead of the expected one ot two.
Try setting max_parallel_workers_per_gather to 0 and see if the query gets faster.
If that helps, you could ANALYZE the table and see if the estimates become more realistic and ...
0
We've ended up using the JOINs over CTEs, which implicitly applies the expected order, and queries looking like:
WITH
acl AS (SELECT set_config('scope.acl', 'VALUE', TRUE) "__acl"),
result AS (${query})
SELECT * FROM bypass FULL OUTER JOIN result ON 1 = 1
In which case, acl CTE runs before the result and ensures that RLS could pick ...
1
If fullname column is never altered manually (i.e. it must match firstname and lastname values always) then simply use generated column. It will be (re)calculated automatically during insert/update:
ALTER TABLE people
DROP COLUMN fullName,
ADD COLUMN fullName VARCHAR(200) GENERATED ALWAYS AS (firstName || ' ' || lastName) STORED;
DEMO
1
You might have better luck with the GIN index rather than GiST.
GIN doesn't support KNN the way GiST does, so you instead you would have to apply the match operation with appropriate value of pg_trgm.word_similarity_threshold, then sort the row which survive that.
SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel
...
1
Another suggestion is that you use GENERATED COLUMNS as follows.
I do know that this is potentially wasteful of space, but once PostgreSQL implements the VIRTUAL storage mechanism for GENERATED COLUMNS, this shouldn't be (too much of a) problem. And they're also available only in >= 12!
It has the further advantage of not requiring an extension - often ...
-1
If you truly want it to only run once for that particular record, you could make fullname a nullable field and change the rule to:
ON UPDATE TO people DO
UPDATE people
SET "fullname" = concat(new."firstName", ' ', new."lastName")
WHERE people.id = new.id
AND fullname IS NULL
0
the execution plan on Postgres 12 shows it went parallel and apparently It made it worse , where in Postgres 9 it went single threaded.
seems like postgres overestimated in every single step and eventually thought that is too much work and consequently it went parallel and made a huge scene for nothing.
probably If you update your stats and probably rebuild ...
0
Have you tried \a, \pset pager off and \pset tuples_only?
Let's start with \a and \pset pager off first:
$ psql
psql (9.3.22, server 10.13)
WARNING: psql major version 9.3, server major version 10.
Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.
=> \a
Output ...
1
No there is no difference
CREATE TABLE incoming_requests ("status" character varying(16) )
✓
select FROM incoming_requests WHERE COALESCE(TRIM(status), '') = ANY (array['', 'OK', 'ERROR']);
✓
db<>fiddle here
1
Is there a COLLATE clause value that would sort Case Insensitive?
en_US.UTF-8 as provided by the GNU C library does linguistic-aware comparisons that are meant to sort strings independently of the case.
Here's an example on Ubuntu 18.04, PostgreSQL 11.
=> with a(x) as (values ('a'),('A'), ('b'),('B'),('c'),('C'))
select * from a order by x collate "...
0
After looking at the source code of Postgres I have found that libpqconnect is running it while making a connection. There are many places but libpqconnect is the file where pg_is_in_reovery is also running after the search path and satisfying the my criteria.
1
My advice is to use a “deferred constraint trigger” to update the "Products" table whenever "Items" is modified.
Such triggers are AFTER triggers that are not run immediately after the statement, but at the very end of the transaction.
The advantage here is that the rows in "Products" won't be locked any longer than necessary, ...
3
You need to extract the value as text which is done using the ->> operator (-> returns a json value).
SELECT *
FROM job_request_table p
cross join json_array_elements(p.data_meta) as d(element)
where d.element ->> 'data_type' = 'Insurance';
However, given your sample data, this would result in the error "cannot extract elements from ...
2
This won't work. In particular, you cannot create a single index that will support conditions like
WHERE (user_properties ->> 'some random property')::date >= '2000-01-01'::date
for arbitrary attributes.
You will have to identify the properties you want to use in comparisons and create specialized indexes on these properties. In the above sample, ...
1
Use a GiST index:
CREATE INDEX ON hotels_hotel USING gist (name gist_trgm_ops);
and search like this:
SELECT name,
word_similarity('trade center new york mariott', name) AS sml
FROM hotels_hotel
ORDER BY name <->> 'trade center new york mariott'
LIMIT 5;
That will get you the five closest matches.
Top 50 recent answers are included
