Tag Info

Hot answers tagged

9

TL;DR: New users can create tables in the public schema because people complained that it was too hard when they couldn't. If you dislike the defaults, you should probably create a new template database with the initial configuration that you want. For example, you might: DROP SCHEMA public; or REVOKE ALL ON SCHEMA public FROM public; GRANT USAGE ON ...


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


5

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


4

READ COMMITTED imposes fewer overheads as PostgreSQL does not have to do dependency tracking. In practice the difference is likely to be minimal. Isolation level should be selected based on correctness, not performance. It's not possible to determine whether READ COMMITTED is sufficient from the queries provided, but I don't see a strong reason to suspect ...


4

Assuming this sanitized table definition CREATE TABLE events ( event_id serial PRIMARY KEY , user_id int , event_type int , ts timestamp -- don't use reserved word as identifier ); Your comparison seems unfair, the first query has ORDER BY event_id, but the second hasn't. The EXPLAIN output does not fit the first query (no sort step). Be ...


4

serial is purely a shorthand way to create an integer column with an associated sequence for its default values. The documentation for serial linked from the question even says as much: The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the ...


3

My educated guess (while proper information is missing): You declared bin_number as character(n), char(n) or just char data type. character(n) is an outdated, inefficient, weird data type, but defined in the SQL standard. char (without double quotes!) or char(1) are aliases for character(1). Typically you do not want to use it. It's a blank-padded data ...


3

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


3

This behaviour appears to be by design. In src/pl/plpgsql/src/pl_exec.c the error context callback explicitly checks to see if it's being called in the context of a PL/PgSQL RAISE statement and, if so, skips emitting the error context: /* * error context callback to let us supply a call-stack traceback */ static void plpgsql_exec_error_callback(void ...


3

I accepted @Erwin's answer but here are the benchmarks on generated data (10000 rows, best of 5 executions) using the corrected queries. I run it with the multi-colmun index. As expected, queries 1 (26.324 ms) and 2 (23.264 ms) are rather similar in terms of performance while query 3 is the slowest (32.775 ms). CREATE INDEX events_fast_idx ON events ...


3

You explain: I get result 1453 only when all items in column attributeY are empty for 1453. But that's incorrect. Bold emphasis mine. The aggregate function count returns (per documentation): number of input rows for which the value of expression is not null The same is true for SQLite (per documentation): The count(X) function returns a ...


3

It will be available in 9.5. Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947 Discussion on pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html


3

The logic is similar to what @Daniel already posted, but this is a bit faster (twice as fast in my tests on pg 9.3): SELECT CASE WHEN now()::time > time '16:05' THEN now()::date + time '16:05' ELSE (now()::date - 1) + time '16:05' END AS ts; now() is the Postgres implementation of the SQL standard CURRENT_TIMESTAMP (which you ...


3

As for the difference between INNER JOIN ON vs WHERE clause there is a good answer here. There are several answers there and the accepted answer pretty much summarises it all. However, I cannot but comment that you query can be rewritten to significantly improve the performance, like this: select wifi_name ,sum(case when success = 1 then 1 else 0 ...


3

1. Subquery expression You can fix it with parentheses like @a_horse commented: SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1)); But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system ...


3

Use an UPDATE statement. update student set total = social + math + science; But that column is totally useless. In general you should not store data in a database that can easily be derived from existing values. In your, adding the values of the three columns adds no (noticeable) overhead to a select statement, so there is no point in storing the ...


3

When you create a foreign key in postgres, set ON DELETE SET NULL. ALTER TABLE public.tree_data ADD CONSTRAINT tree_data_id_fkey FOREIGN KEY (parent_id) REFERENCES tree_data(id) ON DELETE SET NULL;


3

create table tree_data ( id integer primary key, code text, name text, parent_id integer, constraint fk_parent foreign key (parent_id) references tree_data(id) on delete set null ); SQLFiddle example: http://sqlfiddle.com/#!15/b9a62/1


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

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

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

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

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

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

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


2

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


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


2

Here's a workaround that works for me: Create the schema then dump the tables only, in order such that no FK constraint checks fail: pg_dump --username=postgres --data-only --table=table_1 --table=table_2 --table=table_3 --table=... --format=custom foo > foo.dump pg_restore will now work.


2

@dezso had the completely right idea: All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data The only thing left now was to make it happen. So here's what I did. I took a leaf out of his book and manually edited the dump-file to use a table named table_backup. Then I created ...



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