Closer examination of the documentation shows that array_length() takes two parameters. I just had to change SELECT question_text, array_length(sort_order) AS level, … to SELECT question_text, array_length(sort_order, 1) AS level, …
The ideal way would be to normalize your design and split the two components of the column into two separate columns. One of type integer, one text. With the current table, you could: SELECT col FROM tbl ORDER BY (substring(col, ‘^[0-9]+’))::int — cast to integer , substring(col, ‘[^0-9_].*$’); — works as text The same substring() … Read more
I wrote a custom validator using jsonschema in order to do this. project/validators.py import django from django.core.validators import BaseValidator import jsonschema class JSONSchemaValidator(BaseValidator): def compare(self, value, schema): try: jsonschema.validate(value, schema) except jsonschema.exceptions.ValidationError: raise django.core.exceptions.ValidationError( ‘%(value)s failed JSON schema check’, params={‘value’: value} ) project/app/models.py from django.db import models from project.validators import JSONSchemaValidator MY_JSON_FIELD_SCHEMA = { ‘schema’: … Read more
You can use information from the catalog tables like so: Create a table with id as the primary key create table test1 (id int primary key, name text); Create the SQL to drop the key select concat(‘alter table public.test1 drop constraint ‘, constraint_name) as my_query from information_schema.table_constraints where table_schema=”public” and table_name=”test1″ and constraint_type=”PRIMARY KEY”; The … Read more
The simplest thing would be: update table set array = array_append(array, ‘element’) where … or perhaps use the || operator: update table set array = array || ‘element’ where … Both of those are equivalent to the more common set n = n + 11 for numbers. Depending on the types involved, you might need … Read more
If you’re sure your connection string is already well-formed like the one gnerkus described, the last thing you need to check is your password. If it’s contain non alphanumeric characters, maybe that’s the one who cause the issue. It seems either the Node.js or the way javascript work itself causing this (I’m not really sure … Read more
Remove all data from one table The simplest form of the TRUNCATE TABLE statement is as follows: TRUNCATE TABLE table_name; Remove all data from table that has foreign key references To remove data from the main table and all tables that have foreign key references to the main table, you use CASCADE option as follows: … Read more
Assuming you installed PostgreSQL on Windows with the PostgreSQL “One-click” installer packaged by EnterpriseDB, psql is not added to the PATH automatically. That’s partly because adding it to the path could otherwise cause confusion when people have multiple versions of PostgreSQL installed. You need to specify the full explicit path to psql, eg: “%PROGRAMFILES%\Postgresql\9.2\bin\psql.exe” or … Read more
You have to drop the default constraint before changing the type: ALTER TABLE parts ALTER COLUMN is_dpm_scanned DROP DEFAULT, ALTER COLUMN is_dpm_scanned TYPE BOOLEAN USING is_dpm_scanned::BOOLEAN, ALTER COLUMN is_dpm_scanned SET DEFAULT FALSE; See also: Changing a column from string to string array in PostgreSQL for a detailed explanation. How to cast varchar to boolean.
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