New answers tagged postgresql
0
You could try adding a json array column (json[]), but it depends on your access patterns what the best column type would be.
Example:
ALTER TABLE tableName
ADD COLUMN timeTableRows json[];
And then:
INSERT INTO tableName
(
timeTableRows
)
VALUES
( array['{
"type": "DEPARTURE",
"scheduledTime": "2020-01-26T04:57:00.000Z",
"causes": []
}'...
2
You quoted your column aliases:
SELECT
tb_dim_equipe.no_equipe "EQUIPE",
tb_dim_profissional.no_profissional "PROFISSIONAL",
no_cidadao "CIDADÃO",
a.nu_cns "CNS",
sum(...) as "TOTAL"
Postgres, unlike standard-compliant SQL databases, normalizes unquoted identifiers by converting them to lower case, such that the reference to c.TOTAL is equivalent to ...
1
pg_dump -t will dump the definition of foreign key constraints. But will not dump the definition (or data) of the table which that constraint points to.
Similarly, if you have a check constraint which uses a function, the definition of the function will not get dumped.
I don't know the thought process behind dumping sequences. That does seem like a ...
1
Basically, I suggest a many-to-many design like this:
CREATE TABLE blog ( -- I suggest singular terms
blog_id serial PRIMARY KEY -- and descriptive names
, blog text NOT NULL -- NOT NULL!
);
CREATE TABLE tag (
tag_id serial PRIMARY KEY
, tag text NOT NULL -- NOT NULL!
);
CREATE TABLE blog_tag (
blog_id int ...
0
Neither, for MySQL:
SELECT ts, DATE_FORMAT(ts, '%W') AS W,
DATE_FORMAT(ts, '%a') AS a FROM ...
+---------------------+--------+------+
| ts | W | a |
+---------------------+--------+------+
| 2020-01-26 10:53:05 | Sunday | Sun |
+---------------------+--------+------+
That is, just use an expression.
1
A language sql function can't catch exceptions, you need PL/pgSQL for that.
To run a query and discard the result in PL/pgSQL you have to use perform. You write the query the same way you would write a SELECT statement, but replace SELECT with perform. So instead of select * from foo you use perform * from foobar. If you don't want to run a SELECT query, ...
1
The IP address should be followed by either a slash and a mask length (like /32) with no whitespace, or by whitespace and a netmask (like 255.255.255.255). What you seem to have here is whitespace and mask length. So you get a mask length being interpreted as a netmask. I am surprised that that doesn't throw an error upon parsing, but in any event it ...
0
Trigger after so I make an update on us instead of using TG_OP. Now it works as expected with this function :
create or replace function activite.calcul_alti_us()
returns trigger as
$body$
BEGIN
update us
set (alti_base, alti_sommet) = (l.alti - base, l.alti-sommet)
from log l
where us.numlog = l.numlog ;
return NEW ;
END ;
$body$
language 'plpgsql' ;...
0
There are many errors:
It must be a BEFORE trigger to modify the row about to be inserted.
There is no column base in table log.
TG_OP is always in upper case, so you must compare with 'INSERT'.
0
Per your configuration, you are exposing all the IPs to your database which is not recommended.
Please reload the config file using /usr/edb/bin/pg_ctl -D /datadirectorypath reload
Post reload, you may try to connect again.
1
here are two ideas that can be tried..
Run a script or other little app in the background that scans through the killing connections that do not match the established rules
it fires off every few seconds ..
Or create a system event trigger that fires off checks the user IP address and any other information that needs to be checked when the connection is ...
1
This query will give you the argument types and result types as arrays for any function:
SELECT f.oid::regproc AS function_name,
f.proargtypes::regtype[] AS argument_types,
CASE WHEN f.proallargtypes IS NULL
THEN ARRAY[f.prorettype::regtype]
ELSE array_agg(args.type::regtype) FILTER (WHERE args.mode = 'o')
END AS ...
0
I've been using syntax like "TABLE example1" ... equivalent to ... "SELECT * FROM example1"
... and you really shouldn't be doing either of these.
Always specify the columns that you want to retrieve explicitly.
Databases are inherently shared constructs and you have no control over whether or when someone [else] suddenly dumps two dozen blob fields ...
1
You need the btree_gist extension so that you can create a GiST index on an uuid:
CREATE EXTENSION btree_gist;
Now you can create an exclusion constraint:
ALTER TABLE reservations ADD EXCLUDE USING gist (
reservable_id WITH =,
reserved_between WITH &&
);
&& is the “overlaps” operator for ranges.
12
It's documented with the SELECT statement and it's called "TABLE Command" there.
In the SQL standard it's called an "explicit table":
The <explicit table>
TABLE <table or query name>
is equivalent to the
( SELECT * FROM <table or query name> )
This seems to be part of the standard at least ...
0
This actually seemed to work fairly well:
REVOKE ALL ON SCHEMA public FROM readonlyuser
But hey, time will tell :)
0
You have to revoke the users rights to the schema
Its kinda hard to revoke public schema as its default is well public which is granted automatically to any user granted access to database and all users are added to the public role.
Commands like this have to be run
REVOKE ALL PRIVILEGES ON public FROM public;
There are lots of gotchas with this
here ...
0
Here is a suggestion users_equal_user
create table user_equal_user (
ueu_user_id int primary key ,
ueu_link_user_id int);
Select * from users where user_id in
(select ueu_link_user_id from user_equal_user where ueu_user_id = pass_in_user_id);
This will provide a many to many relation your looking for and be unlimited
EDITED OP asked how to deal with A=...
0
There is method called continuous Wal_Archiving, that can simulate The Diff backups in MSSQL
You run pg_basebackup instead of pg_dump, then ship the WAL_Logs to the backups server.
To recover from a failure, restore the last good backup and replay the WALs from the point in time from the last backup.
Depending on configuration can be better than DIFF ...
0
This cannot be done with PostgreSQL alone. There are commercial, closed source solutions for that.
You cannot simply use your application with such a solution; the application has to be specifically written to support such a multi-master solution.
Usually it is best to avoid the complexity of multi-master replication and choose a different architecture.
1
Check the wait events in pg_stat_activity to see if your query is running or hanging.
In the latter case, examine pg_locks or use the pg_blocking_pids function to see which session blocks you.
If no locks are involved, see if the backend process consumes CPU or not. Optionally use strace to see what the backend is doing.
0
i tried using the #-
What specifically did you try?
Using #- works for me, specifically updatedJson #- '{0,values,0}'
1
The file is read whenever the foreign table is scanned, which can happen several times during processing of a query.
There is no caching except the file system cache of the operating system.
1
No it does not create a read or write lock on films table as this creates ACCESS SHARE LOCK;
to test this open two sessions
in session one run this command
begin;
create table films2 as
table films;
then in the second session run this command
Select * from films;
Update films set something = 0
If you need to lock films table you have to ...
1
Never mind, Its possible, I have tested with this. But we have to make sure that if you have any PK, Unique key it should unique across your source databases(all 4).Or on the subscription database create the table without the constraints.
1
If you really want to get the count once the INSERT is finished, you could do something like this.
with source_data as (
SELECT pkey, col1, col2
FROM table2
), input_count as (
select count(*) as source_count
from source_data
), new_rows as (
INSERT INTO table1 (pkey, col1, col2)
select *
from source_data
ON CONFLICT DO NOTHING
returning ...
1
Insert not needed for to count:
WITH
cte1 AS ( SELECT COUNT(*) cnt FROM table1 ),
cte2 AS ( SELECT COUNT(*) cnt FROM table2 ),
cte3 AS ( SELECT COUNT(*) cnt FROM table1 JOIN table2 USING (pkey) )
SELECT cte1.cnt "Records in table1",
cte2.cnt "Records in table2",
cte3.cnt "Conflicts count",
cte2.cnt - cte3.cnt "Potential inserts count"
...
0
You need an index:
CREATE INDEX ON downloads_activity (date_trunc('day',created_at));
Depending on the data type, both the index and the query might need time zone adjustment.
0
Select A.Daily,
count(distinct da.user) as Users
from
(
-- calculate all constants needed once
select date(d) as Daily,
date(d) - Interval '30' DAY as DailyStart,
date(d) - Interval '1' DAY as DailyEnd
from generate_series(current_date - Interval '10' DAY, current_date - Interval '1' DAY, '1 ...
0
The main thing you need is an ordering of the rows, since rows are unordered per se. Once you have an ordering, you can use the window function lag to access the previous row in that ordering.
Assuming that your rows are ordered by tstamp, that would look like
SELECT lat,
lon,
lag(lat) OVER (ORDER BY tstamp) AS prev_lat,
lag(lon) OVER (...
1
I do believe that the diagnosis of Laurenz Albe is correct but, at least for me on pg 11, setting cursor_tuple_fraction = 1.0 did not result in a parallel plan for queries launched from DBeaver. But what did work was setting the ResultSet fetch size in DBeaver to 0. Note that DBeaver will fetch all of the results for every query with this setting so you ...
1
You can use EXCEPT to remove the complementing rows.
SELECT friend1,friend2 FROM friends
WHERE friend1=2 or friend2=2
EXCEPT
SELECT friend2,friend1 FROM friends
WHERE friend1=2 or friend2=2;
But maybe using a CTE will perform better.
WITH buddies
AS ( SELECT friend1,friend2 FROM friends
WHERE friend1=2 or friend2=2 )
SELECT friend1,...
0
I had the same problem today. I discovered that you do have to specify the -h flag for it to work:
psql -U postgres -p 5433 -h localhost
You can also use the URI style connection string:
psql postgresql://postgres:<your_password>@localhost:5433
The default is "local socket" which does not work in the case of docker.
0
It appears your problem is regarding setting Environment Variables correctly. After installation, the command psql won't work because you need to set the correct PATH to this command in your Environment variables file - which will differ a little according to your version.
I just wanted to add a comment here for future inquiries.
I'm working on a MacOS ...
0
There is nothing built-in for Postgres.
But two very popular backup tools support differential backups:
barman
pgbackrest
0
No, you cannot use an index on one column for searches on another column. I second Jasens comment that you should consider doing away with the generated integer and using the timestamp instead.
0
If you consume 10000 sequence values per second, you'll have consumed 10000*3600*24*365 values per year, that is 3,1536E11. At that rate, it takes a couple of million years to exhaust a bignt sequence.
So you shouldn't lose any sleep over it.
1
force manually an autovacumm policy on mergency situations
This is not going to work. hot_standby_feedback doesn't prevent autovacuum from running. It lets it run but prevents it from removing rows that are not "dead enough". No amount of vacuuming is going to help if you don't get the long running queries on the replicas to go away first.
You can use ...
0
You can use the function json_array_elements to convert your array into a set of elements. Each element is itself an array, so you can use the -> operator to retrieve the second element. Once you have them, use a standard count(*), with the corresponding FILTER.
You can do it with the following SQL statement (the WITH helps you view the "step-by-step" ...
0
Necesito hacerle una consulta, verifico o sabe aproximadamente cuanto pesaba esa tabla/base a respaldar? ya que debemos tener ese dato . Muchas veces cometemos el error de automatizar este procedimiento sin limpiar los espacios y como consecuencia .. el script comienza ( crea estructura), pero se cae a mitad de camino por no tener el espacio suficiente para ...
1
If you have hot_standby_feedback = on, a manual VACUUM won't delete the dead row versions if there is a long running query on the standby.
The only way to do that would be to set old_snapshot_threshold to a value different from the default value. Then VACUUM (and autovacuum) will remove dead tuples even if an old transaction might still need them. Note that ...
0
If you don't have the required permissions, pg_dump will give you an error message rather than an empty COPY statement. Moreover, permissions do not apply to superusers.
You should verify what the database user used by pg_dump sees when selecting from the original table.
The only explanations I can think of are:
Somebody deleted all rows from the table.
...
2
A function may not see the same state of the database depending on whether it's VOLATILE or not, when this state is changing during the execution of the SQL statement it's called from, either because of the statement itself or because of another transaction if the isolation level is
Read Committed.
This is documented in https://www.postgresql.org/docs/...
0
If you have all binaries and the complete data directory, then you can copy all of that to a new computer and start Postgres using pg_ctl.
Note that the new target computer should ideally run the same Windows version and "bitness" (i.e. 64bit if the old one was 64bit - who uses 32bit these days?) as the old computer used. You can probably get away with a ...
1
Yes, that is the idea. In the case of a replication conflict PostgreSQL has only two options:
cancel the query
delay the application of replicated changes.
Setting max_standby_streaming_delay to -1 will delay replication indefinitely long.
There are ways to reduce replication conflicts:
Set hot_standby_feedback = on to remove replication conflicts caused ...
0
The technique for such requirements is called cursor. You start a transaction and use your API's functions or the SQL command DECLARE to create a cursor.
Then the query is only executed once, but you can FETCH the results in chunks.
2
There is no "better" between those two designs. They are just different. So it depends on what you want to store in your database. If every field you store belongs to just one object, then the first design is the better choice, but if you want to have fields and objects stored and each one can relate to another, the second design is better. Let me give you ...
1
For this problem I would use the date formatting functions (after setting lc_time to the appropriate locale)
SELECT to_char(timestamp, 'Day')
FROM fact_table
In general the table form is, to me, cleaner (when there is no suitable function)
Perhaps with an in-line table if there is a good reason to not have a permanent table.
SELECT day_name
FROM ...
0
Theoretically, your database does not have problem, if it is running.
But to verify it, first Stop your database, make a physical copy of PostgreSQL folders.
After that, start your database make an ordinary backup using pg_dump, after that try to restore this backup in a new environment, if you don't have a problem your database is ok.
If you have some ...
0
You are looking for the columns confupdtype and confdeltype in the catalog pg_constraint.
The codes mean:
Foreign key deletion action code: a = no action, r = restrict, c = cascade, n = set null, d = set default
Top 50 recent answers are included
