Tag Info

Hot answers tagged

6

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the "chicken/egg" problem of inserting a user and his active email, ...


5

If you can add a column to the table, the following scheme would almost1 work: CREATE TABLE emails ( UserID integer NOT NULL, EmailAddress varchar(254) NOT NULL, IsActive boolean NOT NULL, -- New column ActiveAddress varchar(254) NOT NULL, -- Obvious PK CONSTRAINT PK_emails_UserID_EmailAddress PRIMARY KEY (UserID, ...


4

To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown. I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one! A word of advice (and see ...


3

Problem This is a more complex problem than is obvious on a quick glance. You are sorting by two columns, each from a different table, while you join on two other columns. This makes it impossible for Postgres to use the provided indexes and it has to default to (very) expensive sequential scans. Here is a related case on dba.SE: Can spatial index help a ...


3

It looks like you've run into the issue that jsonb columns have a flat 1% statistics rate, as reported here Working around jsonb's lack of stats?. Looking at your query plans, the differences between the estimates and the actual executions are huge. The estimates say there are probably 200 rows, and the actual return 100158 rows, which causes the planner to ...


2

The actual syntax corresponding to the imaginary SELECT columnname FROM %currenttable% would be, in plpgsql: execute format('SELECT columnname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME); The TG_* built-in variables are documented in Trigger Procedures and the execute and format plpgsql constructs in Basic Statements. The query above is ...


2

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want: BEGIN EXECUTE format('SELECT colname FROM %I', TG_RELID) END; or BEGIN EXECUTE format('SELECT colname FROM %I.%I', ...


2

You can use a CTE to supply the values: with data (street, city, user_id) as ( values ('street1','LA', 2) ) insert into addresses(street, city, user_id) select * from data where exists (select * from users where users.id = data.user_id and users.storeaddress = true); Or alternatively a derived table: insert ...


1

I'm going to add my answer, which was correct in the comments, as the answer here. Be specific about your types! In your master table, you have point_date as a timestamp with time zone. In your CHECK constraint, you have used timestamp without time zone. Then, in your query, you just use point_date >= '2015-08-25 00:00:00'::timestamp, thus using a ...


1

You can run that, no problem: VACUUM FULL ANALYZE pg_largeobject; Might even remove some dead rows. Details: VACUUM returning disk space to operating system But it's probably not going to solve your actual problem. When using the large object facility of Postgres, large objects ("blob": binary large object) themselves are broken up in chuncks of ...


1

The best way I think would be to use the LAG() or LEAD() functions: SELECT *, - 100.0 * (1 - LEAD(Price) OVER (ORDER BY t.Id) / Price) AS Grown FROM table_name AS t ORDER BY t.Id ; With LEAD(Price) OVER (ORDER BY t.Id) you have access to the next Price when the rows are ordered by Id. It's not clear what the order should be. Based on the data in ...


1

The first, most important, thing you can do is get rid of that use of json fields. If you know the field you'll be querying in advance, preferably make it a real column. At the cost of inefficiently widening the table you can do that with a generated column using a trigger, then index the generated column. It'll hurt your insert performance a bit, but you ...


1

Depending on how selective the combined predicates are, I would imagine a good index for this particular query would be: CREATE INDEX index_name ON products (above_revenue_average ASC, start_date DESC) WHERE status > 100 AND category_id <> 5; The SELECT * is potentially problematic because the index above does not contain all columns. ...


1

Use pg_restore to list its contents, dump it to SQL, or restore it to a database.


1

Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this: CREATE OR REPLACE FUNCTION trfn_tbl_log_any() RETURNS trigger AS $func$ DECLARE _ct int; BEGIN IF NEW.timetype = 'start' THEN EXECUTE format($$ SELECT floor(t.timeidx) + 1 ...


1

Or you can use TG_RELID, but since its data type is plain oid, not regclass, one must cast it to regclass explicitly to get the auto-conversion to a schema-qualified (only if the current search_path requires it), cleanly escaped table name. The documentation: TG_RELID Data type oid; the object ID of the table that caused the trigger invocation. ...


1

work_mem Obviously, the sort operation spills to disk: Sort Method: external merge Disk: 36224kB More work_mem can help the query, like @Kassandry already suggested. Increase the setting until you see Memory instead of Disk in the EXPLAIN output. But it's probably a bad idea to increase the general setting based on one query. Proper setting depends on ...


1

One of the easiest and most effective ways to increase performance on this sort of query is to execute SET work_mem=40MB (because you have ~32MB of temp file for sorting, and a little extra often helps) then run your query, and see if the EXPLAIN ANALYZE plan changes from disk to an in-memory sort. Afterwards, run RESET work_mem to put the value back to the ...



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