How to Check Type of Value in Postgres

Check the value type of a JSON value in Postgres

1. As other folks have said, there is no reason to convert the variable to an integer just to them cast it to a string. Also, phone numbers are not numbers. :-)

2. You need to be using the ->> operator instead of ->. That alongside IS NOT NULL gets your SELECT query working.

   Note the difference between the two tuple values after running this query:

   SELECT fields->'phone', fields->>'phone'
   FROM parent_object;
   

   Your working query:

   SELECT *
   FROM parent_object
   WHERE (fields->>'phone') IS NOT NULL;
   

3. Postgres does not currently natively support atomically updating individual keys within a JSON column. You can write wrapper UDFs to provide this capability to you: How do I modify fields inside the new PostgreSQL JSON datatype?

How to get a list column names and datatypes of a table in PostgreSQL?

SELECT
        a.attname as "Column",
        pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
    FROM
        pg_catalog.pg_attribute a
    WHERE
        a.attnum > 0
        AND NOT a.attisdropped
        AND a.attrelid = (
            SELECT c.oid
            FROM pg_catalog.pg_class c
                LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relname ~ '^(hello world)$'
                AND pg_catalog.pg_table_is_visible(c.oid)
        );

More info on it : http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

type check for a field in table (postgres) rails

So, a validation like below will work. Add it to the Student model.

validates :marks, numericality: { only_integer: true }

Whenever you will try to create a Student record, this validation will be called by Rails. If it passes then the recored will be created, otherwise not. There are methods to check if a record is valid or not in rails.

Read the guide to know how validation works in rails.

Checking the type of a json property

You can use json_typeof (or jsonb_typeof for 9.4's jsonb). Despite being missing from the 9.3 documentation it's apparently present, and is documented in the manual for json functions in 9.4 and above.

Postgres query to check a string is a number

I think the easiest way would be a regular expression match:

select '12.41212' ~ '^[0-9\.]+$'
=> true

select 'Service' ~ '^[0-9\.]+$'
=> false

How to determine properly data types when I have number in quotes or mixed data?

During import it will cast the value to the column's type, if possible.
For example, if you do SELECT 'FALSE'::boolean it will cast and return false. SELECT '074554'::int works as well and returns 74554.

But the bare characters NA will give you problems. If those are intended to be null, try to do a find/replace on the file and just take them out, so that the first row of data has "009910";;;"FALSE" and see if that works.

You could also have all columns as text, quote the NA values, and import.

Then create a new table, and use INSERT INTO ... SELECT from the all-text table and manually cast or use CASE as needed to convert types.

For example, if you imported into a table called raw_data, and have a nicer table imports:

INSERT INTO imports
SELECT
  column1::int,
  CASE WHEN column2 = 'NA' THEN null ELSE column2::int END,
  CASE WHEN column3 = 'NA' THEN null ELSE column3::int END,
  column4::boolean
FROM
  raw_data

---

Related Topics