Hot answers tagged

20

On 18th of November, 1883 at 12:00 (new time), standard time was adopted by the American railroads. This means that before that time, Los Angeles used actual local time, based on mean solar time. After that, it was moved to its local time zone, which, being an integral offset of hours from the Greenwich Mean Time, was slightly different from the previous ...


12

The query you have is basically correct. The only mistake is in the second (recursive) part of the CTE where you have: INNER JOIN descendants d ON d.parent_id = o.object_id It should be the other way around: INNER JOIN descendants d ON d.object_id = o.parent_id You want to join the objects with their parents (that have already been found). So the ...


11

Note: This answer addresses a couple of basic problems, but it's not the final solution. The question was still inconsistent after several requests for clarification, so I stopped processing. General difficulty The Problem is: predicates on some columns, ORDER BY on a different column. In your fast query, without ORDER BY, the first (arbitrary) 10 rows ...


10

Here is what I do in such cases, usually some of this helps: Look at the whole query and try to remove unneeded tables from it. Rethink outer JOINs (that is, LEFT/RIGHT JOIN) and if possible, eliminate them from view definition, replacing by inner JOINS. Try to increase planner constants so the server can put more effort into planning phase. You can do ...


10

Just add the constraint as NOT VALID From the manual: If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, [...] and they'll fail unless the new row matches the specified ...


9

If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value: ( (("qb_id" IS NOT NULL) :: INTEGER + ("xero_id" IS NOT NULL) :: INTEGER + ...


9

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave. This allows Pgpool to load ...


8

A variation on @a_horse_with_no_name's answer would be to first create the table with the constraint and then insert into the table, still using a transaction, so everything rolls back in case of constraint violations. This is something you should consider if the rows to be inserted are a lot (i.e. in the hundreds of thousands or millions). If it's ...


7

You can consider a rule as transforming the command being executed, whereas a trigger is altering the data itself. (Note that this is a simplification! Spend some time reading the documentation for CREATE TRIGGER and CREATE RULE rather than trusting some random internet guy.) So you can define a rule that is invoked when PostgreSQL sees a certain command, ...


7

I would suggest two fundamental tables for this purpose, if you're creating this all from scratch. This assumes that this is being created in PostgreSQL. An employee table containing their name and whatever other details are important for your application, with a numeric primary key. The primary key is important because you may have more than one employee ...


7

You put the cast outside the array constructor - use ::varchar[] instead. The current query will return you the array literal (which is of type varchar) instead of an actual array - see the example SQL Fiddle. But overall, if you only need the output as an array downstream, it would make your life easier if you returned proper columns upstream - easier to ...


6

The biggest difference in time in your execution plans is on the top node, the UPDATE itself. This suggests that most of your time is going to IO during the update. You could verify this by turning on track_io_timing and running the queries with EXPLAIN (ANALYZE, BUFFERS) The different plans are presenting rows to be updated in different orders. One is ...


6

I assume: (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) <> (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del) is what puzzles you. It means that if any of the columns differ the predicate is true. It is a shorter form of: NOT ( (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) = ...


5

You can negate your constraint to find out the rows that does not satisfy it: SELECT * FROM integrations.accounts WHERE NOT ((("qb_settings" IS NOT NULL) or ("xero_settings" IS NOT NULL) or ("freshbooks_settings" IS NOT NULL) or ("myob_settings" IS NOT NULL) or ("ppy_settings" IS NOT NULL))) This can be ...


5

I am not exactly sure why the selectivity of an equality predicate is so radically over-estimated by the GiST index on the tstzrange column. While that remains interesting per se, it seems irrelevant to your particular case. Since your UPDATE modifies one third (!) of all existing 3M rows, an index is not going to help at all. On the contrary, incrementally ...


5

The right way to do this will probably be to have your application INSERT via a stored procedure (or in Postgres a function). If stored procedures are not an option, create a view and rewrite inserts against that view so that they affect some other table. CREATE TABLE t ( a integer, b integer ); CREATE VIEW v AS SELECT t.a, t.b FROM t; CREATE ...


5

Your CHECK constraint can be much simpler: ALTER TABLE billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK (qb_id IS NOT NULL OR xero_id IS NOT NULL OR freshbooks_id IS NOT NULL OR unleashed_id IS NOT NULL OR csv_data IS NOT NULL OR myob_id IS NOT NULL) NOT VALID; Or even ...


5

The index is a sorted structure - if you need only a sufficiently small portion of the data in the table, it could be fetched from the index more efficiently. This needs a few prerequisites, though: PostgreSQL version 9.2 or newer, as index-only scans appeared in this version the index supports the query (the order of the columns of the index decides ...


5

You need to do that with two statements: create table new_table as select ....; alter table new_table add constraint ...; Make sure you run this in a single transaction (e.g. by turning OFF auto commit in your SQL client, or wrapping that with a begin transaction ... If the alter table fails, you can rollback everything and the table is gone as well.


5

Bitmaps can either store a bitmap of rows, or if that becomes too large to fit in work_mem it can "go lossy" by storing a bitmap of blocks. It can do this selectively, so some blocks can be converted lossy while others not. If it goes lossy, then the Heap Scan must recheck every row in every lossy block which it visits, because it no longer has information ...


4

If the version was 9.3 or newer, I would try this rewrite of the query: SELECT w.stamp, w.column1, w.column2, w.column3 FROM "table_name" AS w JOIN LATERAL ( SELECT p.column1, p.column2, p.column3 FROM "table_name" AS p WHERE p.stamp < w.stamp AND p.stamp >= '2015-12-01'::timestamp ORDER BY p.stamp ...


4

psql delegates the handling of input to a shared library, which is either libreadline or libedit. But libedit used to not work reliably with multibyte character sets, as has been reported on multiple occasions, here for OSX for example. From your comment: /Library/PostgreSQL/9.4/bin/psql -> libedit@1.47.0 does not work, ...


4

You have NULLs that violate the CHECK CONSTRAINT. To verify that this is indeed the problem, run this SQL. SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL OR xero_settings IS NOT NULL OR... (fill in the fields that correspond to those in the CONSTRAINT). This will give you all the records that have fields which have a NULL ...


4

There are some row(s) that violate the constraint. But the 59 rows are not very relevant (although it includes the offending rows) because it's a different, bigger result set. It's the rows that have at least one of the 5 values as NULL. You want the ones that only have all 5 values as NULL. To find them, you can run the "opposite" of your constraint: ...


4

The new 9.5 version has added CUBE, GROUPING SETS and ROLLUP extensions to GROUP BY which can be used for such queries. If you have to do this in previous versions, one way is using a CTE, with something like (note that it's not the same exact output as your query, since I didn't know the type of created_at. I guess it's a timestamp and didn't want to mix it ...


4

CHAR and VARCHAR are implemented exactly the same in Postgres (and Oracle). There is no difference in speed when using those data types. However, there is one difference that can make a difference in performance: a char column is always padded to the defined length. So if you define a column as char(100) and one as varchar(100) but only store 10 characters ...


4

Up until 9.4 You can go and ask the system catalogs (pg_type, to be precise): SELECT rolname FROM pg_type t JOIN pg_authid r ON typowner = r.oid WHERE typname = 'bla'; usename ───────── dezso From PostgreSQL 9.5 on From this version on, there is a new object identifier type called regrole, and it makes the query a bit simpler: SELECT ...


4

The Linux postgres user is entirely different from the postgres database user. It's created via the rpm package. When you're logged in as the system postgres user, it assumes the database user and database name that you're wanting to connect to is the same as the operating system user that you're attempting to connect from. That's why a simple psql works, ...


4

An index on (practice_id, client_data_provider_id, date_scheduled) would be better than the one you have (practice_id, date_scheduled, client_data_provider_id), for this particular query. Notice the difference in order. When there are multiple equality (=) conditions and one range condition (>=, >, between, etc) in the where clause, it's better to ...


4

As documented in the manual, string constants (or in general: anything that is not a number) need to be enclosed in single quotes: ALTER TABLE newarts ALTER COLUMN jurisdiction_id SET DEFAULT 'a82857b6-e336-4c6c-8499-4242';



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