Tag Info

Hot answers tagged

5

You need to use a second CTE for the INSERT: WITH upsert AS ( UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.* ), inserted AS ( INSERT INTO tbl (a) SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert ) RETURNING * ) select * from upsert union all select * from inserted


4

You need one partition for that many records. Not 1000. Certainly not 1000/year. This is not a problem that requires partitioning. It looks to me like you've decided on the solution before fully stating and analysing the problem. Reading between the lines, it sounds like you're implementing a mulit-tenant system and have already decided that partitioning is ...


2

A general goal in designing data storage and retrieval systems is that query time should scale with the amount of data being retrieved, not the amount of data that exists in total. Partitioning is a powerful tool for achieving that goal. Consider a table of session data that looks something like: create table clickstream ( clickstream_id bigserial ...


2

PostgreSQL won't run off NTFS on Linux. Format the drive as ext4. Frankly, putting a database on a USB key on a tablespace isn't usually a great idea. The tablespace is unreadable without the rest of the database, and the rest of the database is useless without the tablespace. So you're creating a fragile system with two points of failure. It's a much ...


2

To count how many contributors have contributed 5 images or more: SELECT COUNT(*) AS number_of_contributors FROM ( SELECT 1 FROM images GROUP BY contributor_id HAVING COUNT(*) >= 5 ) AS t ; It could be written without the derived table but it's obfuscated: SELECT COUNT(*) OVER () AS number_of_contributors FROM images GROUP BY ...


2

Check out Drupal and RDF. Check out what StackExhange (etc) uses. And study how GIT works. I recommend keeping the current value in the primary table, and put the history in other table(s). That is, plan for efficient access to the current value, at the expense of history. If you are talking about "articles" of, say, 1K characters, then compress them. ...


1

There is at least one case where bin_number <> '' is handled differently in MySQL and PostgreSQL. It's when the string only contains spaces. Check out this earlier answer that sheds a light to the MySQL side of things. PostgreSQL: SELECT '' <> ' ' true MySQL: SELECT '' <> ' ' false Usually when the statement contains LIKE the ...


1

Since Postgres is always evaluating CTEs, you can use a CTE for table a and then something like this: WITH a AS ( SELECT <a column list> FROM table_a WHERE <conditions> ) TABLE a UNION ALL SELECT <b column list> FROM table_b WHERE NOT EXISTS (TABLE a) AND <maybe other conditions> ; Note: (TABLE a) is just a ...


1

This currently isn't practical. Writes may not be done on a downstream replica, whether it replays WAL via streaming or archives. However, the logical decoding feature in PostgreSQL 9.4 sets the foundations for making this sort of thing possible. You could have a logical decoding plugin that reads the change-stream from the master server, then writes audit ...


1

Sounds like the data you're exporting isn't utf-8 encoded, or is being chopped up / mangled in transit. This: Ã9ÃýÃ0Ã looks like what happens when you decode utf-8 as iso-8859-15, cp1252, or related 1-byte encodings. But it's not valid utf-8 when demangled. Perhaps you've cut it part way through a string, rather than copying from the beginning of the value? ...


1

I found a solution involving a few extra steps. The "tenant_admin" role is still created the same way, but it is now used as follow: postgres=> SET ROLE tenant_admin; SET postgres=> CREATE ROLE "owner3"; CREATE ROLE postgres=> GRANT ...


1

No, a basic btree index on a small column is typically cheap to maintain. Of course it gets slightly more expensive when the table has accumulated some bloat from dead rows, but the difference should be small. And you have to consider additional storage on disk for the index. One thing seems worth mentioning: Updates on columns involved in an index in any ...


1

No, I am afraid that's not possible. I have been wishing this was possible myself on several occasions. Either you have a registered row (composite) type that matches the return type or you have to list columns individually. CREATE or REPLACE FUNCTION select_join() RETURNS TABLE (col1 int, col2 date, ...) AS ... Or you create a composite type: CREATE ...


1

1st case You seem to forget the valid_during range. As your third case suggests, there can be multiple entries per (rec_id, val), so you must select the right one: UPDATE master m SET valid_on = f_array_sort(m.valid_on || u.valid_on) -- sorted array, see below FROM updates u WHERE m.rec_id = u.rec_id AND m.valid_during @> u.valid_on -- ...



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