Tag Info

New answers tagged

0

This rewritten query should be substantially faster: SELECT id || ':' || group_number AS uniq_id -- id::text || ':' || group_number AS uniq_id -- with integer FROM table_one t1 WHERE EXISTS ( SELECT 1 FROM table_two t2 WHERE t2.id = t1.id AND t2.group_number = t1.group_number ) AND NOT EXISTS ( SELECT 1 FROM ...


1

Since #1 requires copying data from the master to the child while it is in an active production environment, I personally went with #2 (creating a new master). This prevents disruptions to the original table while it is actively in use and if there are any issues, I can easily delete the new master without issue and continue using the original table. Here ...


2

Have you tried rewriting your NOT IN predicates as LEFT JOINs? SELECT t1.id || ':' || t1.group_number AS uniq_id FROM table_one t1 LEFT JOIN table_two t2 ON t1.id = t2.id AND t1.group_number = t2.group_number LEFT JOIN table_three t3 ON t1.id = t3.id AND t3.timestamp > NOW() - INTERVAL '30 days' AND t3.client_id > 0 WHERE t2.id IS ...


0

Why not just call the plpgsql function that raises a notice, from inside the SQL function? According to the doc: SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list So a SELECT statement can be inserted at the beginning without changing the result. create function print(text) returns void as ...


2

I have heard of concurrency problems like that in MySQL before. Not so in Postgres. Built-in row-level locks in the default READ COMMITTED transaction isolation level are enough. I suggest a single statement with a data-modifying CTE (something that MySQL also doesn't have) because it's convenient to pass values from one table to the other directly (if you ...


1

I'd say this is a good security practice to restrict OS access to your DB files. First you should definitely restrict write privileges to DB files at the OS level, if not already configured that way. Don't allow anyone other than the OS user that runs the DB to write to the DB files. Practically any DB software I can think of restricts this by default. ...


1

You probably have checkpoint_timeout set very high on the production system, and archive switches are being driven by checkpoint_segments, probably in conjunction with a low setting of archive_timeout. But, checkpoint_segments has no effect during recovery. Restartpoints during recovery are driven exclusively by checkpoint_timeout. Since recovery can ...


0

pg_dump won't emit CREATE EXTENSION and other database options if you specify a specific table or a table pattern using -t.


0

For me @Vérace's answer didn't maintain the column names, but assigned default names (f1, f2, etc.) instead. I am using PostgreSQL 9.1 with the JSON extension. If you want to export the entire table, there is no need for a subquery. In addition, this will maintain the column names. I used the folowing query: COPY (SELECT row_to_json(t) FROM fred as t) to ...


3

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with: revoke usage on schema public from public; This is required when it's not desirable that people peek into other people ...


1

postgres has no password after an automated install, it's expected to authenticate through the peer method. sudo su - postgres is the first step to it. The second step is to connect through the Unix local domain socket, but your command doesn't do that, it connects through TCP, that's why you're stuck with the password problem. The installed pg_hba.conf ...


2

Have a look a tablespaces. You can keep the main and small parts of the database in the SSD drives and migrate the bigger tables to the tablespaces in your other drive. http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html In alternative the whole database can be put into the tablespace by setting the default tablespace parameter.


1

it's better to use partitioning, e.g. one table per month. You can then truncate archive tables which is an instant operation which frees disk space, or move them to a tablespace on a cheaper device and/or replace them with an aggregate row in an aggregates table. The table seen by your app will usually be a view of the union of the monthly tables. You need ...


2

I won't put all of them together. Even if you query them together, it may occur that your application will be able to post multiple images per post or multiple events per post or multiple posts per event. In this case you would be able to save space. The JSON may be a good idea, but it would be better (in view of indices) to use multiple tables. This way ...


1

What you see is obviously a display bug in the current pgAdmin 1.20. I could reproduce it. You must be aware that the code you see is re-engineered from system table entries. If you look up your function in the system catalogs directly, you'll find that your the return type has been registered properly (at least it works for me in pg 9.4): SELECT ...


3

It's unclear whether you want the total count or the count in the past 24 hours. For the latter: SELECT count(*), page FROM page_views GROUP BY 2 WHERE created_at >= now() - interval '24 hours' -- guessing column HAVING count(*) > 20 ORDER BY 1 DESC; The condition count(*) > 20 has to go into the HAVING clause. You did not provide a table ...


1

Actually, this is all you need: NEW := jsonb_populate_record(NEW, NEW.json); Per documentation: jsonb_populate_record(base anyelement, from_json jsonb) Expands the object in from_json to a row whose columns match the record type defined by base (see note below). What's not documented: The row provided as first argument retains all values that ...


0

You already found that you have to use dynamic SQL with EXECUTE. What you are missing: If child tables are not guaranteed to share the same row type , you must add a target column list to your INSERT statement or you are bound to run into errors or worse: it might work in surprising ways. You need to defend against SQL injection. Table names have to be ...


1

Try this form: EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW; It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays. An even more modern and cleaner version (quote the table's name if necessary): EXECUTE format('insert into %I values ($1.*)', child) USING NEW;


4

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or in the start of the function definition, declaring how you want such conflicts to be resolved (the 3 possible values are error (the default), use_variable and use_column): CREATE OR REPLACE FUNCTION ...


1

You have to configure the following two files pg_hba.conf host all all 0.0.0.0/0 md5 postgresql.conf listen_addresses='*' You have to check if the port 5432 is open: http://www.yougetsignal.com/tools/open-ports/ If it's not then add a rule to your iptables: iptables -A INPUT -s 0/0 -p tcp --dport 5432 -j ACCEPT 0/0: If you want anybody to access ...


3

TL;DR: No, except for some basic cases. Some lock-strength reductions for ALTER TABLE have been added to PostgreSQL 9.5. You can't do anything that requires a full table rewrite without an exclusive lock though, in 9.5 or below. Some operations, like ALTER TABLE ... DROP COLUMN or ALTER TABLE ... ADD COLUMN ... without a DEFAULT and NOT NULL can be done ...


2

The LIKE operator can't do this. No built-in function does what you are trying to do. Write a stored function, or make this transformation in the application code.


2

Here's a description of PostgreSQL's date handling I found on the pgsql mailing list from January 2012: As best I can tell, that document is talking about issues that are beyond Postgres' ken. When you tell us a timestamp value is '2012-01-30 21:13:28.097017-05', that's what we store --- whether you meant it to be in TAI, UTC, UT1, or whatever is ...


4

Use a FULL [OUTER] JOIN, combined with two rounds of window functions: SELECT ts , min(foo) OVER (PARTITION BY foo_grp) AS foo , min(bar) OVER (PARTITION BY bar_grp) AS bar FROM ( SELECT ts, f.foo, b.bar , count(f.foo) OVER (ORDER BY ts) AS foo_grp , count(b.bar) OVER (ORDER BY ts) AS bar_grp FROM foo f FULL JOIN bar b ...


4

You could use the function age() to simplify your expression (returns interval). But it's much more efficient to use a sargable expression to begin with. This operates with the exact time difference (current time is relevant): SELECT * FROM accounts WHERE status = 'PENDING_PAYMENT' AND status_updated_at < now() - interval '10 days' To operate ...


5

Use the ONLY key word: TRUNCATE ONLY public.history_uint; Per documentation: If ONLY is specified before the table name, only that table is truncated. If ONLY is not specified, the table and all its descendant tables (if any) are truncated. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are ...


4

Use substring() with a regular expression instead: substring(ls.attribute_actions_text FROM 'name="(.*?)"/>') The dot (.) matches any character, *? is the non-greedy quantifier for a sequence of 0 or more matches and the parentheses (()) mark the substring to be returned. Like your code, this selects the first string matching the pattern and does not ...


-1

DO $$ BEGIN CREATE INDEX "index_name" ON "table" (column); EXCEPTION WHEN DUPLICATE_TABLE THEN -- END $$; No?


2

I put in a little time to try and develop an answer for this question which may fit your needs, but since I don't have detailed criteria, it may not be perfect. Hopefully, though, it is close enough so that you can manipulate to meet your design needs. Initial assumptions To begin, I had to make a few initial assumptions to design the algorithm. 1) When ...


1

I just ran into the same issue on Postgres 8.3.11. Although I could not identify the root cause, the fix was simple enough: REINDEX INDEX tbl_cust_id_idx; This page contains hints as to what may have caused the error, although they are vague: An indicated table index was corrupted (may be a result of recent postgres or system failure, there was a lack ...


0

Try with this query does the trick: select * from pg_stat_activity where datname = 'mydatabasename'; Info: See this closely related answer. pg_stat_activity is a view in the pg_catalog schema. You can query it by SELECTing from it like any other table, eg SELECT * FROM pg_stat_activity. The manual page you linked to explains its columns. You'll ...


3

This means that I have to wait until all of my files are copied back from the new master the old one, which means a lot of data traffic When using pg_basebackup, yes. But, if you're confident that the existing files at the destination are almost identical to the source, rsync may be used. The rsync remote-update protocol is able to identify what parts of ...


3

Dynamic fields are notoriously difficult in plpgsql. In particular there's no way we can write new.variable := something where variable stands for a column name. See How to set value of composite variable field using dynamic SQL for ways that involve querying the catalog at runtime. Personally, I'd suggest a simpler solution with the plv8 language. CREATE ...


3

It sounds like the code might be using SAVEPOINTs to handle errors, and not releasing the savepoints before proceeding. That would explain the large number of virtual xid locks. RELEASE SAVEPOINT after you're done with a step. You might also want to consider batching the work into smaller chunks, as the: SAVEPOINT Try it ROLLBACK TO SAVEPOINT if it ...


1

The best query very much depends on data distribution. You have many rows per date, that's been established. Since your case burns down to only 26 values in the result, all of these solutions will be blazingly fast as soon as the index is used. The partial index below will be a bit faster if you have many NULL values. For more distinct values it would get ...


4

JSONB may be easy to read, but it's complicated and inefficient to write into. See for example this question: PostgreSQL update and delete property from JSONB column, on how it looks like. It's an order of magnitude harder than an update/delete with classic EAV tables. Possibly when you'll have written the parts to append/merge/delete key/value pairs, the ...


1

It is difficult to explain your precise case without explain plans and engine profiling information. You may use pg_stat_statements module or PostgreSQL perf for tracing, but I am convinced your issue is the transaction size. It is known that the pattern of one transaction per row must be avoided for performance reason as commit overhead will slow down the ...


0

Basics: The 1st row of the CSV file has column names of defined format. The PROGRAM clause of COPY and GET DIAGNOSTICS after COPY require Postgres 9.3+. format() requires Postgres 9.1+ This works with pure standard Postgres - except for the head command that the shell is expected to provide. For Windows versions consider: How to do what head, tail, more, ...


3

why didn`t the first query fail? Because this is valid SQL. First of all consider that it is valid to run a SELECT against the table that doesn't reference any columns from that table. SELECT DISTINCT 'foo' FROM product_template WHERE type = 'import'; The above will return a single row result of foo if any rows exist that match the WHERE ...


5

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


0

I've done a lot of experimenting and here are my findings. GIN and sorting GIN index currently (as of version 9.4) can not assist ordering. Of the index types currently supported by PostgreSQL, only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order. work_mem Thanks Chris ...


0

From the postgresql documentation: CLUSTER can re-sort the table using either an index scan on the specified index, or (if the index is a b-tree) a sequential scan followed by sorting. It will attempt to choose the method that will be faster, based on planner cost parameters and available statistical information. Your index on labelDate is a btree.. ...


0

You should try to create an index on time, and see if this improves the execution time of your query: CREATE INDEX time_index ON sensor_readings(time); An alternative is to partition or cluster the table on time, as described in another answer.


0

The main problem is that you have 60 GB of data which you want to access quickly on a single slow HDD (I've never heard of 7400 rpm, is it 5400 or 7200?). You can partition on the time, so the last two weeks of data are grouped together in tight set. Or you could try clustering on the time instead of partitioning on it. You could either build on index on ...


1

The sub-select: SELECT role_id from role WHERE role_name = 'tech' returns exactly one row, so your query can be transformed to: SELECT emp.name, role.role_name, emp.emp_id FROM emp JOIN role ON emp.role_id = 1 This can be transformed to: SELECT emp.name, role.role_name, emp.emp_id FROM emp CROSS JOIN role WHERE emp.role_id = 1 Therefor the ...


4

Your query, given your data, is equivalent to this one: SELECT emp.name, role.role_name, emp.emp_id FROM emp INNER JOIN role ON emp.role_id = 1; -- because this is the role_id for 'tech' This means that you don't define a condition about how to join the two tables. In turn, this results in a Cartesian product of role (without any restriction about ...


0

The logging collector tries to capture stderr and write it to a file. But it looks like systemd wins the redirecting war, and sends the output to the journal. You can check the systemd journal with: journalctl -u postgresql The systemd configuration is in usr/lib/systemd/system/postgresql.service. I've tried various settings for StandardError, but none ...


2

Here are a few quick tips which can help improve your performance. I'll start with the easiest tip, which is almost effortless on your part, and move on to the more difficult tip after the first. 1. work_mem So, I see right off-hand that a sort reported in your explain plan Sort Method: external merge Disk: 5696kB is consuming less that 6 MB, but is ...


6

This unicity constraint can be enforced with this unique index: CREATE UNIQUE INDEX idxname ON mytable(least(col1,col2),greatest(col1,col2)); Demo: test=> insert into mytable (col1,col2) values(1,2); INSERT 0 1 test=> insert into mytable (col1,col2) values(2,1); ERROR: duplicate key value violates unique constraint "idxname" DETAIL: Key ((LEAST(col1, ...



Top 50 recent answers are included