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

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

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


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

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

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


2

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


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.


1

It looks like what you want is row-level security. PostgreSQL will support this in version 9.5, but that's due to come out in over a year. For now, you'll need to use views. If you don't mind the fact that a clever user can trick the system into revealing information they're not meant to be able to see using malicious functions in the WHERE clause, you can ...


1

I believe the answer is in this header comment of heaptuple.c: * Before Postgres 8.3 varlenas always had a 4-byte length header, and * therefore always needed 4-byte alignment (at least). This wasted space * for short varlenas, for example CHAR(1) took 5 bytes and could need up to * 3 additional padding bytes for alignment. * * Now, a short varlena (up to ...


1

If you use EXPLAIN you'll see the ordering. For a random plan I concocted with some throwaway data and similar query (ORDER BY ... LIMIT 1 FOR UPDATE) I got the plan: Limit (cost=33.09..33.10 rows=1 width=26) -> LockRows (cost=33.09..39.88 rows=543 width=26) -> Sort (cost=33.09..34.45 rows=543 width=26) Sort Key: t_id ...


1

For Question 1: using md5 auth for local connections is particularly helpful when you have a multi-user machine, or if you might add additional user accounts for friends/family/coworkers/whoever someday. If you are and will remain the only actual user on your system, there is less benefit, but it could still be useful: suppose some bad guy manages to break ...


1

Whether a password is required or not has nothing to do with pg_shadow and whether a password is actually defined for the user. Yes, I know, that's weird. pg_hba.conf controls the authentication method. If you want to request a password, use md5 authentication. If you want to allow login with no password to anyone, use trust. If you want to require the same ...



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