Tag Info

New answers tagged

0

Postgres Text Search supports prefix search select 'housewarming'::tsvector @@ 'hou:*'::tsquery; --true I looked at amazon's search, and guess what? ta doesn't return entertainment


0

If you have many request and some semicolons use Transaction : In psql open editor mode \e: -- at start of edited text BEGIN TRANSACTION; -- ... your requests here -- save exit Back to psql : your requests are executed but change are not written : If all is OK, save changes with : COMMIT; else If you have errors or want to cancel : ROLLBACK;


1

Unfortunately that's not that easy because there is no "select any view" privilege (as e.g. Oracle offers). If you can put all views into a single schema, then this could be achieved by granting the select privilege on that schema. Something like this: First you need to create a role that can be used for this create role view_reader; The role only ...


0

Some years ago I tested different solutions (amongst others some similar to those from @ErwinBrandstetter) for merging overlapping periods on a Teradata system and I found the following the most efficient one (using Analytical Functions, newer version of Teradata have built-in functions for that task). sort the rows by start date find the maximum end date ...


0

There are Postgres extensions to handle Chinese. Have you tried http://pgxn.org/dist/zhparser/? You can then maybe port it to Vietnamese.


1

It's hard to tell what exactly your graphs say. But commit is designed to be slow. By default, Postgres waits for changes to be written to disk before it lets commit return. You can let commit complete before changes are written to disk with this option: synchronous_commit = off That should certainly help to spread the disk I/O required for commit over ...


2

I Will answer my own question, From: http://stackoverflow.com/questions/13505785/postgresql-database-size-is-less-after-backup-load-on-heroku The reason of this is its MVCC system. Every time you UPDATE any record in a database it creates another "version" of this record instead of rewriting the previous. This "outdated" records will be deleted by VACUUM ...


0

If you are on 9.4 something like this might be what you are after: select json_object(array_agg(id)::text[],array_agg(rw)::text[]) from( select id , ( select to_json(array_agg(row_to_json(t))) from (select typ,prop from bgb where id=b.id) t ) rw from bgb b group by id ) z;


1

You can simply do UPDATE my_tbl SET (a1, a2) = (a1 || 123465, a2 || 132456789) WHERE id = 321; To obtain a function, you simply have to replace the integer constants with the parameters. You can safely do it in a query language (aka SQL) function, no need for pl/pgsql at all.


2

You want current_setting: SELECT current_setting('pubsubpull.request_id');


2

Simpler view For this objective: The point of which is to give averages of the numeric columns for each hour .. it seems just as good to truncate to full hours, which is simpler and cheaper: CREATE OR REPLACE VIEW spp.rtprices_hourly AS SELECT date_trunc('hour', "interval") AS hour , node_id , round(avg(rtlmp), 2) AS rtlmp , ...


0

This is how I fixed my issue: `dpkg -l | grep postgres will show you the postgresql packages that have been installed. Run: sudo apt-get --purge remove with each package name, separated by a space, following remove. root@vps1484:/home# dpkg -l | grep postgres ii postgresql 9.3+146really9.1+148 ...


2

Tip: use psql's -E flag to get it to show you how what queries it is issuing for e.g. its \dm meta-command, and adjust from there. Here's a rough and not at all cleaned-up query, but it should do the trick... WITH matviews_with_keys AS ( SELECT c.oid, c.relname, c2.relname AS idx_name FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, ...


0

You have at least two options. The first one makes use of a small query and a text editor. We have to collect the schemata of our interest: SELECT nspname FROM pg_namespace; You can add a WHERE clause if you want to limit the scope. Copy the output and amend it, so you get a number of GRANT USAGE ON SCHEMA ... TO your_role; commands. Then just ...


2

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one. When you do so, I can only recommend two small things: Use DROP MATERIALIZED VIEW blabla CASCADE to get a ...


0

I've found it ! In Windows Service, i found that it was "adminpostgres" who was running the pgAgent service. I just had to edit the pgpass.conf in c:/user/adminpostgres/AppData/postgresql with my old config to make it works !


4

You can cut this down quite a bit - the first thing to notice is that you don't need the information in the author_titles table at all to get your results: select author_id , sum(case max_sold when 1 then 30 when 2 then 60 when 3 then 100 else 0 end)/count(*) max_items_sold_wavg , sum(sold)/count(*) items_sold_wavg , created_at ...


2

First of all, this is an error message from PostgreSQL, which is not the same as psql (the command-line client). The SET command sets run-time parameters of the Postgres server, which is completely independent from the client (psql in this case). Either way, it works for me. Testing the standard distribution of Postgres 9.4.1 on a Windows client set to a ...


1

Core feature is the window function lag(). Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!): CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int) RETURNS integer AS $func$ DECLARE del_ct int; BEGIN -- this may or may not be necessary: -- ...


1

I'm afraid it's not possible - at least according to the documentation Check this out here. To enable messages to be translated to the user's preferred language, NLS must have been selected at build time (configure --enable-nls). All other locale support is built in automatically. You have to configure the database when you install it. Is your ...


4

Generally, you can always wrap a query as subquery if you don't want to output all rows: SELECT title FROM (SELECT ...) sub; But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented). For the case at hand, it must be mentioned that you are using the additional module ...


0

This query will return 8 similar posts to the current post we are viewing. The number 5 is placeholder. It need to be overridden by variable. SELECT SIMILARITY(title, (SELECT title FROM gallery WHERE id=5)) AS sim, title, uri FROM gallery WHERE title % (SELECT title FROM gallery WHERE id=5) AND id!=5 ORDER BY sim DESC LIMIT 8;


4

You need to remove the status column from your index: CREATE UNIQUE INDEX builders_unique_house ON houses (builder_id) WHERE status IN ('PLANNING', 'CONSTRUCTING'); If you keep the status column, the index will contain e.g. ('PLANNING', 1) and ('CONSTRUCTING', 1) - that combination is unique. If you remove the status, only the builder_id will be ...


0

You must pass a locale that matches your desired encoding and is supported by the host as well. For example, if you don't want localised collations etc, you could pass --locale=C. Otherwise you might want --locale=en_US.ISO-8859-1 (or just en_US).


1

You won't see a "autovacuum" process. You will only see postgres.exe processes. And because autovacuum doesn't run all the time, you will only occasionally see an additional process starting and stopping (but it will still be named postgres.exe). If you want to verify that auto vacuum is really running you can change the configuration property ...


0

Replace the comma with a dot, which is the default "decimal point". ALTER TABLE lca_test ALTER COLUMN prevailing_wage TYPE numeric(10,0) USING translate(prevailing_wage, ',', '.')::numeric; But as long as you are not going to store fractional digits (numeric(10,0)), you should really use integer (covers -2147483648 to +2147483647 or bigint (covers ...


0

Sorry its Postgres 8.4 and its not a view SELECT relnamespace, relname, relkind FROM pg_class WHERE relname = 'template'; relnamespace;relname;relkind 2200;"template";"r" I found out why it wasn't working. I was joining with 2 other tables, so it was confused which tableoid I was asking for Although I found it odd that it wasn't giving me the usual ...


4

I'm ignorant of postgres SQL syntax, however this appears to be an issue with either the TYPE numeric(10,0) not containing decimal granularity (numeric(10,2)) would work, or the fact that the currency is in a european format that utilizes commas instead of decimals.


-1

If you include ID I would assume that you can join it back to itself on ID to get that information in one dataset.


2

In PostgreSQL 8.4+ or any other DBMS that supports windowing functions, the following approach will also work, and will probably be faster on large tables because the database can answer the query with a single scan of the main table (rather than aggregating on the first scan and then querying the main table again for matching rows): select ID, value_A, ...


3

Postgres has distinct on that does just what you are looking for: select distinct on (id) id, value_a, value_b from t order by id, value_b; SQLFiddle here You haven't specified what you would like to see when the are multiple values of value_b for an id though.


2

You can do a GROUP BY to get the minimum value of value_B and then join back with the main table. Something like this. select table1.ID, table1.value_A, table1.value_B From table1 inner join (select id,min(value_B) value_B from table1 group by ID) t1 on table1.id = t1.id and table1.value_B = t1.value_B; SQL Fiddle


0

select ID, value_A, value_B From t where (value_B,ID) in (select min(value_B),ID from t group by ID);


2

At a guess, your user isn't the owner of the tables and hasn't been granted rights to them WITH GRANT OPTION so it can delegate them. The database administrator must do something like: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser WITH GRANT OPTION and should probably also GRANT similar rights on SEQUENCEs, on the schema its self, etc. ...


1

There must be some kind of misunderstanding. tableoid is a system column that's available for any regular table in any version of Postgres since at least v7.3. Per documentation: Every table has several system columns that are implicitly defined by the system. tableoid is one of them. The solution I provided under the questions you refer to is also ...


1

I talked to someone who helped me find the answer. The correct syntax is, for anyone in future reference. The documentation mentions it, although it can be easy to miss. START TRANSACTION; DROP SCHEMA IF EXISTS replays CASCADE; CREATE SCHEMA replays; CREATE ROLE admins WITH PASSWORD 'changeme'; GRANT SELECT ON ALL TABLES IN SCHEMA replays TO PUBLIC; ...


2

I need to press the q key as required by less or man for the execution to finish and return to the command prompt? Sounds like your terminal's pager is taking over the output: you can tell psql not to send its output to the pager when you're using the -c 'select... ' invocation via: psql --pset pager=off -d database -c 'SELECT * from TABLE' You may ...


1

On Windows Server 2012, the command pg_config did not reveal the data directory for some crazy reason. But I found it by looking at the file C:\Program Files\PostgreSQL\9.4\pg_env.bat, which contained this line: @SET PGDATA=E:\POSTGRESQL That was the answer I needed.


1

By default, the results are entirely buffered in memory for two reasons: 1) Unless using the -A option, output rows are aligned so the output cannot start until psql knows the maximum length of each column, which implies visiting every row (which also takes a significant time in addition to lots of memory). 2) Unless specifying a FETCH_COUNT, psql uses the ...


1

If you install from apt-get you will be able to use apt-get upgrade option later and other apt-get build in options (available from Ubuntu). Furthermore apt-get installs binaries and manages their versions. You just install and nothing more. Installing from source gives you the ability to do a more detailed installation. (You can customize the build and ...


0

Check the server.log. That normally indicates why the issue is happening. I have faced the same issue and I could see that it was unable to find var/postgres/ postgres.auto.conf etc. I am using Mac so I used the following commands and it came back again. But I lost my data folder. brew uninstall postgres brew install postgres initdb `brew ...


2

On way would be to restore only the relevant table to a new empty dummy database. Then find rows with standard SQL tools. Not sure if you are restricted by Heroku in any way. On the shell, as postgres user (or any user with sufficient privileges) first create an empty staging DB: createdb -T template0 dummy If the DB isn't too big and you have plenty of ...


1

Simple approach (can split sets) Chain DELETE and INSERT with a data-modifying CTE for best performance and to be safe: WITH del AS ( DELETE FROM tbl t WHERE udate < (now() - interval '1 year')::date AND EXISTS ( -- is not the greatest SELECT 1 FROM tbl WHERE compid = t.compid AND seq > t.seq RETURNING ...


3

According to Luis Carvalho, one of the developers of PL/Lua, PL/Lua can use LuaJIT. However, you may find there are many more factors which affect the overall performance of the PL code you write, including the overhead of the language's bindings in PostgreSQL, data type conversions, familiarity of your developers with Lua vs. other languages and their ...


0

The child tables need to have indexes on their foreign keys too, cause postgres won't auto-create them: I think the code above will can help: CREATE INDEX rxmsg_vid_index ON rxmsg USING btree (vid ); CREATE INDEX drivers_vehicle_id_index ON drivers USING btree (vehicle_id ); And i dont know if you do that but the "point" column need to ...


0

You should take a look at my recent answer here to a related question regarding umlauts. Basically, the solution involves a "shadow" or "search" column for proper names. Basically, you use an ON INSERT trigger to populate your search column with the field you wish to search modified for your chosen search method - in this example, case insensitive. The ...


4

The documentation states the following: default_with_oids (boolean) This controls whether CREATE TABLE and CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT OIDS is specified. It also determines whether OIDs will be included in tables created by SELECT INTO. The parameter is off by default; in ...


1

In pg_hba.conf You should set # TYPE DATABASE USER ADDRESS METHOD local all all trust Authentication method should be TRUST, that will allow You to enter postgres without password and set it for postgres user. Below you see how to change password ;) ALTER USER postgres ...


0

You obviously have many rows with the same list_name per book_id and many rows per combination in your table ranking_points, which is very inefficient. book_id ... 32 (book_id, list_name) ... 1903 total rows ... 186023 You defined the column list_name character varying(255), which indicates it's a potentially long string. (A ...


3

In order to avoid overloading my server, the requests are queued and handled one at a time. That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details ...



Top 50 recent answers are included