Tag Info

New answers tagged

0

Your combinations in order of typical performance: 1. > 2. > 4. ( > 3.) 3. is invalid. If rows are only unique per (code, class_id), the lookup by code alone can return multiple rows and is different from the rest. 2. is pointless. If code is unique, there is no point in adding another predicate on class_id - except to verify that a given code ...


0

A record will only be null if all of its fields are null or if the record itself is null. If you want to check if any of the fields is null then check one by one: create table final ( name character varying(20) not null, data source check ( (data).id is not null and (data).weight is not null ), constraint final_pkey primary key ...


2

It's not possible to save query plans "for all future sessions". Query plans are only ever saved for the current session. And only reused under a number of favorable conditions. Plans for ad-hoc SQL queries are not saved at all. All queries inside PL/pgSQL functions are treated like prepared statements. And there are more steps than just the query planning. ...


2

If your data structure is fixed and out of your control, then the best way to do this is to use a recursive common table expression (CTE) like in this question. If you can change the structure then there are ways to make such queries considerably more efficient. What you have there is often called a "naive tree" - while it allows easy construction and easy ...


0

"a boolean column stating whether or not a group has any users" Use EXISTS: CREATE VIEW group_info AS SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty FROM groups g; This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need ...


3

First off, you do not want to use char(50). Use varchar(50) or just text. Read more: Any downsides of using data type “text” for storing strings? Assuming the following rules: Basic slugs never end with a dash. Duplicate slugs are suffixed with a dash and a sequential number (-123). Note that all of the following methods are subject to a race ...


0

When you do pg_restore --dbname=mydb --no-owner mydb.dump You are trying to connect to the mydb database which still does not exist. Connect to an existent one and use the --create parameter: pg_restore --dbname=existentdb --no-owner --create mydb.dump http://www.postgresql.org/docs/current/static/app-pgrestore.html


2

If you want the result as rows in instead of as columns as in @a_horse's answer then create the year ranges in a CTE and join the table to it with years(year_range) as ( values (int4range(1900, 1945, '[]')), (int4range(1946, 1964, '[]')), (int4range(1965, 1974, '[]')), (int4range(1975, 1991, '[]')), (int4range(1992, 2005, '[]')), ...


2

That spike is probably caused by dirty data pages being flushed to disk. Raise the checkpoint_segments parameter in the postgresql.conf file. http://www.postgresql.org/docs/current/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS http://www.postgresql.org/docs/current/static/wal-configuration.html


0

I don't see where you are referencing the content of your CSV anywhere in what you posted.. (at least before any future edits). You should be using the COPY command. http://www.postgresql.org/docs/current/static/sql-copy.html EDIT Since Topic and Format tables appear to be lookup tables for referential integrity, the correct course of action would be to ...


6

This appears to be the cause of the problems, from the same page quoted by ypercube: Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same ...


3

Use conditional counting: select count(case when year <= 1945 then 1 end) as pre1945, count(case when year between 1946 and 1964 then 1 end) as period2, count(case when year between 1965 and 1974 then 1 end) as period3, ... from ... where ...; This works because count() ignores null values and the case statement returns a null for ...


2

2nd query: Of course you get duplicate rows. 1 row per producto each fabricante is connected to - multiplied with the number of rows in pais each combination is connected to. 1st query: An explicit JOIN binds before (groups of) comma-separated items in the FROM list. This is why you cannot reference fabricante in the JOIN condition between pais and ...


0

When I faced this message, I checked the postgresstartup.log, it showed that the data directory was having permissions as 777,After I changed to 700 , the postgresql started working sudo su - postgres chmod 700 -R <postgresql_Data_Directory> exit /etc/init.d/postgresql start /etc/init.d/postgresql status


6

This should work but I'm not really sure if it's the best regarding efficiency: WITH copy_to_other_table AS ( INSERT INTO other_table (column_a, column_b) SELECT column_a, column_b FROM main_table WHERE column_a = 1 ), main_table_deleted AS ( DELETE FROM main_table WHERE column_a = 1 AND NOT EXISTS ...


0

In the second query you ommited nomencladores.pais.* in the select and as there can be several pais_id for each distribuidor_producto_solicitud.producto_solicitud_id it is very likely that you are getting repeated results for this reason. However, if the only problem is that you are getting repeated results the only thing you need is to add the keyword ...


0

Try adding the "-w" flag to your pg_ctl -D . -l run.log start command, so that pg_ctl should only return once the server is up. If that worked, the problem was that your createdb command was running too quickly after pg_ctl, before Postgres was actually up and accepting connections, when those commands were executed in a script as opposed to manually. ...


3

Using debugging parameters like enable_nestloop should not change the results of a query. If it does that'd generally be a bug. However, you should not use enable_nestloop = off or similar in production. It's a very big hammer, and while it might appear to fix your immediate problem, it'll create performance problems elsewhere. A nested loop is often the ...


2

Quick and dirty If it's ok to raise an exception, simply cast to regclass: SELECT 'foo'::regclass If 'foo' is found, you get back the oid, output as text representation, so that's just the table name, escaped if necessary. Else you get an error message complaining about the invalid cast. This has two major shortcomings: The search includes implicit ...


1

Exclude users without emails Assuming we only want users that actually have emails. Users without emails are ignored. The reason I went with this assumption at first is that all your queries do that already: LEFT JOIN emails on users.id = emails.user_id WHERE emails.email LIKE 'a' || '%%' By adding a WHERE condition on emails.email you effectively ...


1

You can convert the config value to an array and replace the $user with the current user name. The array can then be used in the where condition: where n.nspname = any(string_to_array(replace(current_setting('search_path'), '$user', current_user), ','))


0

Without knowing your table and view structure, I created a test case for myself: CREATE TABLE private_table ( id serial, col1 text ); CREATE OR REPLACE VIEW public_view AS SELECT id, col1 FROM private; CREATE OR REPLACE RULE insert_private AS ON INSERT TO public_view DO INSTEAD INSERT INTO private_table (col1) ...


6

The name of the additional module hstore is derived from "hash store", because it was inspired by perl hash. Oleg Bartunov and Teodor Sigaev - the authors of the module - say so in their presentation here: Hstore — key/value storage (inspired by perl hash)


1

What you are seeing is SQL Server data pages in a backup file. Or a SQL Server data file (.MDF). Either way, is is a collection of 8k data pages. These would be restored or attached to a SQL Server. They are not conveniently readable without a SQL Server install. No SQL Server = no access to the data. Quite simple. If you don't have access to a SQL ...


1

If you don't always want to pass all parameters, create a function with parameter defaults. The basic, simple form would be an SQL function without dynamic SQL: CREATE OR REPLACE FUNCTION func( _a int = NULL , _b text = NULL , _c text = NULL) RETURNS TABLE(...) AS $func$ SELECT ... FROM ... WHERE (speed = $1 OR $1 IS NULL) AND ...


1

You can use Dynamic queries. Here is a page about it. Check out about 2/3rds of the way down after "Example 40-1. Quoting Values In Dynamic Queries". But like it states, you need to be VERY careful. Doing this type of SQL creation on the fly can be a huge hole for SQL injection. http://www.postgresql.org/docs/current/static/plpgsql-statements.html


1

You can't use on delete set null if any of the foreign key columns don't accept null: create schema stack; set search_path=stack; -- create table t1( foo integer , bar integer , primary key(foo,bar) ); -- create table t2( foo integer , baz integer , bar integer , primary key ...


2

To convert an hexadecimal string to characters, the encoding must be specified. This is necessary because the same series of bytes may produce different characters depending on their encoding. For instance, if the hex codes represent an utf-8 string, use: select convert_from(decode(your_hex_string, 'hex'), 'utf-8'); If the encoding is not supported by ...


4

We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten That's not the case. In order to support rollback and crash-safety, PostgreSQL must write a new copy of every modified row, rather than modifying the row in-place. Twice, actually, because it must be written to WAL (a sequential ...


0

In SQL, NULL (operator) (value) is generally NULL. This is not unique to hstore, and is the norm for everything. The empty string '' is different to NULL. '' || 'somestring' is 'somestring', wheras NULL || 'somestring' is NULL. The same is true for hstore. Just like NULL + 1 is NULL. If this is a problem for you, you should probably store empty hstore ...


3

I solved the mystery on pg irc channel - I had a long running query (idle in transaction) and they told me that a new index doesn't get used until all transactions that started before its creation don't finish. Killed the transaction, and now the indexes are back to normal.


0

In Postgres, you can use an index on the expression: CREATE INDEX foo_idx ON events (mod(offset, 5)); Only works for that number, of course. And 5 would be a bad example, because ~ 20 % of the rows would qualify for each possible value, and an index starts paying off for ~ 5 % selected rows or fewer. Otherwise, a sequential scan will be faster. Aside: ...


2

SELECT person, max(ct) AS max_ct FROM ( SELECT person, count(*) AS ct FROM ( SELECT person, result , row_number() OVER (PARTITION BY person ORDER BY time) - row_number() OVER (PARTITION BY person, result ORDER BY time) AS grp FROM tablex ) sub1 GROUP BY person, result, grp ) sub2 GROUP BY person ORDER ...


0

There is no more space on the device: could not write to log file: No space left on device Check it with df and make some space


0

The problem turned out to be a shp2pgsql process that was run in a loop in a shell script in screen that finished successfully several hours before I posted this question. It did not show up in pg_locks, nor pg_stat_activity, as it is a separate process, which was then being piped into psql. Killing this window inside screen, allowed the ALTER TABLE ...


1

Per the notes given on the wiki for that query, it only shows row level locks. ALTER TABLE takes a table level ACCESS EXCLUSIVE lock. One or more transactions will be holding weaker locks on the table that prevent ALTER TABLE from acquiring its lock. You can identify the lock being waited on with something like: select * from pg_stat_activity sa inner ...


1

Try this : CREATE TABLE my_table_bk AS SELECT * FROM my_table WHERE true = false ; this will create a table "my_table_bk" with a same structure as ""my_table without constraints and without data


0

Even if extracting fields from a date would always produce results that could fit in an integer, according to the doc, extract doesn't directly work on a date type: The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date are ...


1

Yes - if you use functions in a language other than SQL, or if you define them as STRICT. Essentially, you must prevent inlining of the function. If the function isn't inlined, then predicates can't be pushed down through it and it can't be flattened. Only SQL functions are eligible for inlining, and only if they are not defined as STRICT.


0

Bound to query If you can only change the view, not the query: this is 100 % equivalent using a correlated subquery instead of the LEFT JOIN: CREATE VIEW the_view_new AS SELECT a.id, a.name , (SELECT age_group FROM table_b WHERE id = a.id) AS age_group FROM table_a a; Your query as is just reads top and bottom row from the index now, IOW blazingly ...


2

I had something similar happen recently with a table of 3.5 million rows. My update would never finish. After a lot of experimenting and frustration, I finally found the culprit. It turned out to be the indexes on the table being updated. The solution was to drop all indexes on the table being updated before running the update statement. Once I did that, ...


1

I had something similar happen recently with a query very much like yours. My update of 3.5 million rows would never finish. The culprit turned out to be the indexes on the table being updated. The solution I finally came upon what to drop all indexes on the table being updated before running the update statement. Once I did that, the update finished in a ...


1

Amazon's RDS only offers PostgreSQL versions 9.3.x, and it seems unlikely that they'll ever offer to host older versions of Postgres. So by jumping from a local 8.4 install directly to RDS, you would in effect be making two significant changes at once (jumping up several Postgres versions, as well as switching to managed hosting). That may be alright or not ...


0

I was finally able to make it work by removing the -f option when creating the SSH tunnel. For an unknown reason, the error appears when using the -f option but it works fine otherwise and I can connect via pgAdmin using the SSH tunnel.


0

you get an impression of the performance looking at the comparison with a real column store: https://www.monetdb.org/content/citusdb-postgresql-column-store-vs-monetdb-tpc-h-shootout


2

Yes it's safe. It's not exactly clear in the documentation but date_trunc can return either timestamp or timestamptz depending on what you pass it. If you pass a date, it will cast the input to timestamp and returns timestamp so there can be no tz issue when casting back to date. If you pass a timestamptz it does the truncation the way you'd expect and ...


2

You can use LAG() function in a query with GROUP BY just like any other query. The only difference is that the columns allowed in the window (OVER) and in the LAG are the ones allowed in SELECT after a GROUP BY: select date, session_id, sum(upload_usage) as upload, sum(download_usage) as download, sum(total_usage_on_a_day) as ...


0

Here, a solution using subqueries http://sqlfiddle.com/#!10/52460/13 SELECT *, total_usage_on_a_day - COALESCE(( SELECT total_usage_on_a_day FROM foo WHERE session_id = T.session_id and Date < T.Date ORDER BY Date DESC limit 1 ),0) AS Extected_difference FROM foo T


0

PL/pgSQL functions are black boxes to the query planner. Queries inside are optimized just like other queries, but separately and one by one like prepared statements, and the execution plan may be cached for the duration of the session. Details: PostgreSQL Stored Procedure Performance Like @Daniel already commented, you can use the additional module ...


0

To remove all elements from the column images (holding a json array) where 'id' is 'note_1': pg 9.3 UPDATE items i SET images = i2.images FROM ( SELECT id, array_to_json(array_agg(elem)) AS images FROM items i2 , json_array_elements(i2.images) elem WHERE elem->>'id' <> 'note_1' GROUP BY 1 ) i2 WHERE i2.id = i.id AND ...



Top 50 recent answers are included