Search Results

Let me add to efesar's answer that the documentation states the following: The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating u …

It is up to the developer/DBA to write sensible joins, in this regard PostgreSQL is just a slave that does what its master commands. …

It looks like raising the work_mem by some MBs (say 30) would speed things up significantly. You could also remove the ORDER BY clause from the aggregation CTE. Similarly, the GROUP BY in the subquer …

If you want to happen it automatically (I mean, without the application doing that), you have to create a sequence: CREATE SEQUENCE some_table_pk_seq START 50; Then change the column to use this as …

You can self join the table so that on the left there are the hosts for month 1, on the right for month 9: SELECT host, risk, plugin_id, month, year FROM your_table t1 JOIN your_table t2 ON t1.mo …

The actual solution very much depends on your exact situation. If you can use a pl/pgsql function, you can check first for the existence of the table: IF NOT EXISTS (SELECT 1 FROM pg_class WHERE rel …

Second, you have to keep your table tidy by vacuuming it regularly - if you have a relatively recent PostgreSQL version, there are chances that the database will take care of it with autovacuum. …

If I understand your requirements correctly, you want to join the other table on polyname and then get the max() of poly_version, like SELECT rule_name, rule_version, polyname, state, max(poly_versio …

For the first point, you can try this: COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]) The same can be achieved by a CASE, too: CASE WHEN array_agg(id) IS NULL THEN '{0}'::integer …

Try WITH vals (reportid, amount) AS ( VALUES (10, 40), (15, 29), (40, 4) ) INSERT INTO testschema.requestinfo (requestid, reportid, amount) SELECT currval('testschema.reque …

Using psql variables with quotes is a bit tricky. As you noticed, they don't get substituted in certain positions, among other, between quotes. My only solution to this so far has been including t …

You could try it yourself as well, but no: renaming a column won't break your foreign keys. Try this: CREATE TABLE a (a_id serial PRIMARY KEY); CREATE TABLE b (a_id integer REFERENCES a (a_id)); A …

I usually solve such problems by setting up a table for all the possible data points (here the pests and dates). This is easily achieved by a CROSS JOIN, see the WITH query below. Then, as the finis …

Your understanding is not correct. Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client means …

Database-level settings are not stored in any configuration file, but the pg_db_role_setting catalog, the only question is how to get them out there. As often in similar cases, psql can be of tremend …