postgresql-9.3 – Tarik Billa

how to parse json using json_populate_recordset in postgres

April 8, 2024 by Tarik

The first argument passed to pgsql function json_populate_recordsetshould be a row type. If you want to use the json array to populate the existing table anoop you can simply pass the table anoop as the row type like this: insert into anoop select * from json_populate_recordset(null::anoop, ‘[{“id”:67272,”name”:”EE_Quick_Changes_J_UTP.xlsx”}, {“id”:67273,”name”:”16167.txt”}, {“id”:67274,”name”:”EE_12_09_2013_Bcum_Searchall.png”}]’); Here the null is the default … Read more

Access PostgreSQL server from LAN

November 28, 2023 by Tarik

First, edit the postgresql.conf file, and set listen_addresses. The default value of ‘localhost’ will only listen on the loopback adaptor. You can change it to ‘*’, meaning listen on all addresses, or specifically list the IP address of the interfaces you want it to accept connections from. Note that this is the IP address which … Read more

Can we create a column of character varying(MAX) with PostgreSQL database

November 21, 2023 by Tarik

If you want to created an “unbounded” varchar column just use varchar without a length restriction. From the manual: If character varying is used without length specifier, the type accepts strings of any size So you can use: create table foo ( unlimited varchar ); Another alternative is to use text: create table foo ( … Read more

Are postgres JSON indexes efficient enough compared with classic normalized tables?

September 12, 2023 by Tarik

I will need some queries in the form “list all objects where one of altnames is ‘foobar’.” The expected table size is on the order of a few million records. Postgres JSON queries can be used for that, and it can also be indexed (Index For Finding Element in JSON array, for example). However, SHOULD … Read more

Installing PostgreSQL within a docker container

August 16, 2023 by Tarik

The problem is that the your application/project is trying to access the postgres socket file in the HOST machine (not docker container). To solve it one would either have to explicitly ask for an tcp/ip connection while using the -p flag to set up a port for the postgres container, or share the unix socket … Read more

Can’t make postgresql load at startup in Mac OS

August 5, 2023 by Tarik

Use -w option with launchctl. $ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist -w Overrides the Disabled key and sets it to false.

LocalDateTime to ZonedDateTime

July 12, 2023 by Tarik

Postgres has no such data type as TIMESTAMP. Postgres has two types for date plus time-of-day: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE. These types have very different behavior with regard to time zone information. The WITH type uses any offset or time zone information to adjust the date-time to UTC, then disposes … Read more

PostgreSQL Nested JSON Querying

June 27, 2023 by Tarik

This is because operator ->> gets JSON array element as text. You need a cast to convert its result back to JSON. You can eliminate this redundant cast by using operator ->: select person->’dogs’->0->’breed’ from people where id = 77;

keystroke to clear screen in psql?

June 26, 2023 by Tarik

As an alternative to Ctrl-L use, e.g. for scripts in an linux environment: \! clear or \! cls for Microsoft.

In pg_restore, how can you use a postgres connection string to specify the host/database/username/password?

June 6, 2023 by Tarik

In PostgreSQL tools wherever you can specify a database name you can instead specify a connection string. In the syntax for pg_restore the dbname is passed with a flag, not as a positional parameter: $ pg_restore –help pg_restore restores a PostgreSQL database from an archive created by pg_dump. Usage: pg_restore [OPTION]… [FILE] General options: -d, … Read more