Hot answers tagged

5

A couple of things that have caught you out here. In the SQL Server version using the Point, the order of the coordinate is Lat Lon, eg Y X. The OGC Point construct is X Y or Lon Lat. The next is that the PostGIS query you've posted actually returns 8.20039023523232e-005 which while appearing similar to the SQL Server result, is quite a lot smaller. The ...


3

We're now looking for a way to monitor the replication; mostly to verify that the slave server is still up-to-date. For monitoring replica lag, there are several ways that give slightly different answers, depending on which version of Postgres you are using. A simple query that can be done directly on the standby is: SELECT (CASE WHEN ...


2

You have to do with users the same thing you're doing with days: WITH days AS ( SELECT generate_series(current_date-7, current_date, '1d')::date AS day ), eves AS ( SELECT user_id, created_at::date AS full_day, COUNT(*) as evs FROM logged_events WHERE logged_events.created_at >= current_date-6 GROUP BY user_id, full_day ), ...


2

A UNION (or perhaps a UNION ALL, for reasons that I will note below) is a reasonable way to do this. There are a couple of ways to write the UNION. You did the first, which is to aggregate at each individual table level and then UNION the aggregated results. Is there a possibility that two different tables will have the same object in them? That is, ...


2

The syntax you use is not valid for a unique index because a unique index does not create a constraint. You need to remove the ON CONSTRAINT and use the index expression instead. This works: INSERT INTO journals (ext_ids, title) VALUES ('{"nlmid": "000"}', 'blah') ON CONFLICT ((ext_ids ->> 'nlmid'::text)) DO NOTHING;


2

In your 1st query there's a NULL returned by the subquery and you're a victim of Three-valued-logic. See also: https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/ A simple workaround is to add WHERE propid IS NOT NULL. But better switch to NOT EXISTS instead, which treats UNKNOWN as FALSE (i.e. Two-valued-logic): ...


1

Thanks to Amacvar on reddit for pointing me to the answer: max_standby_archive_delay and max_standby_streaming_delay have to be set for longer period than the longest query you want to run on the hot-standy. From https://www.postgresql.org/docs/current/static/hot-standby.html It is important that the administrator select appropriate settings for ...


1

From the PostgreSQL documentation: CREATE TABLE new_table AS SELECT * FROM ab1_2; Replace the * with the field names (with alias if you need to change the name) you need. You can put in the other 5 tables as UNION: CREATE TABLE new_table AS SELECT * FROM ab1_2 UNION SELECT * FROM ab3 UNION SELECT * FROM ab4 UNION SELECT * FROM ab5 UNION SELECT * FROM ...


1

While there's still no way to do anything about the select all columns but one bit, but you can use json_agg(to_json(b.col_1, b.col_2, b.col_3 ...)) to get a json array of jsons each in the format {"col_1":"col_1 value", ...}. So the query would look something like: SELECT a.id, a.name, json_agg(to_json(b.col_1,b.col_2,b.col_3...)) as item FROM a ...


1

I just made something similar for a colleague. Essentially I made a hidden table that contained one row for each (user,role) pair with suitable constraints. The user table was then a view of the hidden table with all the roles assembled into an array. I then made it possible to insert into the view by adding an appropriate rule. Here is how: trailer=# ...


1

It depends on the activity on the table. It there is no activity then there should be no difference.


1

In your example, the maintenance_work_mem you have set of 256MB should be seen by the subsequent CREATE INDEX CONCURRENTLY command, because you have changed this GUC inside your session. In fact, the docs suggest bumping up maintenance_work_mem (as you showed) for just such a purpose.



Only top voted, non community-wiki answers of a minimum length are eligible