Tag Info

Hot answers tagged

8

Basically, NULL is causing this, because NULL<>NULL. One of the columns in your self-joined table will be all NULLs. Here's a little test case that shows why this can happen. Naughty NULL equality and the way NATURAL JOIN works, picking column names to join on for you. Setup: create table one ( a integer, b integer ); CREATE TABLE two ( A INTEGER , ...


6

You could use CASE WHEN operator for solving this: ORDER BY status, CASE status WHEN 'active' THEN planned_date - now() WHEN 'inactive' THEN now() - planned_date END


5

Another way is to use ROW_NUMBER(): select * from t order by status, case status when 'active' then 1 when 'inactive' then -1 end * row_number() over (partition by status order by planned_date) ; Test at SQLfiddle


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 can use the CPU monitor of your operating system (e.g. top in Linux or ProcessExplorer in Windows). Each connection to the database will create a new process on the server. The process id related to the connection is reported in the column pid of the system view pg_stat_activity. With that process ID you can monitor the CPU usage of that process ...


4

In 9.4 version, we'll be able to use the FILTER clause: select t.industry_id, sum(t.clicks) filter (where t.date = current_date) as today, sum(t.clicks) filter (where t.date = current_date - interval '1 day') as yesterday, sum(t.clicks) filter (where t.date >= current_date - interval '2 days' ...


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


3

This can only be if ... you did not actually commit your transaction (yet) and running the second query in a different transaction. or you have other transactions writing to the table in the meantime or something is seriously broken or you are dealing with two different tables: It's worth mentioning that your ALTER TABLE commands are on ...


3

The manual in Rules on INSERT, UPDATE, and DELETE) describes the INSTEAD mechanism for the context of your rule as: Qualification given and INSTEAD the query tree from the rule action with the rule qualification an the original query tree's qualification; and the original query tree with the negated rule qualification added Overlooking the ...


3

Not sure why you are not updating directly with additional predicates in the where clause, but if that is not possible you can select for update as in: SELECT * FROM tbl WHERE id = 1 FOR UPDATE This will lock the selected row and prevent updates of it while you do your validation.


3

\c prints something like You are now connected to database "foobar" as user "squanderer". Use this if you don't mind creating a new connection, because this is what happens. The \connect (shortened as \c) without all parameters will create a new connection identical to your current one. The current connection is closed. See the \connect command spec on ...


3

Generally, refreshing the MV immediately only seems reasonable if write access to underlying tables is a rare event. A statement-level trigger is better than a row-level trigger, but may still prove too much for big tables. I would consider a solution that polls the database every n minutes checking for updates. You could have a trigger write to a table with ...


3

select industry_id , sum(case when current_date <= date then clicks end) as today , sum(case when current_date-1 <= date and date < current_date then clicks end) as yesterday , sum(case when current_date-4 <= date and date < current_date-1 then clicks end) as last3days from ...


2

I assumed you were using a pl/pgsql block (probably a function). For the generic case, you can use record, it can basically take a row of any structure, often used like DECLARE i record; BEGIN FOR i IN SELECT this, that, something, else FROM some_table LOOP ... END LOOP; There is also a possibility of defining a view: CREATE VIEW ...


2

Well, your query here: return NOT Exists ( Select 1 FROM example_table AS a, example_table AS b WHERE a.id != b.id AND a.value = b.value); is not going to see the not-yet-inserted row, which is why it returns true on this second INSERT in your example: insert into example_table (id, value) values (1,0); Now, you could almost fix this by ...


2

Try an extra layer of quotes around your variables: psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string="'Me'" -v version="'1.7.3'"


2

As suggested by @Josh Kupershmidt and @JoeNahmias the solution is to use UNIQUE on md5 hash of the long value. However PostgreSQL 9.3 doesn't support expressions in UNIQUE constraints so an index, that supports expressions, has to be used: create unique index unique_data_url_index on mytable (md5(data_url));


2

Got to psql, issue \d your_table and look up the referenced table's name. If you set \set ECHO_HIDDEN on beforehand, you'll get a bunch of queries that produce the output, those you can reuse in your own discovery script. For example, on my test database these look like test=# \d tfk Table "test.tfk" Column │ Type │ Modifiers ...


2

Simplified query to make it readable: SELECT * FROM "Follows" f JOIN "Users" u ON f."followeeId" = u."id" WHERE f."followerId" = 169368 ORDER BY f."createdAt" DESC LIMIT 1000; Your index follows_followinglist_followerid_createdat_idx looks good for the job. In Postgres 9.2+ it might get a bit faster if you append "followeeId" to the index - if ...


2

You can simplify further: SELECT date_trunc('month', c.created_at) AS year_month , p.reference, , count (distinct c.id) as nb_crea, , (sum(o.amount_ati_cents) / 100) as ca_euros from creations c join products p on p.id = c.product_id join order_items i on i.creation_id = c.id join bundles b on b.id = i.bundle_id join ...


2

I would use a CTE for the first query and reuse it for the second query: WITH cte AS ( SELECT co.name, sum(c.total) AS total, c.created_at FROM c_report c JOIN contact co ON co.id = c.contact_id GROUP BY co.name, c.created_at ) SELECT * FROM cte UNION ALL SELECT po.name, sum(p.total), p.created_at FROM p_report p JOIN person po ON ...


2

The first one is a unique constraint. It can be added to an existing table with: ALTER TABLE ADD CONSTRAINT ... Details in the manual here. It is implemented using a unique index. Per documentation: Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. A uniqueness ...


2

Upgrading a major version of PostgreSQL (i.e. 8.3 to 8.4) requires a database dump and restore - simply copying in data from an older version will most likely not work. There are still some copies of 8.3 that you can download from EnterpriseDB to get you going. However it would still be best to upgrade to the later version afterwards if possible.


2

You first need a list of all possible dates in that range. This can be done using the generate_series() function in Postgres: select dt::date dt from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt Using this you can create a list of all possible attendances: with all_dates as ( select dt::date dt from generate_series(date ...


2

There are several things wrong with this trigger. First: your delete statement. You can't compare NULL using =. You need to use IS NULL: DELETE FROM test2 WHERE email IS NULL; Second: a trigger function (quote from the manual) "must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for." So return ...


2

This can be much simpler, yet, with DISTINCT ON: SELECT DISTINCT ON (product_id) product_id , CASE WHEN stock = 0 THEN NULL ELSE warehouse_id END AS warehouse_id , stock , CASE WHEN stock = 0 THEN NULL ELSE price END AS price FROM product_stock ORDER BY product_id, (stock = 0), price; Assuming stock to be NOT NULL. SQL Fiddle. ...


2

Accidentally read this as a SQL Server question, for Postgres it would have to be a materialized view to do the equivalent. I'll re-use Simo's logic for the sake of consistency. CREATE MATERIALIZED VIEW view AS SELECT id, status, CASE status_date WHEN 'active' THEN planned_date - now() WHEN 'inactive' THEN now() - ...


2

select id from the_table where location in ('a', 'd') group by id having count(distinct location) = 2;


2

It's a gradual process and the effect is comparatively small. But of course, looking up entries in system tables gets slower with lots of rows. Those are just regular tables. Highly optimized, but regular tables. There are indexes to keep the effect small. I have never actually noticed an effect in my biggest database with a couple of hundred tables and ...


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



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