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)


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


3

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


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


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

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

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

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

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!


1

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


1

in PostgreSQL you should use a tool called pgbench. it allows you to run custom scripts with an arbitrary number of concurrent requests and all that. it is also able to create random values for your script and all that - it is really powerful. for the test: make sure you got a proper setting for -j (number of threads used by pgbench) so that pgbench is able ...


1

Issue ALTER ROLE client VALID UNTIL 'infinity' and it will remove the VALID UNTIL clause.


1

On OS X 10.8 and 10.9 (not 10.10) with server app installed, in a terminal window, type sudo serveradmin settings postgres On OS X 10.9 Mavericks, this is the output I get from that, which includes the data directory. postgres:log_connections = "on" postgres:unix_socket_directory = "/private/var/pgsql_socket" postgres:listen_addresses = "127.0.0.1,::1" ...


1

It actually suffixes the password with the username and then MD5 hash is made out of that, and that hash is then prefixed with 'md5'


1

My team once had to build a database for reporting financials with alternative time windows, including fiscal years, fiscal quarters, and fiscal months. The relationship between dates and these units was documented, but really messy. So here is what we did. We created a table, call it Almanac, with one row per date. (In reality, we had one row per work ...


1

No. How would the query processor recognize that it should use the materialized view. The materialized view is another object. See http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view." So materialized ...


1

well, it depends a bit on the use case. reusing item pointers in GIN is definitely a lot harder than it is in a btree because the structure is totally different on the item pointer level. with the new GIN structure life has become even harder to predict so i would keep a close eye on it - depending on your way of using GIN a reindex might be necessary. but ...


1

GIN indexes are built using B-trees, but with a different structure. "The potential for bloat in non-B-tree indexes has not been well researched. It is a good idea to periodically monitor the index's physical size when using any non-B-tree index type." See more at: Routine reindex


1

Answer to own question: I was blind. The statements are not truncated, but they contain newlines. There was some pre preprocessing done before I looked at them. And this preprocessing was done lines based, so they looked like being cut.



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