New answers tagged

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): ...


0

Yes, adding indexes could cause IO wait to increase. Perhaps without the index, you are doing a lot of full scans of the table, thousands or millions of blocks, to get just one piece of data. But the IO wait is very low, because the kernel read ahead keeps the pump primed so your process doesn't wait on IO (instead it uses a lot of User CPU to filter ...


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 ...


0

You need \set (not \pset!) to set psql variables. Unlike assignment in a Unix shell the psql assignment is not capable of arithmetic operations. You could use the psql command \! to execute shell commands, but I'll suggest two different approaches: Option 1: Let Postgres calculate and set new variables with \gset The manual about \gset: Sends the ...


0

I will agree with @A_Horse_With_No_Name and mention that PostgreSQL is a excellent open source DB engine that has grown by leaps and bounds. It's fully ACID compliant out of the box and has a lot of features you can use. If you were deciding just between MariaDB and MySQL though I would go with MariaDB personally. Many large companies such as Wikipedia ...


0

A view is just a normal table with a rule, so this makes sense. See here: postgresql.org/docs/9.5/static/rules-views.html


0

I know of no such limit, although I have never had more than tens of materialized views, not thousands. However a way of hedging against such a problem is to make one materialised view with the customer as a column and create an index on the customer column. That way, Postgres will be able to pick out the relevant parts of the view very quickly. You ...


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;


0

The simple answer is: create table points ( p POINT not null); create table lines ( l LINE not null); Then inserts look like this: insert into points (p) values (POINT(1.2, 123.1)); insert into lines (l) values (LINE(POINT(1.2, 123.1), POINT(-5, -123))); You probably want to build indexes if you want to have some lookups on them. Also a primary key ...


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 ...


0

I fixed the problem by changing folder permissions on the Postgresql ODBC driver folder and giving read/execute access to Network Service. Because MS DTC runs under network service, which I confirmed by looking at the service properties. That got rid of the error!


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 ), ...


-3

|1|2|2012-01-01|12:00:00|1.3459|1.3645| SELECT col1, col2, col3, col4, AVG(col5) col5, AVG(col6) col6 FROM table_name GROUP BY col1, col2, col3, col4;


0

Joining the mailbox table to a "FolderNames" table using a foreign key will be problematic if any particular user wants to change the name of any given folder, without changing that name for everyone else. You could get around this by having a nullable CustomFolderName column in the mailbox table. Depending on the expected number of accounts, storing the ...


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=# ...


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, ...


0

You could write a join, but thats probably not shorter. As for adding the table name, you can hardcode it with the table name. SELECT objart, COUNT(*) AS "Number of objects", "Table_1" AS "Table_Name" FROM "SchemaA".table_1 GROUP BY objart UNION SELECT objart, COUNT(*), "Table_2" FROM "SchemaA".table_2 GROUP BY objart UNION SELECT objart, COUNT(*), ...


1

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


0

This was tricky but fun. Using a recursive query: with recursive v(l, f) as ( -- select Letter, Figure from table values ('A', 1), ('B', 1), ('B', 2), ('C', 2), ('C', 3), ('D', 3), ('E', 4) ), r as ( select * from ( select array[l] as l, array[l] as al, array[f] as f, array[f] as af, 1 as g, 1 as n from v order by l, f ...


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 ...


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.


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 ...


0

It was operator error. My process to run daily base backups was not being run, so I had weeks of WAL segments, and that's what was causing it to fill up the disk and take forever. Once I got them all imported, I ran a base backup and then did the import again, and it was done in less than a minute. Do frequent base backups!


0

If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted That happens automatically if any exception is raised inside a function (by you or by Postgres). A function is always atomic and can only be run within a transaction context - which is the main distinction from real stored procedures (currently implemented in Postgres). ...


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 ...


0

update cartographic_text_vm set primary_key = new.primary_key from (select * from cartographic_text_vm where (fid, ckpt) in (select fid, min(ckpt) from cartographic_text_vm group by fid) ) new where cartographic_text_vm.fid = new.fid and cartographic_text_vm.primary_key != new.primary_key;


0

After further investigation, to specify the default initdb arguments used in pg_createcluster, the below was added in /etc/postgresql-common/createcluster.conf: initdb_options = '--data-checksums' Then pg_createcluster was run as normal and show data_checksums displays: data_checksums ---------------- on (1 row)


1

Two remarks: This is called an associative table, not pivot. You already do a join, you just use the old syntax: SELECT * FROM tbl_profile_follow A JOIN tbl_profile_follow B ON (B.profile_id = A.profile_id AND B.followed_id = A.followed_id) AND (B.profile_id = A.followed_id AND B.followed_id = A.profile_id) This is another way ...


2

The query can be made easier: select least(profile_id,followed_id), greatest(profile_id, followed_id), count(*) from tbl_profile_follow group by least(profile_id,followed_id), greatest(profile_id, followed_id) having count(*) > 1; Using this approach you can also create a unique index on the table create unique index idx_unique_pair on ...


4

Basically it's a crosstab query: PostgreSQL Crosstab Query The dynamic result type is a problem, though. ... is it possible to create a dynamic query which will output the right thing regardless of the number of runs? No. Currently (including Postgres 9.6) not possible with a single SELECT statement. Not unless you know the return type at call time ...


0

According the pg_createcluster usage message, initdb arguments have to go after a double dash. pg_createcluster --datadir=foo/cluster_name 9.3 cluster_name -- --data-checksums


4

Since you need a subquery in either case, I would use a plain aggregate in the subquery (may be cheaper): SELECT count(*) FROM (SELECT race_id, max(rating100) AS rating100 FROM horse_main GROUP BY 1) x JOIN horse_main h USING (race_id, rating100) WHERE h.race_result = 1; If there are many rows per race_id, it will be faster to get ...


1

How is the REDO location determined exactly. When a Checkpoint starts, does it literally say "REDO location is the current WAL record + 1"? In essence, yes. Exactly, no. There is code in there about rounding up for cases where the record will not fit in the remainder of the log file and other corner cases. To get an exact answer you have to read the ...


4

Your query is quite close. In addition to the max rating100 per group, the derived table should also return the individual rating100 values, so that you can check if the row's rating matches its group's maximum. SELECT COUNT(*) FROM ( SELECT horse_name, race_id, race_result, rating100, max(rating100) OVER (partition by ...


0

Using an index consisting of the columns display_name, id, active and is_company would enable the query to be run using the index alone. Ordering the columns in the order provided would remove the sort requirement. This comes at a slightly update cost, and could replace the ix_displayname index. If active were rare, it would be faster to order the columns ...


2

Index Only: Will not visit the heap at all for a block if the block is marked as all-visible in the visibility map. If it does need to visit a block, it might do so on multiple occasions, and those occasions might be separated from each other by a lot of time because multiple distant index entries can point to the same block. During the intervening time, ...


2

When the standby initially receives the log records, the are written to the standby's pg_xlog directory. So those records are there for safekeeping, but they have not yet been applied to the shared_buffers and the data files (and the pg_clog, etc.) Until they are applied, anyone connecting to the standby cannot see the effects of those records. It is kind ...


1

Do I have to write SQL for that manually? Yes, but it's not that hard: create table original (id integer, url text); insert into original values (1,'VeryLongURLText'), (2,'VeryLongURLText'), (3,'LoooongURLText'), (4,'LoooongURLText'), (5,'LoooongURLText'); create the dictionary create table dictionary (id serial, url text); insert into dictionary ...


2

I think the complexity of performing this task is small and does not change with size. The same SQL that will find a missing recipient from 100 users will also find a single missing recipient from 100M users. The time to do so, however, is likely to be linear in the number of posts and the number of users. I can see two ways to organize the data. One is a ...


0

Without knowledge of your schema, query attempted and statistics from explain analyze, any response can only deal in generics. In this sense and in terms of SQL, there are generally two commonly used strategies for dealing with finding missed relatinons: NOT EXISTS and LEFT JOIN x WHERE x IS NULL. NOT EXISTS: SELECT * users WHERE NOT EXISTS (SELECT 1 FROM ...


0

After a lot of help from over at the PostgreSQL irc channel it was determined that the database was corrupted. The most likely cause of which was a hardware error on my ageing PC but also and much less likely a potential PG bug. I'm going to have to start again


0

You should get the maximum value from c.pay then: Select b.enumber, b.bday, max(c.pay) as current_pay From employee b inner join humanr c on b.empid = c.empid group by b.enumber, b.bday


3

There are many ways to do this. Many will work both in SQL Server and Postgres, like using the ROW_NUMBER() function: WITH cte AS ( SELECT e.enumber, e.bday, c.empid, c.pay, ROW_NUMBER() OVER (PARTITION BY c.empid ORDER BY c.id DESC) AS rn FROM employee AS e INNER JOIN humanr AS c ON e.empid = ...


0

In short, No. One of the statistics which PostgreSQL collects is the correlation between the values of a column (actually the ranks of those values) versus the location of that row in the table. You can find that result in pg_stats.correlation for your specific tablename and colname. However, the particular query you give is not one of the situations ...


0

you can use something like... SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1 ...as a subquery (substitute name of your id field for "id") and wrap it up like this... SELECT b.* FROM btmt b, (SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1) As z WHERE b.id = z.id AND z.cnt > 1 LIMIT 24; Hard to tell exactly what you're looking for, but that ...


-1

Another option is to use 4 INTEGER or 2 BIGINT columns.


3

An index is added in PostgreSQL, too, when a PRIMARY KEY or a UNIQUE constraint is created, as it is clearly stated in the docs. See CREATE TABLE: PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns. ... ...


0

Okay ... Upon installation of postgres another service was using the port 5432 so postgres took 5434. I didn't notice. Works now


0

If you are looking for a REST API, there is pg-manati a Nodejs package to automatically manipulate your data in PostgreSQL via the API. Disclaimer: I am the author of the project.


0

I use gdrive (follow the installation and set-up guidelines from the link). Once gdrive is installed, I dump my own databases to a /bak folder daily, and then upload my files to Google Drive's PGBAK folder using gdrive. I use the following script in /etc/cron.daily/: #!/bin/bash PATH=$PATH:/usr/local/bin BAK=/bak # backup globals (database names, users, ...



Top 50 recent answers are included