Tag Info

Hot answers tagged

6

select string_agg(c,'') from ( select distinct regexp_split_to_table(lower(name),'') as c from data ) t The inner select generates one row for each character, and the outer then aggregates that to a long string. If you want the characters sorted, you can use an order by for the aggregate string_agg(c,'' order by c)


6

The name of the additional module hstore is derived from "hash store", because it was inspired by perl hash. Oleg Bartunov and Teodor Sigaev - the authors of the module - say so in their presentation here: Hstore — key/value storage (inspired by perl hash)


6

This should work but I'm not really sure if it's the best regarding efficiency: WITH copy_to_other_table AS ( INSERT INTO other_table (column_a, column_b) SELECT column_a, column_b FROM main_table WHERE column_a = 1 ), main_table_deleted AS ( DELETE FROM main_table WHERE column_a = 1 AND NOT EXISTS ...


6

This appears to be the cause of the problems, from the same page quoted by ypercube: Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same ...


5

Through version 9.3, the indirection array used for sorting had to fit in a single 1GB memory allocation. This created an artificial limit on the number of tuples which could be sorted in memory. Once that limit was reached, it had to switch to a disk sort, even if there was memory left over. This restriction was removed in version 9.4.


4

It shouldn't take long to get up to speed to a reasonable degree if you've used another RDBMS. Read some guidance on PostgreSQL for MySQL users to help you adapt to sequences vs auto_increment, ANSI-standard quoting (though you should be using that in MySQL already), the stricter data type checking, how authentication and roles work, psql's backslash ...


4

Yes you can. Per documentation: Note that the user performing the insert, update or delete on the view must have the corresponding insert, update or delete privilege on the view. In addition the view's owner must have the relevant privileges on the underlying base relations, but the user performing the update does not need any permissions on the ...


4

We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten That's not the case. In order to support rollback and crash-safety, PostgreSQL must write a new copy of every modified row, rather than modifying the row in-place. Twice, actually, because it must be written to WAL (a sequential ...


3

if you add a second disk i suggest to put data on to different tablespaces. a tablespace offers additional storage and is VERY easy to use: mkdir /whatever then in SQL: CREATE TABLESPACE myspace LOCATION '/whatever'; ALTER DATABASE x SET TABLESPACE myspace; of course you can also do this for single tables only. and yes, alternatively you can move the ...


3

As developer, not a full-time DBA, I use Postgres for some of my work. But it is not my focus. I found Postgres very confusing and frustrating when getting started. After a long career using 4D, I was an pro in relational database design and normalization, but a newbie with SQL and "black-box" database engines. Below is a list of the resources that helped ...


3

As I understand the question, you don't need a window function. Aggregate functions do the job: count() in the lowest level (-> row_ct). sum() the resulting row_ct in the next level (-> total_row_ct). SELECT row_to_json(selected_records)::text AS data FROM ( SELECT array_to_json(array_agg(row_to_json(records))) AS data , sum(row_ct) AS ...


3

On their webpage About they clarify that the limit is a lot huge than 4GB. Limit Value Maximum Database Size Unlimited Maximum Table Size 32 TB Maximum Row Size 1.6 TB Maximum Field Size 1 GB Maximum Rows per Table Unlimited Maximum Columns per Table 250 - 1600 depending on column types Maximum Indexes ...


3

I solved the mystery on pg irc channel - I had a long running query (idle in transaction) and they told me that a new index doesn't get used until all transactions that started before its creation don't finish. Killed the transaction, and now the indexes are back to normal.


3

Using debugging parameters like enable_nestloop should not change the results of a query. If it does that'd generally be a bug. However, you should not use enable_nestloop = off or similar in production. It's a very big hammer, and while it might appear to fix your immediate problem, it'll create performance problems elsewhere. A nested loop is often the ...


3

Use conditional counting: select count(case when year <= 1945 then 1 end) as pre1945, count(case when year between 1946 and 1964 then 1 end) as period2, count(case when year between 1965 and 1974 then 1 end) as period3, ... from ... where ...; This works because count() ignores null values and the case statement returns a null for ...


2

Not a direct answer to your question but you should try the first_value window function. It works like this: CREATE TABLE test ( id SERIAL NOT NULL PRIMARY KEY, cat TEXT, value VARCHAR(2) date TIMESTAMP WITH TIME ZONE ); Then, if you want the first item in each cat (category) you will query like that: SELECT cat, ...


2

I had something similar happen recently with a table of 3.5 million rows. My update would never finish. After a lot of experimenting and frustration, I finally found the culprit. It turned out to be the indexes on the table being updated. The solution was to drop all indexes on the table being updated before running the update statement. Once I did that, ...


2

I would look into partitioning. If partitioned by day, you could just drop the entire partition once it gets too old. You may even no longer have to vacuum. Also, overall performance might increase, since you're not inserting where you're deleting. You would just need to write the code to create new partitions and delete old ones. This is exactly what ...


2

So, as mentioned in the comments, since you apparently have the authority to modify the data model itself, there are perhaps better solutions available to you at this point. The direct answer to your question is to update your new prices table to reference your preferred products record in each duplication case, then purge the other duplicated products ...


2

Well, Apologies to all those SQL guru's - this might be a simplistic answer. Since the product table will only have one record per product you don't need the ID column and can just use the 'sku' as the key. Insert into that table a distinct list of the products e.g INSERT INTO products (sku,ProductName) SELECT DISTINCT (sku,ProductName) FROM ...


2

Either way, that's totally possible, given that all your parameters are of the same data type. EXECUTE ... USING happily takes an array, which is treated as a single argument. Access elements with array subscripts. create or replace function test_function(_filter1 text = null , _filter2 text = null ...


2

For PostgreSQL 9.2 and above there is no postgres system user (by default). It runs as NETWORK SERVICE. Create d:\tablespaces and give the NETWORK SERVICE (SERVICE RÉSEAU in french) "Full control" over it. That should do the trick.


2

Option 1: remove createdb for 9.1 When in doubt, use dpkg -S to learn which packages provide a certain command. Example: $ dpkg -S createdb postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/tutorial-createdb.html postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/app-createdb.html postgresql-client-9.1: ...


2

There is no definite answer to whether "it is worth the trouble". That's really a matter of taste and style and the total picture we don't have. If in doubt, just don't bother, that's typically the best course of action. For small, simple tables where you rarely delete rows, it might be convenient for human users to remove gaps from primary keys. However, ...


2

well, you cannot switch back easily. what you can do is to use the old backup method: SELECT pg_start_backup('whatever'); rsync the data dir SELECT pg_stop_backup(); this is the best way as not all the data has to be transported over the network. i definitely recommend this replacement for pg_basebackup because it is the only RELIABLE way to get things ...


2

Take a look here. It's a tool which genuinely tries to compare like with like in terms of database benchmarking!


2

They are the same thing in a default installation. pg_catalog.text is the schema-qualified version of the type name text. The search_path always contains pg_catalog automatically. Per documentation: In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data ...


2

This would be more efficient: With json and json_array_elements() in pg 9.3 SELECT p.id AS p_id, p.data AS p_data , c.id AS c_id, c.data AS c_data FROM test p LEFT JOIN LATERAL json_array_elements(p.data->'children') pc(child) ON TRUE LEFT JOIN test c ON c.id = pc.child::text::int; Use the -> operator instead of ->> in the reference to ...


2

You can use DISTINCT ON: select distinct on (native_name) 'nil' as problem_type, native_name, event_date, count from point_problem_history where building_fk = 3 and problem_type = 'nil' order by native_name, event_date desc ; which does exactly what you want. Similar to GROUP BY, it will keep one row per distinct value of native_name ...


2

There are 3 possible kinds of duplicates: Duplicates within the rows of the bulk insert. That's your immediate cause for the exception. Duplicates between inserted rows and existing rows. Duplicates between inserted rows and concurrently inserted / updated rows from other transactions. 1. and 2. can be fixed easily. But you really need to define exactly ...



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