Tag Info

Hot answers tagged

4

While you EXPLAIN your query, in the first instance, you can see that the planner estimates the total row count to 1 Index Scan using mytable_numfield_idx on mytable (cost=0.00..8.27 **rows=1** width=193) Using a index in this case is beneficial as there are hardly any rows matching your WHERE condition. In the second case, there are around 1080 rows ...


2

Postgres.app has a short documentation here: http://postgresapp.com/documentation/ which tells among other things: Configure your $PATH Postgres.app includes many command line tools. If you want to use them, you must configure the $PATH variable. If you are using bash (default shell on OS X), add the following line to ~/.bash_profile: ...


2

Too bad If you cannot change the query at all, that's too bad. You won't get a good solution. If you had not table-qualified the table (run.frames_stat), you could create a materialized view (see below) with the same name in another schema (or just a temporary one) and adapt the search_path (optionally just in sessions where this is desirable) - for hugely ...


1

You can use pg_stat_replication along with pg_xlog_location_diff. Checking the differences can tell you how many bytes of xlog have flown from the master to the slave.


1

In plpgsql code (not in plain SQL!): EXECUTE format($$COPY (select * from %I.portal) TO '/tmp/out1.csv' WITH CSV$$, cname); cname being the variable holding a (yet unescaped) schema name. You need to understand: dollar quoting the format() function SQL injection and how to defend against it: SQL injection in Postgres functions vs prepared queries ...


1

Three misunderstandings: You cannot return data from a DO command. You cannot SELECT without target in plpgsql code. That's what the error message tells you. You don't need either for a simple SELECT statement. Just run the statement itself: abc() { $PG_CMD 'select * from customer' }


1

You are not doing anything wrong per se, and the documentation is not wrong. But the initscript from CentOS doesn't support you doing things this way, so you have to change the initscripts to not check for these files, since PostgreSQL will check for it itself and just try to start the database even if these files are not there. So to test it out, comment ...


1

Yes, as long as you install version 9.1 (minor version doesn't matter). And normally you don't need to copy/move them anywhere, the installation will notice there is already a database system set up and will use it. Of course taking a backup is never a bad idea. Then again, the reason for it being offline should be clear from PostgreSQL logs. Memory ...


1

I would draw a text backup and search it with vim or any tool of your choice. As far as plpgsql functions are concerned (there is one behind every trigger), you can query the system catalog pg_proc: SELECT * FROM pg_catalog.pg_proc WHERE prosrc ILIKE '%dblink%';


1

Aggregate FILTER clause in Postgres 9.4+ Since Postgres 9.4 there is a clean and fast (SQL standard) way: SELECT count(*) FILTER (WHERE score BETWEEN 0 AND 3) AS low , count(*) FILTER (WHERE score BETWEEN 4 AND 7) AS mid , count(*) FILTER (WHERE score BETWEEN 8 AND 10) AS high count(*) AS total -- adds up, unless NULL or other values ...


1

Just use conditional SUM() statements per column for each number range. The total can be summed by just using SUM(1), assuming all of the data in the table is within one of the ranges - if not, just restrict it as with the others. select sum(case when score between 0 and 3 then 1 else 0 end) as minrange, sum(case when score between 4 and 6 then 1 ...


1

You could try creating an INSTEAD OF SELECT rule on the table .. although this might break the application (depending on what all actually uses the table in question) CREATE RULE "RETURN_MODIFIED_SELECT" AS ON SELECT TO run.frames_stat DO INSTEAD <MY QUERY FROM BELOW>; I have not used RULEs that much, personally - so I may have this ...


1

cascadus2 isn't a number. But you can strip out everything that isn't a number then convert that to a number. select regexp_replace('cascadus2', '[^\d]+', '')::numeric result: numeric ------- 2



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