Tag Info

Hot answers tagged

5

A partial unique index should do this: create unique index max_one_null on item (type_id) where manufactured_until is null; For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type Look into range types and exclusion constraints. They were specifically designed for this problem. Something ...


3

You may use to_char to get the time fields from a single function call: check (to_char(end_time at time zone 'UTC' at time zone 'US/Eastern','HH24:MI:SS') = '23:59:59') Seconds given by SS are not rounded up so that should be OK as an equivalent to floor


3

For now While stuck with your unfortunate solution: CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time) That's right, AT TIME ZONE two times: The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times. The second instance converts ...


2

Consistent rows The important question which does not seem to be on your radar yet: From each set of rows for the same seriesName, do you want the columns of one row, or just any values from multiple rows (which may or may not go together)? Your answer does the latter, you combine the maximum dbid with the maximum retreivaltime, which may come from a ...


2

So this is almost certainly a bad configuration in ip address resolution. As you mention, you have a line resolving localhost to your external ipv6 address. Yes, this should be removed, or changed from fe80::1 to ::1


2

You must group by all of the columns i the select clause: GROUP BY ticket.queue_id, queue.name, article.create_time or add aggregate functions such as MAX: SELECT ticket.queue_id, MAX(queue.name), MAX(article.create_time), COUNT(article.id) FROM article JOIN ticket ON article.ticket_id=ticket.id JOIN queue ON ticket.queue_id=queue.id GROUP ...


2

Use a data-modifying CTE to chain the inserts in a single statement. WITH inskey AS ( INSERT INTO key (key_name) VALUES ($$key_1$$) , ($$key_2$$) RETURNING * -- returns row including newly generated key_id ) INSERT INTO related_key (key_id, related_key_id) SELECT i.key_id, t.related_key_id FROM inskey i JOIN ( VALUES ...


2

Such a 1:1 mapping is sensible when part of the table is heavily accessed and part of it is lightly used. It's a good choice.


2

Possible with a single SELECT: SELECT name, count(*), to_char((count(*) * 100.0 / sum(count(*)) OVER ()), 'FM990.00" %"') AS percent FROM t GROUP BY 1 ORDER BY 1; count(*) is a separate form of the function and slightly faster than count(<expression>). Assuming all columns to be NOT NULL, else you may have to use the ...


1

SELECT name,COUNT(*), ROUND(100.0*COUNT(name)/(SELECT 100.0* count(name) FROM t),3) as percentage FROM t GROUP BY name FIDDLE


1

The fragment you posted so far can be simplified to: INSERT INTO table2 (id, name, date) -- why "date" if you insert a timestamp? SELECT NEW.id, t1.name, NEW.timestamp FROM table1 t1 WHERE ST_DWithin(NEW.position , ST_SetSRID(ST_MakePoint(t1.longitudedecimal, t1.latitudedecimal), 4326) , 0.01447534783) AND t1.id > ...


1

SELECT INTO is usually used to select specific set of data into a table, esp., during scenarios when the data in the table is the priority and not the constraints. It automatically creates a table if there is no such table already. But, INSERT INTO is used when you already have a table that has specific defined constraints and need to add data from a ...


1

Use the information_schema views, they're SQL-standard and contain the information you want. You can also directly access pg_class, pg_attribute, etc, but that's unportable and often fiddlier; you may need helper functions like oidvectortypes, pg_get_function_arguments, etc for some things. If you want to see how psql executes something like \dt, run psql ...


1

You can use the psql command line client. \dt will show a list of tables \dv will show a list of views \d [object_name] will describe the schema of the table or view Not sure how you would describe a query though. More info: https://manikandanmv.wordpress.com/tag/basic-psql-commands/


1

I would say the most advanced database access layer these days is Spring Data JPA. It builds fast, correct DAOs for you at startup time. I would check that out, maybe read some of the source, and gain inspiration from there. As for connections, it depends on the app. For desktop to db apps, you can keep a connection open for the entire user session. For ...


1

Some options: -- regexp_matches SELECT string_agg(arr[1], '') AS string FROM regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi') arr; -- regexp_split_to_table SELECT string_agg(left(word, 1), '') FROM regexp_split_to_table('The United States of America', '\s+') t(word) WHERE NOT (word ILIKE ANY ('{the,of}'::text[])); -- Without ...


1

Ok, I've read the docs more, and now I understand the issue at least a bit better. Basically, what's going on is there are multiple possible values for dbid as a result of the GROUP BY seriesName aggregation. With SQLite and MySQL, apparently the DB engine just choses one at random (which is absolutely fine in my application). However, PostgreSQL is much ...


1

See this post, which links to a big query summarizing all the indexes which may not be pulling their weight.


1

SELECT REPLACE(array_to_string(array(select array_to_string(regexp_matches('The United States of America', '\y(?!(the|of)\y)\D', 'gi'),'')),''),' ',''); FIDDLE


1

You can monitor the index usage through pg_stat_user_indexes and pg_statio_user_indexes More details about the statistics collector can be found in the manual: http://www.postgresql.org/docs/current/static/monitoring-stats.html You should be careful with dropping unused unique indexes though. They might not be used for reading, but they are most probably ...



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