Hot answers tagged

26

There is nothing special about it. As described in PostgreSQL documentation, Creating a Database, you need to be connected to a database, in order to create another one: Since you need to be connected to the database server in order to execute the CREATE DATABASE command, the question remains how the first database at any given site can be created. The ...


13

I see two ways of doing that: With plain standard SQL, simply list all columns and combine that with an OR: delete from the_table where date is null or persons is null or two_wheelers is null or cars is null or vans is null or buses is null or autos is null; Another (Postgres specific) solution is the comparison of the whole row with ...


6

If we slice your query, the first cte (rt2) returns this: id hid parent_hid hierarchy 11 2 1 2 22 3 2 3 44 5 3 5 77 8 5 8 170 11 8 11 190 13 11 13 240 18 15 18 255 20 18 20 The first SELECT ...


4

First, you don't have to duplicate data. If you add an inspector table, that's not duplicating. You need an "inspectors" entity in your model, you add an "inspector" table. Only the inspector_id values will be duplicated which is fine. So, that's solution A. Solution B might be to add a an is_inspector column in the table you want to have the foreign key ...


4

Idea 1 Judging by their names, the columns "denormalizedData" and "hugeText" seem to be comparatively big, probably many times as big as the columns involved in your query. Size matters for big queries like this. Very big values (> 2kb) for text or jsonb get "toasted", which can avert the worst. But even the remainder or smaller values stored inline can be ...


4

In the ANSI SQL Standard, there is no TEXT type. There are various string types defined, like CHAR and VARCHAR and many more but no mention of a TEXT type. There is a CHARACTER LARGE OBJECT and a BINARY LARGE OBJECT. The various TEXT types found in various DBMS (Postgres, SQL Server, MySQL) are additions and have small differences between them. Their ...


4

You want boolean. It can have true (=yes), false (=no) and null (=unknown) More details in the manual: https://www.postgresql.org/docs/current/static/datatype-boolean.html "Avoid nullable columns" is only a recommendation. If you need an "unknown" state, then NULL is exactly what you are looking for. It was specifically created to represent "unknown". ...


3

Preliminary notes You are using odd data types. character(24)? char(n) is an outdated type and almost always the wrong choice. You have indexes on person_id and join on it repeatedly. integer would be much more efficient for multiple reasons. (Or bigint, if you plan to burn more than 2 billion rows over the lifetime of the table.) Related: Would index ...


3

There's no point in using UPDATE to rows INSERTed in the same statement, as the INSERT could set the missing column in the first place: insert into t1(id, name, str2) values (38, 'www', 'aaa'); Besides the pointlessness, it doesn't work, as shown in the question. The reason is mentioned in the doc in 7.8.2. Data-Modifying Statements in WITH: All the ...


3

In Postgres the most efficient way is to do this using distinct on (): select distinct on (o.id) o.id, o.name, t.edu, t.sortby from one o join two t on o.grade = t.grade_id order by o.id, t.sortby desc


3

The error is that the INTO clause is not part of the SQL command. It's part of the plpgsql command EXECUTE. And dynamic field names are currently not possible, neither in SQL nor PL/pgSQL. But there are ways around this limitation: Proof of concept Assign to NEW by key in a Postgres trigger How to set value of composite variable field using dynamic SQL ...


2

The psql command \? shows the option to turn off expanded formatting: Formatting: ... \x [on|off|auto] toggle expanded output (currently off) Typically it will show table data in one record per line if its "off" or else each field in its own line when switched on, e.g.: postgres=# \x off Expanded display is off. postgres=# \l ...


2

A method that I find useful is to implement the cleanup code in my main programming framework (so Django, I guess, in your case) and expose that function via a URL. Then in my crontab I use wget or curl to invoke the cleanup URL. That way the cron file is only responsible for the scheduling part, while the code that does the cleanup is kept together with ...


2

You are correct that PostgreSQL cannot currently use one index to provide selectivity and another index to provide order, in the way you want. Adding this feature has been discussed, but I don't think anyone considers it a high priority. Creating a multicolumn index will not help in any way. From my testing Postgres will not change its query plan at all ...


2

Of course, just use the substring function: select substring(bytea_contents from offset for length) FROM table; or get_byte(bytea_contents,offset) to get a single byte as an integer. See Binary String Functions and Operators in the doc for all functions.


2

How can the 2 indexes be physically the same? The values of id could be more example: 1,2,3,4,6,7,8,9 while the parent_id values could be: NULL,1,1,8,NULL,NULL,2,NULL So the answer to the question is: No, they would be two completely separate and different structures. It doesn't make any other sense. Even if the parent_id were all, one by one, ...


2

You can generate the SQL to add the primary key with: select concat('ALTER TABLE ',table_schema,'.',table_name,' ADD CONSTRAINT ', table_schema,'.pk_',table_name,' PRIMARY KEY (id)') from information_schema.columns where column_name ='id' and table_schema='xxx'; Replace the xxx with the schema for which you want to create the primary ...


2

You should be able to get that by simply adding the additional criteria to the full join instead of the where clause. on i.idhistorialalumnofk = h.id and i.fecha = '06/07/2016' it seems odd to be needing a FULL join here but we don't really have much info so that might actually be the case.


2

To follow on from my comment. I'd recommend a date table. Sample Customer Purchase Data IF OBJECT_ID('tempdb..#CustomerPurchases') IS NOT NULL DROP TABLE #CustomerPurchases GO CREATE TABLE #CustomerPurchases (PurchaseDate date, CustomerName varchar(20)) INSERT INTO #CustomerPurchases (PurchaseDate, CustomerName) VALUES ('2016-07-04','Jon Snow') ,('2016-...


2

You may need to alter this slightly for postgres (is is MS SQL syntax) but something like: SELECT id FROM contacts LEFT OUTER JOIN FROM case_contacts ON case_contacts.contact_id = contacts.id LEFT OUTER JOIN FROM case_payments ON case_payments.contact_id = contacts.id LEFT OUTER JOIN FROM invoices ON invoices.contact_id = contacts.id WHERE ...


2

Among the fastest ways to run such a query is with a specialized index: create index on user_info ((anyarray_uniq(phones) <> phones)) where anyarray_uniq(phones) <> phones; That way the work of doing the comparisons happens when the records are inserted or updated, not when they are selected. (To get this to work, you will have to mark as ...


2

ALTER TABLE ... DISABLE TRIGGER ... takes a SHARE ROW EXCLUSIVE lock. So no relevant concurrent operation can happen on the table: DISABLE/ENABLE ... TRIGGER This command acquires a SHARE ROW EXCLUSIVE lock. where the docs on lock levels say that: SHARE ROW EXCLUSIVE ... This mode protects a table against concurrent data changes, and is ...


2

You can see a count of all user-defined functions being called in the system view pg_stat_user_functions. There is no facility to write all function calls to the log. That would be quite bulky. If you're feeling adventurous, you can play with the settings debug_print_parse and debug_print_plan to get more detailed information about what is being called. ...


2

In your case I think you could consider the use of a Bit String Type data type. For instance, something like: CREATE TABLE yourtable ( booleans bit[5] default B'00000', ... other fields ... ) It is efficient in terms of memory and does not require the use of a complex type like a PostgreSQL array (actually it is a bit array), and more, you do not ...


2

TL;DR: Do not use an array. Use individual boolean columns. Your coworker may not be aware of actual storage requirements. Null storage is very cheap and efficient in Postgres. Do nullable columns occupy additional space in PostgreSQL? A couple of boolean columns are also very cheap and efficient - nullable or not. Actually, just 5 boolean columns ...


2

You could use UNION in the CTE. There is still some duplication of code but less than before: with etl (storenumber, filename, last_extracted) as ( ( select distinct on (storenumber) storenumber, 'storefinancialdate', extracteddate + extractedtime::time from storefinancialdate order by storenumber, ...


2

Looking into my crystal ball, your query might work faster by orders of magnitude like this: SELECT i.*, rr.urllist FROM vw_image i LEFT JOIN LATERAL ( SELECT string_agg(r.url, ',') AS urllist FROM resourceonpage rop JOIN resource r ON r.id = rop.pageid WHERE rop.siteid = 2294 -- or: = i.siteid AND r.resourceid = i.resourceid ...


2

The bad plan is probably a culmination of many problems. Which means that there are many ways of tackling it. My guess is that the culmination of problems causes two plans to look falsely close to each other in cost, and then the differences in memory setting (probably effective_cache_size) between master and slave is the straw that broke the camel's back ...


2

I do not agree with the approach you are thinking of using, but if you are dead set on using it, then I would probably structure the query like this: SELECT path, markdown FROM public."Comment" where path = '/other/relative/path/' AND (TRUE OR markdown = 'here I am'); Essentially adding a 3rd parameter to your query .. one use case would use the 3rd ...


2

Use this query instead: SELECT path, markdown FROM public."Comment" WHERE path = $1 AND ($2 IS NULL OR markdown = $2); To ignore the second parameter, pass NULL for it. Recent related question with more info: Conditional WHERE in plpgsql function



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