Ezequiel Tolnay
  • Member for 7 years, 1 month
  • Last seen this week
pg_upgrade unrecognized configuration parameter "unix_socket_directory"
30 votes

I hacked the problem by running (as root): mv /usr/bin/pg_ctl{,-orig} echo '#!/bin/bash' > /usr/bin/pg_ctl echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' &...

View answer
How can I dump all tables to CSV for a PostgreSQL schema?
Accepted answer
27 votes

Here's a shell script that can do what you want: SCHEMA="myschema" DB="mydb" psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\ while read TBL; do psql -c "COPY $...

View answer
UPSERT with ON CONFLICT using values from source table in the UPDATE part
11 votes

When doing upserts in PostgreSQL 9.5+ you must refer to the excluded data (that which failed to insert) by the alias excluded. Also, the on conflict option must refer to the key: (pk_b) rather than (b)...

View answer
Postgresql error: failed to re-find parent key in index
Accepted answer
7 votes

Your database is likely corrupted, especially the indexes. You can rebuild all indexes and check all tables on a database by: REINDEX SYSTEM; -- run once REINDEX DATABASE <your dbname>; -- run ...

View answer
Tricking SQL server into thinking it's a different date
6 votes

You could replace all instances of GETDATE (and equivalents) with your own function (e.g. ACMEGETDATE if your company's name is ACME), and have this function return either GETDATE straight, or when ...

View answer
Database in postgresql with missing rows, how to fill the missing rows with a backup copy?
Accepted answer
6 votes

You can restore all affected tables into a different schema, there are a variety of ways in which you can do that (several good ideas in https://stackoverflow.com/questions/4191653/i-want-to-restore-...

View answer
Can we export data from PostgreSQL to xlsx file?
Accepted answer
6 votes

PostgreSQL can't do this natively, but you can convert the CSV output to an XLSX file with a simple perl script. csv2xlsx: For this script to work, install Excel::Writer::XLSX and Text::CSV perl ...

View answer
Group daily schedule into [Start date; End date] intervals with the list of week days
Accepted answer
6 votes

This one uses a recursive CTE. Its result is identical to the example in the question. It was a nightmare to come up with... The code includes comments to ease through its convoluted logic. SET ...

View answer
Require JSON to have an element
Accepted answer
5 votes

You can do that with a column check: ALTER TABLE table ADD CHECK ((column->'id') is not null and (column->'name') is not null);

View answer
Converting SQL SERVER Procedure to POSTGRESQL
Accepted answer
5 votes

It's way easier in plpgsql: CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30)) RETURNS VOID AS $$ DECLARE _r RECORD; _id_kraj int; _nazwa_zespolu text; BEGIN _id_kraj := id_kraj FROM kraj ...

View answer
How to select mutiple values with like clause
5 votes

This case is a good candidate for using REGEXP instead of LIKE. SELECT * from table WHERE column REGEXP '^(AAA|BBB|CCC)' REGEXP is case insensitive. For case sensitive searches use REGEXP BINARY. ...

View answer
Select tables on column entry in schema (with many tables)
Accepted answer
4 votes

In case you need assistance in merging all data, you could do it this way: DO $DO$ DECLARE _tab text; _r record; BEGIN FOR _r IN -- refine the following query according to your particular ...

View answer
Is it possible for a weak entity, already related to a strong entity, to get related to another weak entity in an ER diagram?
4 votes

Depending on how much you briefed your problem statement, it can be stated that: Trainer is a strong entity (it has an ID) Neither Client nor Membership Plan seem to be weak or strong entities. They ...

View answer
MIN(column) with subquery
4 votes

Why would this not be sufficient? SELECT * FROM ( SELECT MOD.MODULE_NAME, MA.MARK FROM MODULES MOD JOIN MARKS MA ON (MOD.MODULE_CODE = MA.MODULE_CODE) JOIN STUDENTS S ON (S.STUDENT_NO = MA....

View answer
Custom ID Numbering - fast way on insert?
4 votes

You can use an identity column in a temporary table to avoid a cursor. This is especially convenient with older SQL Server versions that don't support WITH or window functions. CREATE TABLE #Variable ...

View answer
How would I select distinct records from the same table on two different databases?
4 votes

This would suffice, since UNION already produces distinct results. SELECT col1, col2, ... FROM Media_One.dbo.Video UNION SELECT col1, col2, ... FROM Media_Two.dbo.Video

View answer
How to use RETURNS TABLE with an existing table in PostgreSQL?
3 votes

The different modes are interchangeable in some circumstances, but are different. What RETURNS TABLE is good for is described in the sentence you removed from your quote above: This is equivalent ...

View answer
PostgreSQL not using index scan when a second ORDER BY column is present
3 votes

If you create an index on (row_time DESC, row_id DESC), then the second case will will act just as the first one. LIMIT will always operate after sorting is taken care of, so when sorting without an ...

View answer
which time period covers date string over datetime column
Accepted answer
3 votes

A sargable exact alternative to DATE(ColumnName) = DATE'yyyy-mm-dd' is ColumnName >= TIMESTAMP'yyyy-mm-dd' AND ColumnName < TIMESTAMP'yyyy-mm-dd' + INTERVAL '1' DAY Something like ColumnName &...

View answer
Postgres LEFT JOIN with recurring dates and users
Accepted answer
3 votes

You have to do with users the same thing you're doing with days: WITH days AS ( SELECT generate_series(current_date-7, current_date, '1d')::date AS day ), eves AS ( SELECT user_id, ...

View answer
best way to avoid redundant aggregate functions and/or group by columns
3 votes

PostgreSQL's DISTINCT ON is very elegant and performs very well (often better than aggregates): select DISTINCT ON (foo.id, foo.baz) foo.id, foo.baz, bar.boom as min_boom from foo join bar ...

View answer
Postgres database deadlock issue
3 votes

Your software is most likely not using the default postgres database (that is a database that is normally reserved only to store the list of databases, users, and other globals). From psql, run \l to ...

View answer
How can I optimize a query with multiple derived tables containing `IN` and `GROUP BY`?
Accepted answer
3 votes

You can really benefit from an index on hosts (hostname, scan_id) for this query, and possibly another one including status (especially for the second query below). Your query may also benefit from ...

View answer
Can we commit the transaction when the sql server trigger fails?
3 votes

A trigger is already always operating within an implicit transaction, see the related question: Is there a way to ensure that a SQL Server trigger will be executed? You can catch an error and ...

View answer
How to append datetime in filename in postgresql
Accepted answer
3 votes

If using psql via bash command line, you can simply do: psql -c "COPY (select query) To '/tmp/test_`date +"%Y-%m-%d_%H%M%S"`.csv' DELIMITER ',' CSV HEADER;"

View answer
Group daily schedule into [Start date; End date] intervals with the list of week days
3 votes

I couldn't understand the logic behind grouping weeks with gaps, or weeks with weekends (e.g. when there are two consecutive weeks with a weekend, which week does the weekend go to?). The following ...

View answer
Bus timetable Database structure
Accepted answer
3 votes

You'll need to redefine your database to store that information. An example is the following: CREATE TABLE routes ( route_id int PRIMARY KEY, route_name varchar(200) ); CREATE TABLE stops ( ...

View answer
Cross-cutting I18N native support in databases
3 votes

With PostgreSQL you can simulate something like using views and environment variables. create table books_ids( book_id serial not null primary key, author text not null); create table ...

View answer
Filtering special characters in to_tsquery
Accepted answer
3 votes

You may want to just leave it as users enter it, since you can safely use strings processed twice via quote_literal. In the to_tsquery documentation, the example for phrases within ts queries is: ...

View answer
open/execute huge sql file
3 votes

PostgreSQL database dumps are normally produced by the command line tool pg_dump, and expected to be used via either pg_restore or simply psql command line tools. However, you can both backup and ...

View answer
1
2 3 4 5 6