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

Question 1 From PostgreSQL Documentation Table 8.23 - JSON primitive types and corresponding PostgreSQL types: JSON primitive type | PostgreSQL type | Notes null |(none) | SQL NULL is a different concept So you must not confuse SQL NULL value with with JSON null type. Question 2 In your previous question you wanted the JSON value ...


2

To add to what others have said. Temporary tables can also have primary keys, constraints, indexes, etc. whereas CTEs cannot. On the flip side, you can do some pretty neat tricks with CTEs that would be harder, I think, if done with temporary tables-- such as chaining them to performs deletes, inserts, and selects all in one statement. There are some nice ...


2

To enforce unique email addresses, I would remove all competing email columns and store them in one central email table for all active emails. And another table for deleted emails: CREATE TABLE users ( user_id serial PRIMARY KEY , username text UNIQUE NOT NULL , email text UNIQUE -- FK added below -- can also be NOT NULL ); CREATE TABLE email ( ...


2

The amount of details logged is controlled by log_error_verbosity. Setting it to TERSE will exclude DETAIL, HINT, QUERY, and CONTEXT error information: ALTER DATABASE db_name SET log_error_verbosity to 'TERSE';


2

A check in the HAVING clause would do the trick: SELECT author_id, count(*) AS i_count FROM images GROUP BY author_id HAVING max(date_added) > min(date_added) + interval '30 days' ORDER BY i_count DESC;


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

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

In PostgreSQL (as described in documentation, The Information Schema): SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public'; For MySQL you would need table_schema='dbName' and for MSSQL remove that condition. Notice that "only those tables and views are shown that the current user has access to". Also, ...


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


1

One way to think of this would be that you have two classes of user which have slightly different rules: deleted and not deleted. Deleted users e-mails may clash, not deleted users e-mails must be unique. Because these two classes have different rules (i.e. constraints), instead of using a flag to indicate whether or not a user is deleted, I would duplicate ...


1

A CTE is just that -- Common Table Expression, that is, only a syntax construct. The result set described by a CTE may never be materialized in the specified form. A temporary table, on the other hand, is a real database object that is initialized with the structure described by its DDL statement and possibly populated by actual rows.


1

Well, I spent a while looking at writing a custom postgres C extension, and wound up just writting a Cython database wrapper that maintains a BK-tree structure in memory. Basically, it maintains a in-memory copy of the phash values from the database, and all updates to the database are replayed into the BK-tree. It's all up on github here. It also has a ...



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